When working with relational databases, it is rare to keep all of your data in a single table. Instead, you often need to bring together information from multiple tables. This is where joins in SQL become essential. Joins allow you to combine related data based on logical connections between tables, usually through primary and foreign keys.
What is a Join?
A join in SQL is used to retrieve rows from two or more tables based on a related column between them. By using joins, you can write efficient queries to answer complex questions without duplicating data.
Types of Joins
Inner Join
An inner join returns rows when there is a matching value in both tables.
Example:
SELECT employees.name, departments.department_name
FROM employees
INNER JOIN departments
ON employees.department_id = departments.department_id;
This query lists employees along with the names of the departments they belong to. Only employees with a department will appear.
Left Join
A left join returns all records from the left table and the matching records from the right table. If there is no match, the result will show NULL for columns from the right table.
Example:
SELECT employees.name, departments.department_name
FROM empployees
LEFT JOIN departments
ON employees.department_id = departments.department_id;
This shows all employees even if they are not assigned to a department. Unassigned employees will have NULL in the department column.
Right Join
A right join is the opposite of a left join. It returns all records from the right table and the matching records from the left.
Example:
SELECT employees.name, departments.department_name
FROM employees
RIGHT JOIN departments
ON employees.department_id = departments.department_id;
This ensures that all departments are listed, even if no employees are assigned to them.
Full Join
A full join returns all records when there is a match in either the left or right table. Where there is no match, NULL values fill the missing side.
Example:
SELECT employees.name, departments.department_names
FROM employees
FULL JOIN departments
ON employees.department_id = departments.department_id;
This query ensures all employees and all departments are included, whether or not they have matches.
Cross Join
A cross join returns the Cartesian product of two tables, meaning it combines every row from the first table with every row from the second.
Example:
SELECT employees.name, projects.project_name
FROM employees
CROSS JOIN projects;
If there are 10 employees and 5 projects, this query will return 50 rows.
Why Joins Matter
Joins are powerful because they let you normalise data across multiple tables and still query it efficiently. Instead of storing the same information repeatedly, you can separate your data into logical units and combine them dynamically when needed. This keeps databases consistent, avoids duplication, and improves flexibility.