SQL JOINS Explained

Running into Joins can be one of the most daunting and confusing parts for anyone new to SQL. In this articles we will explore what they are, what types exist, when to use them and why they matter. Lets dive in,

What Are Joins?

A join combines rows from two or more tables based on a related column between them. Relational databases like PostreSQL split data into separate tables to avoid duplication , so joins are how you stitch that data back together when you need it.

For example, you might have a customers table and an orders table. Each order has a customer id that points back to a row in customers. A join lets you show each order along with the customer's name even though that information lives in two different tables.

For our examples we will make two tables;
A customers table

An orders table

Types of Joins

1. INNER JOIN

Returns only the rows that have matching values in both tables. This is the default join type and the one you'll use most often.

You will notice that Charlie disappears (no orders) and order 4 disappears (no matching customer). Only rows present on both sides survive.

2. LEFT JOIN (LEFT OUTER JOIN)
Returns all rows from the left table and matching rows from the right table. If there's no match, the right side is filled with NULL.

In this case Charlie shows up even though he has no orders.

3. RIGHT JOIN (RIGHT OUTER JOIN)
This works as the reverse of the left join and returns all rows from the right table, plus matching rows from the left. Non-matches on the left become NULL.

Order 4 (Monitor) shows up with a NULL customer name, since customer_id = 5 doesn't exist.

In a realistic setting, RIGHT JOIN is used far less often than LEFT JOIN as most people just swap the table order and use LEFT JOIN instead, since it reads more naturally.

4. FULL JOIN (FULL OUTER JOIN)

Returns all rows from both tables, matching where possible and filling NULL where there's no match on either side.

This gives you the complete picture where everyone and everything is matched where possible.

5. CROSS JOIN

Returns the Cartesian product of two tables. Every row from the first table paired with every row from the second. No ON clause is needed (or used).

This is mostly useful for generating combinations. For example, a catalogue where every product size needs to be paired with every color available.

6. SELF JOIN

This is a table joined to itself. It is most commonly used for hierarchical or comparative data. For example an employees table where each row has a manager id pointing to another row in the same table.

So in summary

INNER JOIN - You only want records that exist in both tables; orders that have a valid customer.

LEFT JOIN - You want everything from your "main" table, even if there's no related data; all customers, including ones who haven't ordered anything yet.

RIGHT JOIN - Rare in practice; usually rewritten as a LEFT JOIN with tables swapped.

FULL JOIN - You need to see unmatched records from both sides; auditing data to find empty rows on either side.

CROSS JOIN - You deliberately need every combination of two sets.

SELF JOIN - Your table references itself; organization charts "who reports to whom."

Wrapping Up

Joins are the backbone of relational SQL. The best way to really internalize these examples is to do them for yourself, load in a couple of small tables like the ones above and run these queries. Seeing it all come together based on the JOIN used really helps to simplify the concept and the use cases become obvious.