PostgreSQL Joins
Combining Rows from Multiple Tables in PostgreSQL
In PostgreSQL, joins allow you to pull together rows from two or more tables, based on a shared relationship—usually matching values in columns.
Inner Join
Brings only matching entries from both tables.
Example: Combine "orders" and "clients" where their IDs are the same:
SELECT orders.id, clients.name FROM orders JOIN clients ON orders.client_id = clients.id;
Left Join (or Left Outer Join)
Gets all rows from the first table and the matched ones from the second. If there’s no match, the second table's columns will show as NULL.
Example:
SELECT clients.name, orders.id FROM clients LEFT JOIN orders ON clients.id = orders.client_id;
Right Join (or Right Outer Join)
Similar to Left Join, but starts with the second table. All its entries appear, even if no match exists in the first.
Example:
SELECT clients.name, orders.id FROM clients RIGHT JOIN orders ON clients.id = orders.client_id;
Full Join (or Full Outer Join)
Combines Left and Right joins—displays everything from both, with NULL where matches are missing.
Example:
SELECT clients.name, orders.id FROM clients FULL JOIN orders ON clients.id = orders.client_id;
Cross Join
Matches each entry in one dataset with all entries from the other, forming every potential row pairing.
Example:
SELECT clients.name, products.title FROM clients CROSS JOIN products;
Self Join
A table can be linked with itself. Commonly applied to relate entries from the same table for internal reference or comparison.
Example:
SELECT a.name AS person, b.name AS manager FROM employees a JOIN employees b ON a.manager_id = b.id;
Prefer Learning by Watching?
Watch these YouTube tutorials to understand POSTGRESQL Tutorial visually:
What You'll Learn:
- 📌 POSTGRESQL JOINS [Complete guide in 12 mins]
- 📌 6 SQL Joins you MUST know! (Animated + Practice)