SQL Joins cheat sheet


Column 1 1. INNER JOIN: Get the satisfaction scores, first names, and last names only for employees that show up in both the satisfaction and demographic tables. `SELECT sat.id, sat.satisfaction, info.first_name, info.last_name FROM employee_sat AS sat INNER JOIN employee_info AS info ON sat.id = info.id;` 2. LEFT JOIN: Get the performance scores and ages for all employees in the satisfaction table, even if they don’t show up in the demographic table. `SELECT sat.performance, info.age FROM employee_sat AS sat LEFT JOIN employee_info AS info ON sat.id = info.id;` 3. RIGHT JOIN: Get the salaries and genders for all employees in the demographic table, even if they don’t show up in the satisfaction table. `SELECT sat.salary, info.gender FROM employee_sat AS sat RIGHT JOIN employee_info As info ON sat.id = info.id;` 4. FULL/OUTER JOIN: Get the IDs of all employees, along with their satisfaction scores, first names, and last names, whether they show up in the satisfaction table, the demographic table, or both. `SELECT sat.id, sat.satisfaction, info.first_name, info.last_name FROM employee_sat AS sat OUTER JOIN employee_info AS info ON sat.id = info.id;` Column 2 5. CROSS JOIN: Generate all possible pairings of satisfaction scores and performance ratings for testing. `SELECT sat.satisfaction, sat.performance FROM employee_sat AS sat CROSS JOIN ( SELECT DISTINCT performance FROM employee_sat ) AS perf_pairs;` 6. LEFT ANTI JOIN: Find employees who have satisfaction records but no demographic information. `SELECT sat.id, sat.satisfaction, sat.performance FROM employee_sat AS sat LEFT JOIN employee_info AS info ON sat.id = info.id WHERE info.id IS NULL;` 7. RIGHT ANTI JOIN: Find employees who have demographic records but no corresponding satisfaction records. `SELECT info.id, info.first_name, info.last_name FROM employee_sat AS sat RIGHT JOIN employee_info AS info ON sat.id = info.id WHERE sat.id IS NULL;` 8. FULL/OUTER ANTI JOIN: Find employees who are either in the satisfaction table or in the demographic table, but not in both. `SELECT sat.id, sat.satisfaction, sat.performance, info.first_name, info.last_name FROM employee_sat AS sat FULL OUTER JOIN employee_info AS info ON sat.id = info.id WHERE sat.id IS NULL OR info.id IS NULL;` Column 3 9. SELF-JOIN: Find the first and last name of each employee's manager. `SELECT emp.first_name AS employee_first_name, emp.last_name AS employee_last_name, mgr.first_name AS manager_first_name, mgr.last_name AS manager_last_name FROM employee_info AS emp LEFT JOIN employee_info AS mgr ON emp.manager_id = mgr.id;` 1. INNER: Gets only matching rows from both tables (only the intersection). 2. LEFT: Everything from the left, with matches from the right table if possible, else a NULL. 3. RIGHT: Everything from the right, with matches from the left table if possible, else a NULL. Every right join can be rewritten as a left join. 4. FULL OUTER: Everything from both sides, matched if possible, else NULL. 5. CROSS: Combines every row from the first table with every row from the second table (Cartesian product). 6. SELF: Joins a table with itself. 7. LEFT ANTI: Everything from the left that has no match in the right. 8. RIGHT ANTI: Everything from the right that has no match in the left. 9. FULL ANTI: Rows that exist in either table but not in both (inverse of inner).

Column 1

1. INNER JOIN: Get the satisfaction scores, first names, and last names only for employees that show up in both the satisfaction and demographic tables.

SELECT sat.id, sat.satisfaction, info.first_name, info.last_name
FROM employee_sat AS sat
INNER JOIN employee_info AS info
ON sat.id = info.id;

2. LEFT JOIN: Get the performance scores and ages for all employees in the satisfaction table, even if they don’t show up in the demographic table.

SELECT   sat.performance,  info.age
FROM employee_sat AS sat
LEFT JOIN employee_info AS info
ON sat.id = info.id;

3. RIGHT JOIN: Get the salaries and genders for all employees in the demographic table, even if they don’t show up in the satisfaction table.

SELECT sat.salary,  info.gender
FROM employee_sat AS sat
RIGHT JOIN employee_info As info
ON sat.id = info.id;

4. FULL/OUTER JOIN: Get the IDs of all employees, along with their satisfaction scores, first names, and last names, whether they show up in the satisfaction table, the demographic table, or both.

SELECT sat.id,  sat.satisfaction, info.first_name, info.last_name
FROM employee_sat AS sat
OUTER JOIN employee_info AS info
ON sat.id = info.id;

Column 2

5. CROSS JOIN: Generate all possible pairings of satisfaction scores and performance ratings for testing.

SELECT sat.satisfaction, sat.performance
FROM employee_sat AS sat
CROSS JOIN (
    SELECT DISTINCT performance 
    FROM employee_sat
) AS perf_pairs;

6. LEFT ANTI JOIN: Find employees who have satisfaction records but no demographic information.

SELECT sat.id, sat.satisfaction, sat.performance
FROM employee_sat AS sat
LEFT JOIN employee_info AS info
ON sat.id = info.id
WHERE info.id IS NULL;

7. RIGHT ANTI JOIN: Find employees who have demographic records but no corresponding satisfaction records.

SELECT info.id, info.first_name, info.last_name
FROM employee_sat AS sat
RIGHT JOIN employee_info AS info
ON sat.id = info.id
WHERE sat.id IS NULL;

8. FULL/OUTER ANTI JOIN: Find employees who are either in the satisfaction table or in the demographic table, but not in both.

SELECT sat.id, sat.satisfaction, sat.performance,   info.first_name, info.last_name
FROM employee_sat AS sat
FULL OUTER JOIN employee_info AS info
ON sat.id = info.id
WHERE sat.id IS NULL OR info.id IS NULL;

Column 3

9. SELF-JOIN: Find the first and last name of each employee's manager.

SELECT 
    emp.first_name AS employee_first_name, 
    emp.last_name AS employee_last_name, 
    mgr.first_name AS manager_first_name, 
    mgr.last_name AS manager_last_name
FROM employee_info AS emp
LEFT JOIN employee_info AS mgr
ON emp.manager_id = mgr.id;

1. INNER: Gets only matching rows from both tables (only the intersection).

2. LEFT: Everything from the left, with matches from the right table if possible, else a NULL.

3. RIGHT: Everything from the right, with matches from the left table if possible, else a NULL. Every right join can be rewritten as a left join.

4. FULL OUTER: Everything from both sides, matched if possible, else NULL.

5. CROSS: Combines every row from the first table with every row from the second table (Cartesian product).

6. SELF: Joins a table with itself.

7. LEFT ANTI: Everything from the left that has no match in the right.

8. RIGHT ANTI: Everything from the right that has no match in the left.

9. FULL ANTI: Rows that exist in either table but not in both (inverse of inner).