SQL Joins – Types and Examples
In this lecture, we will cover different types of SQL Joins using the Employees and
Departments tables.
Schemas:
Departments Table
● department_id (Primary Key)
● department_name
● location_id
Employees Table
● employee_id (Primary Key)
● first_name
● last_name
● salary
● department_id (Foreign Key referencing department_id in the Departments table)
1. INNER JOIN
Question:
Retrieve employee details along with their department names.
SQL Query:
SELECT e.employee_id, e.first_name,
e.last_name, e.salary, d.department_name
FROM Employees e
INNER JOIN Departments d
ON e.department_id = d.department_id;
Explanation:
● INNER JOIN returns records that have matching values in both tables.
● It fetches employee details and the name of the department to which the employee
belongs.
● If an employee is not assigned to a department, they will not appear in the results.
2. LEFT JOIN (LEFT OUTER JOIN)
Question:
List all employees and their department names, including those who are not assigned to any
department.
SQL Query:
SELECT e.employee_id, e.first_name, e.last_name, e.salary, d.department_name
FROM Employees e
LEFT JOIN Departments d
ON e.department_id = d.department_id;
Explanation:
● LEFT JOIN returns all records from the Employees table and matching records from the
Departments table.
● If an employee is not assigned to a department, the department name will be displayed as
NULL.
3. RIGHT JOIN (RIGHT OUTER JOIN)
Question:
List all departments and their employees, including departments that have no employees.
SQL Query:
SELECT e.employee_id, e.first_name, e.last_name, e.salary, d.department_name
FROM Employees e
RIGHT JOIN Departments d
ON e.department_id = d.department_id;
Explanation:
● RIGHT JOIN returns all records from the Departments table and matching records
from the Employees table.
● If a department has no employees, the employee details will be displayed as NULL.
4. FULL JOIN (FULL OUTER JOIN)
Question:
Retrieve all employees and departments, showing matching employees and departments, as well
as unmatched records from both tables.
SQL Query:
SELECT e.employee_id, e.first_name, e.last_name, e.salary, d.department_name
FROM Employees e
FULL OUTER JOIN Departments d
ON e.department_id = d.department_id;
Explanation:
● FULL JOIN returns all records from both tables, showing all matching data.
● If no match is found, NULL values will be used where data is missing on either side.
5. CROSS JOIN
Question:
Display a list of every possible pair of employees and departments (Cartesian product).
SQL Query:
SELECT e.employee_id, e.first_name, d.department_name
FROM Employees e
CROSS JOIN Departments d;
Explanation:
● CROSS JOIN generates all possible combinations of rows from both the Employees and
Departments tables.
● This can be useful for creating combinations or testing.
6. SELF JOIN
Question:
Find employees who earn more than the average salary of their department.
SQL Query:
SELECT e1.employee_id, e1.first_name, e1.salary
FROM Employees e1
JOIN Employees e2
ON e1.department_id = e2.department_id
WHERE e1.salary > (SELECT AVG(salary) FROM Employees WHERE department_id =
e2.department_id);
Explanation:
● A self-join joins a table with itself.
● In this case, the query finds employees who earn more than the average salary of their
respective department.
When to use each type of SQL join:
1. INNER JOIN:
● When to use:
Use INNER JOIN when you only want to retrieve rows that have matching values in both
tables. This is helpful when you only need data that has a relationship between the
tables.
● Example:
You want to fetch all employees who are assigned to a department.
2. LEFT JOIN (LEFT OUTER JOIN):
● When to use:
Use LEFT JOIN when you want to retrieve all rows from the left table (first table), and
the matched rows from the right table. Rows in the left table with no match in the right
table will still be included, but with NULL values for the right table's columns.
● Example:
You want a list of all employees, including those who are not assigned to any
department.
3. RIGHT JOIN (RIGHT OUTER JOIN):
● When to use:
Use RIGHT JOIN when you want to retrieve all rows from the right table (second table)
and the matched rows from the left table. Rows in the right table without matches in the
left table will still be included with NULL values for the left table’s columns.
● Example:
You want a list of all departments, including those that have no employees assigned to
them.
4. FULL JOIN (FULL OUTER JOIN):
● When to use:
Use FULL JOIN when you want to retrieve all rows where there is a match between both
tables and also include all rows that don't have matching records from either table. This
is useful when you want to see everything, including the unmatched records.
● Example:
You want to see all employees and departments, even if some employees are not
assigned to a department, or some departments have no employees.
5. CROSS JOIN:
● When to use:
Use CROSS JOIN when you want to return the Cartesian product of the two tables,
meaning every row from the first table combined with every row from the second table.
This is generally rare and used for specific testing or special data manipulations.
● Example:
You want to create every possible combination of employees and departments for
testing or creating pairs.
6. SELF JOIN:
● When to use:
Use SELF JOIN when you need to join a table with itself. This is often used when you
want to compare rows within the same table.
● Example:
You want to find employees who earn more than their colleagues in the same
department.
