JOIN in SQL

A JOIN is used to combine rows from two or more tables based on a related column. It usually combines using foreign key.

Table Data

Types of Joins

1. INNER JOIN

It returns only the records with matching values in both tables.

Example - Get all creatures that have been purchased at least once.

SELECT creatures.name, purchases.delivery_via
FROM creatures INNER JOIN purchases
ON creatures.id = purchases.creature_id;

• SQL Engine scans creatures and purchases table.
• For each row in creatures table, it finds a matching purchases.creature_id = creatures.id. If matched it added in the record. If not, thr row is discarded.
• Only rows with valid foreign key matches are returned.

Result -

name	delivery_via
Zorglub	space_shuttle
Xenthar	space_shuttle
Zorglub	wormhole_express

2. LEFT JOIN

It returns all rows from left table, and matched rows from right table. Unmatched rows returns with null. It is also called LEFT OUTER JOIN.

Example - Show all creatures even those that haven’t been purchased by any customer.

SELECT c.name, p.delivery_via 
FROM creatures c LEFT JOIN purchases p
ON c.id = p.creature_id;

For each creatures row, SQL Engine tries to match it with purchases. If no match found, It sill returns creature name with NULL for the right table columns.

Result-

name	delivery_via
Zorglub	space_shuttle
Xenthar	space_shuttle
BlipBlop	NULL

3. RIGHT JOIN

It returns all rows from right table, and matech rows from the left table. Unmatched rows returns with null. Also called RIGHT OUTER JOIN. It is similar to left join but direction reversed.

Example - Show purchases records from the purchases table, along with the matching creature details (if available).

SELECT p.id, p.delivery_via, c.name 
FROM creatures c RIGHT JOIN purchases p
ON c.id = p.creature_id

It returns all purchases with NULL for the left table columns(if any).

id	delivery_via	name
1	space_shuttle	Zorglub
2	space_shuttle	Xenthar
3	wormhole_express	Zorglub

4. FULL OUTER JOIN

It returns all the records, filling NULL wherever there's no match.

SELECT c.name, p.delivery_via
FROM creatures c
FULL OUTER JOIN purchases p
  ON c.id = p.creature_id;

Result-

name	delivery_via
Zorglub	space_shuttle
Zorglub	wormhole_express
Xenthar	space_shuttle
BlipBlop	NULL

5. CROSS JOIN

Cross join returns the Cartesian product of two tables. It combines every row from the first table with every row from the second table. If table creatures has m rows and table purchases has n rows, the result will have m x n rows. It used when you need all possible combinations between tables.

SELECT c.name, p.delivery_via
FROM creatures c
CROSS JOIN purchases p;

name	delivery_via
Zorglub	space_shuttle
Zorglub	space_shuttle
Zorglub	wormhole_express
Xenthar	space_shuttle
Xenthar	space_shuttle
Xenthar	wormhole_express
BlipBlop	space_shuttle
BlipBlop	space_shuttle
BlipBlop	wormhole_express