SQL Joins: Inner, Left, Right, and Full

When working with relational databases, the ability to combine data from multiple tables is essential. That’s where SQL joins come into play. Joins allow you to retrieve data from two or more tables based on a related column between them. There are four main types of joins in SQL: inner, left, right, and full. In this article, we will explore each type of join, discuss their differences, and provide code examples to demonstrate their usage.

Inner Join

The inner join is the most common type of join in SQL. It returns rows from both tables where the specified condition is met. If there is no match, the row will not be included in the result set.

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

For example, if we have two tables – employees and departments – and we want to retrieve all employees who have a department assigned to them, we can use the following query:

SELECT employees.name, departments.department_name
FROM employees
INNER JOIN departments
ON employees.department_id = departments.id;

Left Join (Left Outer Join)

The left join returns all rows from the left table and the matched rows from the right table. If there is no match, the result set will contain NULL for each column from the right table.

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;

Using the same tables as above, if we want to retrieve all employees and their department names, including those who do not belong to any department, we can use the following query:

SELECT employees.name, departments.department_name
FROM employees
LEFT JOIN departments
ON employees.department_id = departments.id;

Right Join (Right Outer Join)

The right join is similar to the left join, but it returns all rows from the right table and the matched rows from the left table.

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;

If we want to retrieve a list of all departments and any employees assigned to them, we would use a right join:

SELECT employees.name, departments.department_name
FROM employees
RIGHT JOIN departments
ON employees.department_id = departments.id;

Full Join (Full Outer Join)

The full join combines the results of both left and right outer joins. It returns all rows from both tables and fills in NULLs for missing matches on either side.

SELECT column_name(s)
FROM table1
FULL JOIN table2
ON table1.column_name = table2.column_name;

To see all employees and all departments regardless of whether there is a match or not, a full join is used:

SELECT employees.name, departments.department_name
FROM employees
FULL JOIN departments
ON employees.department_id = departments.id;

In summary, SQL joins are powerful tools that allow you to merge and manipulate data from different tables in a database. By understanding when to use inner, left, right, or full joins, you can efficiently manage complex data relationships and perform advanced queries with ease.