In a relational database, information is rarely stored in a single place. Customers live in one table, their orders in another, and the products they buy in yet another. To answer a simple question like “which customers ordered which products,” you need a way to bring these tables together. That is exactly what SQL JOINs do.
Understanding joins is one of the most valuable skills for anyone working with data. In this article, we will break down how joins work and explore the main types with clear, practical examples.
Why do we split data into separate tables?
Relational databases are designed to avoid repeating the same information over and over. Instead of writing a customer’s full name and address on every single order, we store the customer once and link each order to that customer through an identifier, usually called a key.
- A primary key uniquely identifies each row in a table (for example, a customer ID).
- A foreign key is a column in one table that points to the primary key of another table.
Joins use these keys to match rows from different tables and present them as if they belonged to a single result.
The basic structure of a JOIN
Most joins follow the same simple pattern: you tell the database which two tables to combine and on which columns they should match.
SELECT customers.name, orders.total
FROM customers
JOIN orders
ON customers.id = orders.customer_id;Here, the database looks at each row in customers and finds the matching rows in orders where the IDs line up. The result is a combined table showing customer names next to their order totals.
The main types of JOINs
The difference between join types comes down to one question: what should happen to rows that do not have a match in the other table?
INNER JOIN
An INNER JOIN returns only the rows that have a match in both tables. If a customer has no orders, they simply will not appear in the result. This is the most common join and a good default when you only care about records that are connected.
LEFT JOIN
A LEFT JOIN returns all rows from the left (first) table, plus any matching rows from the right table. When there is no match, the columns from the right table are filled with empty values (NULL). This is perfect for questions like “list all customers, including those who have never placed an order.”
RIGHT JOIN
A RIGHT JOIN is the mirror image of a LEFT JOIN: it keeps all rows from the right table and fills in NULLs where the left table has no match. In practice, many developers simply rewrite these as LEFT JOINs by swapping the table order, but it is useful to know both exist.
FULL JOIN
A FULL JOIN (also called FULL OUTER JOIN) returns all rows from both tables, matching them where possible and using NULLs where there is no match on either side. It is helpful when you want a complete picture that includes unmatched records from both tables.
A quick comparison
| Join type | Keeps unmatched left rows? | Keeps unmatched right rows? |
|---|---|---|
| INNER JOIN | No | No |
| LEFT JOIN | Yes | No |
| RIGHT JOIN | No | Yes |
| FULL JOIN | Yes | Yes |
Common mistakes to avoid
- Forgetting the ON condition: without it, the database may combine every row with every other row, producing a huge and meaningless result.
- Joining on the wrong columns: always make sure you are matching a foreign key to the correct primary key.
- Ignoring NULLs: outer joins introduce empty values, so remember to handle them when filtering or calculating totals.
Putting it into practice
Joins become intuitive once you experiment with them. Try creating two small tables, filling them with a few rows, and running each type of join to see how the results change. Pay attention to which rows appear and which disappear, and you will quickly build a mental model of how the data connects.
If you want to deepen your understanding of databases and SQL, explore the free database and programming courses available on Cursa. They offer hands-on lessons that take you from writing your first query to designing complete data models.
