SQL Joins – INNER JOIN, LEFT JOIN, RIGHT JOIN & FULL OUTER JOIN with Examples (2026-27)

✅ What is a SQL JOIN?

✅ A SQL JOIN combines rows from two or more tables based on a related column shared between them.

✅ The related column is typically a foreign key in one table and a primary key in another.

✅ Without JOINs, you would need to run multiple separate queries and combine results manually — JOIN does it all in one step.

✅ All SQL JOIN Types at a Glance

✅ Sample Tables Used in All Examples

✅ INNER JOIN – Only Matching Rows

INNER JOIN returns only rows where there is a matching value in both tables. Rows with no match are excluded.

✅ SELECT Query:-

SELECT E.EmpID, E.Name, D.DeptName
FROM Employees E
INNER JOIN Department D ON E.DeptID = D.DeptID;

✅ Result:-

✅ LEFT JOIN – All Left Table + Matching Right

LEFT JOIN returns ALL rows from the left table (Employees), plus matching rows from the right table (Department). Where no match exists, the right table columns show NULL.

✅ SELECT Query:-

SELECT E.EmpID, E.Name, D.DeptName
FROM Employees E
LEFT JOIN Department D ON E.DeptID = D.DeptID;

✅ Result:-

✅ RIGHT JOIN – All Right Table + Matching Left

RIGHT JOIN returns ALL rows from the right table (Department), plus matching rows from the left table (Employees). Where no match exists, the left table columns show NULL.

✅ SELECT Query:-

SELECT E.EmpID, E.Name, D.DeptName
FROM Employees E
RIGHT JOIN Department D ON E.DeptID = D.DeptID;

✅ Result:-

✅ FULL OUTER JOIN – All Rows from Both Tables

FULL OUTER JOIN returns ALL rows from both tables. Matching rows are combined; non-matching rows from either side show NULL for the other table's columns.

✅ SELECT Query:-

SELECT E.EmpID, E.Name, D.DeptName
FROM Employees E
FULL OUTER JOIN Department D ON E.DeptID = D.DeptID;

✅ Result:-

✅ CROSS JOIN – Every Possible Combination

CROSS JOIN returns the Cartesian product — every row from the left table paired with every row from the right table. No ON condition is used.

✅ SELECT Query:-

SELECT E.Name, D.DeptName
FROM Employees E
CROSS JOIN Department D;

✅ SELF JOIN – Table Joined with Itself

A SELF JOIN joins a table with itself using table aliases. Common use case: finding employee-manager relationships in the same table.

✅ Extended Employees Table with ManagerID:-

✅ SELECT Query — Show Each Employee and Their Manager Name:-

SELECT E.Name AS Employee, M.Name AS Manager
FROM Employees E
LEFT JOIN Employees M ON E.ManagerID = M.EmpID;

✅ Result:-

✅ JOIN Type Comparison Table

✅ Key Points to Remember

✅ INNER JOIN — most used; returns only rows with a match in both tables.

✅ LEFT JOIN — use when you want all records from the left table regardless of matches.

✅ RIGHT JOIN — mirror of LEFT JOIN; all records from the right table.

✅ FULL OUTER JOIN — all rows from both; NULL fills unmatched sides.

✅ CROSS JOIN — every combination; result size = rows left × rows right.

✅ SELF JOIN — join a table with itself; always use aliases (E, M, etc.).

✅ Always use aliases — makes multi-table queries readable: FROM Employees E JOIN Department D ON E.DeptID = D.DeptID.

✅ MySQL workaround — MySQL does not support FULL OUTER JOIN; use UNION of LEFT and RIGHT JOIN instead.

✅ Common SQL JOIN Mistakes to Avoid