SQL - Joins

Joins

SQL joins combine rows from multiple tables. They are central to relational databases because normalized data is usually spread across related tables, and the join condition is almost always a primary-key / foreign-key match (see SQL Keys And Constraints).

INNER JOIN

INNER JOIN returns only matching records from both tables.

SELECT *
FROM A
INNER JOIN B ON A.c1 = B.c1;

JOIN without a modifier usually means INNER JOIN.

LEFT JOIN

LEFT JOIN returns all rows from the left table and matching rows from the right table.

SELECT *
FROM A
LEFT JOIN B ON A.c1 = B.c1;

There is no practical difference between LEFT JOIN and LEFT OUTER JOIN.

RIGHT JOIN

RIGHT JOIN returns all rows from the right table and matching rows from the left table.

SELECT *
FROM A
RIGHT JOIN B ON A.c1 = B.c1;

SELF JOIN

A self join joins a table to itself. It is useful for comparing rows in the same table.

SELECT
    A.id,
    A.recordDate,
    A.temperature
FROM Weather AS A, Weather AS B
WHERE A.id <> B.id
  AND A.temperature > B.temperature
  AND DATEDIFF(A.recordDate, B.recordDate) = 1;

This creates a Cartesian product first, then filters for the desired row relationship.

CROSS JOIN

CROSS JOIN creates a Cartesian product: every row from the first table paired with every row from the second table.

USING Vs ON

When field names are the same in both tables, these are equivalent:

JOIN B USING (field_name)
JOIN B ON A.field_name = B.field_name

Use ON when column names differ or when the join condition is more complex.