SQL JOINs Explained: INNER, LEFT, RIGHT, FULL OUTER and SELF JOIN with Examples

SQL JOINs are one of the most important concepts in relational databases. Once you understand how each type works, writing complex queries becomes much easier. This article explains INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN and SELF JOIN with clear examples.

What is a SQL JOIN?

A JOIN combines rows from two or more tables based on a related column. For example, you might have an Employees table and a Departments table. A JOIN lets you query both tables together and see which employee belongs to which department.

For all examples below, we will use these two tables:

What does INNER JOIN return?

INNER JOIN returns only the rows where there is a match in both tables. You get one kind of row in the result:

• Matched rows - where the join condition is true, columns from both tables are populated. Rows with no match on either side are excluded.

SELECT e.name, d.name AS department
FROM Employees e
INNER JOIN Departments d ON e.department_id = d.id

Carol is excluded because her department_id is NULL. HR is excluded because no employee belongs to it. Only the intersection matters.

What does LEFT JOIN return?

LEFT JOIN returns all rows from the left table, matching them where it can. You get two kinds of rows in the result:

• Matched rows - where the join condition is true, columns from both tables are populated (same as an INNER JOIN).
• Unmatched left rows - rows from the left table with no match in the right table; the right table columns come back as NULL.

SELECT e.name, d.name AS department
FROM Employees e
LEFT JOIN Departments d ON e.department_id = d.id

Carol appears this time with NULL for department. All employees are included, even those without a department. HR still does not appear because it has no employee.

What does RIGHT JOIN return?

RIGHT JOIN is the mirror of LEFT JOIN. It returns all rows from the right table, matching them where it can. You get two kinds of rows in the result:

• Matched rows - where the join condition is true, columns from both tables are populated (same as an INNER JOIN).
• Unmatched right rows - rows from the right table with no match in the left table; the left table columns come back as NULL.

SELECT e.name, d.name AS department
FROM Employees e
RIGHT JOIN Departments d ON e.department_id = d.id

HR appears now with NULL for the employee name. All departments are included, even those with no employees. Carol is excluded because there is no department matching her NULL department_id.

What does FULL OUTER JOIN return?

FULL OUTER JOIN returns all rows from both tables, matching them where it can and filling in NULL where it cannot. More precisely, you get three kinds of rows in the result:

• Matched rows - where the join condition is true, columns from both tables are populated (same as an INNER JOIN).
• Unmatched left rows - rows from the left table with no match in the right table; the right table columns come back as NULL.
• Unmatched right rows - rows from the right table with no match in the left table; the left table columns come back as NULL.

SELECT e.name, d.name AS department
FROM Employees e
FULL OUTER JOIN Departments d ON e.department_id = d.id

All employees and all departments appear. Carol shows up with no department, and HR shows up with no employee.

What is a SELF JOIN?

A SELF JOIN is when a table is joined with itself. There is no special SELF JOIN keyword in SQL - you simply use a regular JOIN with two different aliases for the same table.

It is useful when a table has a column that references another row in the same table. A common example is an employees table where each employee has a manager_id that points to another employee in the same table.

SELECT e.name AS employee, m.name AS manager
FROM Employees e
LEFT JOIN Employees m ON e.manager_id = m.id

Alice has no manager (she is at the top), so her manager column is NULL. Bob and Carol both report to Alice. Dave reports to Bob. The same table is referenced twice using the aliases e (employee) and m (manager).

When should you use each JOIN?

• INNER JOIN - when you only care about rows that have a match on both sides. Most common use case.
• LEFT JOIN - when you want all records from the main table, regardless of whether a related record exists. Very useful for finding missing data (WHERE right.id IS NULL).
• RIGHT JOIN - same logic as LEFT JOIN but from the other direction. Usually rewritten as a LEFT JOIN for clarity.
• FULL OUTER JOIN - when you need all records from both tables, including unmatched rows from each side.
• SELF JOIN - when a table has a column referencing another row in the same table, such as hierarchical or parent-child relationships.

Frequently asked questions

Official resources

• Joins (SQL Server) - Microsoft Learn