1. Inner join on multiple columns
Suppose we have two tables in place as below:
Joining them on F1 and F4 will give you:
And join them on both F1=F4 and F2=F5, you will get:
Refer to:#!3/cec20/2
2. The anwsers to the questions from Jitbit:
List employees (names) who have a bigger salary than their bossSELECT subordinates.nameFROM [dbo].[employees] subordinates INNER JOIN [dbo].[employees] bossON subordinates.bossid = boss.idWHERE subordinates.salary > boss.salaryList employees who have the biggest salary in their departmentsSELECT nameFROM emps AS empINNER JOIN (SELECT department_id, MAX(salary) AS salaryFROM emps GROUP BY department_id) AS max_salariesON emp.department_id = max_salaries.department_id AND emp.salary = max_salaries.salary;Correlated Query(faster)SELECT empy1.nameFROM employees AS empy1WHERE empy1.salary = (SELECT MAX(empy2.salary) FROM employees AS empy2WHERE empy2.department_id = empy1.department_id )List departments that have less than 3 people in itSELECT name AS DepName FROM [dbo].[departments]WHERE [dbo].[departments].id IN (SELECT d.id FROM [dbo].[employees] e INNER JOIN [dbo].[departments] d ON e.department_id = d.idGROUP BY d.idHAVING COUNT(e.id) < 3)List all departments along with the number of people there (tricky – people often do an "inner join" leaving out empty departments)SELECT d.name AS DepName, COUNT(e.id) AS HeadCount FROM departments d LEFT JOIN employees e ON d.id = e.department_idGROUP BY d.nameList employees that don’t have a boss in the same departmentSELECT subordinates.nameFROM employees subordinates LEFT JOIN employees bossON subordinates.bossid = boss.idWHERE subordinates.department_id != boss.department_id OR subordinates.bossid IS NULLUsing EXISTS(faster)SELECT subordinates.nameFROM employees AS subordinatesWHERE NOT EXISTS (SELECT boss.id FROM employees AS bossWHERE subordinates.department_id = boss.department_id AND subordinates.bossid = boss.id)List all departments along with the total salary thereSELECT d.name, CASE WHEN SUM(e.salary) IS NULL THEN 0 ELSE SUM(e.salary) END AS TotSalFROM departments d LEFT JOIN employees eON d.id = e.department_idGROUP BY d.name
版权声明：本文为博主原创文章，未经博主允许不得转载。
，自信的生命最美丽！
