Joins
RIGHT JOIN, FULL JOIN, and CROSS JOIN
So far, you have used INNER JOIN and LEFT JOIN.
PostgreSQL also supports RIGHT JOIN, FULL JOIN, and CROSS JOIN.
RIGHT JOIN
A RIGHT JOIN works like a LEFT JOIN, except PostgreSQL keeps every row from the table on the right side of the join.
To see this, first add a customer who has no orders:
12345678INSERT INTO customers (id, email, full_name, country, created_at)VALUES ( 10001, 'Customer10001@Example.com', 'Customer 10001', 'US', '2025-01-01');Now run:
12345678SELECT c.id AS customer_id, c.full_name, o.id AS order_idFROM orders oRIGHT JOIN customers c ON o.customer_id = c.idWHERE c.id = 10001;You should see:
123 customer_id | full_name | order_id-------------+----------------+---------- 10001 | Customer 10001 | NULLThere is no order for Customer 10001, but the customer still appears because customers is the table on the right side of the RIGHT JOIN.
The columns from orders contain NULL because no matching order was found.
A simplified view looks like this:
RIGHT JOIN can usually be written as LEFT JOIN
Consider the query we just used:
123FROM orders oRIGHT JOIN customers c ON o.customer_id = c.idWe can produce the same result by reversing the table order and using a LEFT JOIN:
123FROM customers cLEFT JOIN orders o ON c.id = o.customer_idIn both cases, all rows from customers are preserved.
For this reason, you will often see LEFT JOIN used instead of RIGHT JOIN. Reordering the tables can make the query easier to read from left to right.
FULL JOIN
A FULL JOIN, also called a FULL OUTER JOIN, keeps unmatched rows from both tables.
It combines the behavior of a LEFT JOIN and a RIGHT JOIN.
If two rows match, PostgreSQL combines them normally.
If a row from the left table has no match, PostgreSQL still returns it and fills the right-side columns with NULL.
If a row from the right table has no match, PostgreSQL also returns it and fills the left-side columns with NULL.
A simplified example looks like this:
The first row matched.
The second row existed only on the left, so the right-side value is NULL.
The third row existed only on the right, so the left-side value is NULL.
This is the main difference between the outer join types:
12345LEFT JOIN → keep every row from the left tableRIGHT JOIN → keep every row from the right tableFULL JOIN → keep every row from both tablesFULL JOIN is useful when you need to compare two sets of data and also see the rows that exist on only one side.
CROSS JOIN
A CROSS JOIN works differently from the joins you have seen so far.
There is no join condition.
Instead, PostgreSQL combines every row from the first table with every row from the second table.
Let's use two customers and three products:
12345678SELECT c.full_name, p.name AS product_nameFROM customers cCROSS JOIN products pWHERE c.id IN (1, 2) AND p.id IN (1, 2, 3)ORDER BY c.id, p.id;You should see:
12345678 full_name | product_name------------+-------------- Customer 1 | Product 1 Customer 1 | Product 2 Customer 1 | Product 3 Customer 2 | Product 1 Customer 2 | Product 2 Customer 2 | Product 3We selected two customers and three products.
A CROSS JOIN combines each customer with every product:
12 customers × 3 products = 6 rowsA simplified view looks like this:
Unlike the other joins we have used, a CROSS JOIN does not need an ON clause because PostgreSQL is not trying to determine which rows match.
It deliberately produces every possible combination.
This also means the result can become very large.
For example, the customers table contains 10,000 rows and the products table contains 5,000 rows. A cross join between all of them would produce:
110,000 × 5,000 = 50,000,000 rowsThis is why CROSS JOIN should be used only when every combination is actually what you want.
Before continuing, remove the customer added for this lesson:
12DELETE FROM customersWHERE id = 10001;