Joins in SQL - INNER, LEFT, RIGHT, OUTER, CROSS, SELF

SQL joins are used to combine data from two or more tables based on a related column. The different types of joins determine how rows are matched between tables and which unmatched rows are included in the result.

INNER JOIN

• Definition: Returns rows where there is a match in both tables.
• Use Case: Retrieve only the rows with matching values in the specified column.

Tables:

Employees Table

ID	Name	Dept_ID
1	Alice	101
2	Bob	102
3	Charlie	NULL
4	Diana	103

Departments Table

Dept_ID	Dept_Name
101	HR
102	IT
103	Marketing
104	Finance

Query:

SELECT e.Name, d.Dept_Name
FROM Employees e
INNER JOIN Departments d
ON e.Dept_ID = d.Dept_ID;

Result:

Name	Dept_Name
Alice	HR
BOb	IT
Diana	Marketing

LEFT JOIN (or LEFT OUTER JOIN)

• Definition: Returns all rows from the left table and the matching rows from the right table. If no match is found, NULL values are returned for the right table.
• Use Case: Retrieve all rows from the primary table and include related data if it exists.

Query:

SELECT e.Name, d.Dept_Name
FROM Employees e
LEFT JOIN Departments d
ON e.Dept_ID = d.Dept_ID;

Result:

Name	Dept_Name
Alice	HR
BOb	IT
Charlie	NULL
Diana	Marketing

RIGHT JOIN (or RIGHT OUTER JOIN)

• Definition: Returns all rows from the right table and the matching rows from the left table. If no match is found, NULL values are returned for the left table.
• Use Case: Retrieve all rows from a secondary table and include related data if it exists.

Query:

SELECT e.Name, d.Dept_Name
FROM Employees e
RIGHT JOIN Departments d
ON e.Dept_ID = d.Dept_ID;

Result:

Name	Dept_Name
Alice	HR
BOb	IT
Diana	Marketing
NULL	Finance

FULL OUTER JOIN

• Definition: Combines LEFT JOIN and RIGHT JOIN, returning all rows from both tables. Rows without matches are filled with NULLs.
• Use Case: Retrieve a complete dataset from both tables, even if some rows don’t match.

Query:

SELECT e.Name, d.Dept_Name
FROM Employees e
FULL OUTER JOIN Departments d
ON e.Dept_ID = d.Dept_ID;

Result:

Name	Dept_Name
Alice	HR
BOb	IT
Charlie	NULL
Diana	Marketing
NULL	Finance

CROSS JOIN

• Definition: Produces a Cartesian product, combining each row from the first table with every row from the second table.
• Use Case: Useful for generating combinations when no logical relationship exists between tables.

Query:

SELECT e.Name, d.Dept_Name
FROM Employees e
CROSS JOIN Departments d;

Result:

Name	Dept_Name
Alice	HR
Alice	IT
Alice	Marketing
Bob	HR
Bob	IT
Bob	Marketing
Charlie	HR
Charlie	IT
Charlie	Marketing
Diana	HR
Diana	IT
Diana	Marketing

SELF JOIN

• Definition: A table joins with itself, treating it as two separate tables.
• Use Case: Find relationships within the same table, such as hierarchical or recursive data.

Table: Employees Table

ID	Name	ManagerID
1	Alice	NULL
2	Bob	1
3	Charlie	1
4	Diana	2

Query:

SELECT e1.Name AS Employee, e2.Name AS Manager
FROM Employees e1
LEFT JOIN Employees e2
ON e1.ManagerID = e2.ID;

Result:

Employee	Manager
Alice	NULL
Bob	Alice
Charlie	Alice
Diana	Bob

Conclusion

• Use INNER JOIN for matched rows only.
• Use LEFT JOIN or RIGHT JOIN for preserving unmatched rows from one side.
• Use FULL OUTER JOIN for all rows from both tables.
• Use CROSS JOIN for all combinations of rows.
• Use SELF JOIN for analyzing relationships within the same table.