What types of SQL joins do you know?

SQL joins are fundamental operations used to combine rows from two or more tables based on a related column between them. Understanding the different types of joins is crucial for retrieving comprehensive and meaningful data from relational databases.

Understanding SQL Joins

Joins are a key component of the SQL language, allowing you to link data from multiple tables. This is essential when your data is normalized across several tables to reduce redundancy and improve data integrity. The type of join you use determines which rows are included in the result set based on whether the join condition is met in one, both, or neither of the tables.

Common SQL Join Types

INNER JOIN

The INNER JOIN keyword returns records that have matching values in both tables. If there are rows in 'TableA' that do not have a match in 'TableB', or vice versa, those rows will not be included in the result set.

SELECT column_name(s)
FROM TableA
INNER JOIN TableB
ON TableA.common_column = TableB.common_column;

LEFT JOIN (or LEFT OUTER JOIN)

The LEFT JOIN keyword returns all records from the left table (TableA), and the matching records from the right table (TableB). If there is no match in the right table, the result is NULL for the columns from the right side. It is also known as LEFT OUTER JOIN.

SELECT column_name(s)
FROM TableA
LEFT JOIN TableB
ON TableA.common_column = TableB.common_column;

RIGHT JOIN (or RIGHT OUTER JOIN)

The RIGHT JOIN keyword returns all records from the right table (TableB), and the matching records from the left table (TableA). If there is no match in the left table, the result is NULL for the columns from the left side. It is also known as RIGHT OUTER JOIN.

SELECT column_name(s)
FROM TableA
RIGHT JOIN TableB
ON TableA.common_column = TableB.common_column;

FULL OUTER JOIN

The FULL OUTER JOIN keyword returns all records when there is a match in either the left (TableA) or the right (TableB) table. If there are no matches, it will still return the rows with NULL values for the columns from the non-matching side. Note: Not all database systems (e.g., MySQL) support FULL OUTER JOIN directly.

SELECT column_name(s)
FROM TableA
FULL OUTER JOIN TableB
ON TableA.common_column = TableB.common_column;

CROSS JOIN

The CROSS JOIN keyword returns the Cartesian product of the rows from the joined tables. This means it combines each row from the first table with every row from the second table, resulting in a result set where the number of rows is the product of the number of rows in both tables. It typically doesn't have an ON clause.

SELECT column_name(s)
FROM TableA
CROSS JOIN TableB;

SELF JOIN

A SELF JOIN is a regular join (usually an INNER JOIN or LEFT JOIN) that joins a table to itself. This is useful when you need to compare rows within the same table, for example, finding all employees who report to the same manager, or hierarchical data.

SELECT A.employee_name, B.employee_name AS manager_name
FROM Employees A, Employees B
WHERE A.manager_id = B.employee_id;

Summary of Join Types