What a JOIN Actually Does
Before the four join types, understand the one thing they all share. A JOIN takes two tables and produces a combined table by matching rows on a key column. Think of it like two spreadsheets: a sales sheet with a customer_id column, and a customers sheet with an id column. A join matches each sale to its customer by connecting customer_id to id, and brings the customer columns into the result.
The key column must exist in both tables and hold the same kind of value — a customer ID in one, the same customer ID in the other. If the key does not line up, the join produces missing or duplicated rows, and that is where most of the confusion lives. In this guide I use the same two tables throughout — orders and customers — so you can see the difference between the joins with the exact same data.
Let me set up the example tables, because every join below uses them. The orders table has three rows: order 1 by customer 101 for $120, order 2 by customer 102 for $85, and order 3 by customer 104 for $200. The customers table has customer 101 (Anna), 102 (Ben), and 103 (Cara). Notice customer 104 has an order but no customer record, and customer 103 has a record but no order. Those two unmatched rows are exactly what the different joins handle differently.
INNER JOIN: Keep Only the Rows That Match
INNER JOIN keeps only the rows that exist in both tables. If an order has no matching customer, or a customer has no order, those rows disappear from the result. This is the join you use when you only want the complete rows — for example, orders that can be linked to a real customer. It is the most common join and the default mental model most people start with.
SELECT orders.order_id, orders.amount, customers.name FROM orders INNER JOIN customers ON orders.customer_id = customers.id. Read the ON clause as 'match orders to customers where the order's customer_id equals the customer's id.' The INNER keyword is optional — plain JOIN means INNER JOIN in most databases, but writing it out makes your intent clear to anyone reading your query.
Run that query on the example data and you get two rows: order 1 with Anna ($120) and order 2 with Ben ($85). Order 3 has no customer (104 is missing from customers), so it drops out. Customer 103 (Cara) has no order, so she drops out too. INNER JOIN gives you exactly the intersection — orders that have a real customer behind them.
Use INNER JOIN when every row in the result must be complete. If you are building a report of 'orders with their customers' and an order with no customer is meaningless to you, INNER JOIN is right. The moment you want to keep unmatched rows — like 'show me all orders even if the customer data is missing' — you move to LEFT JOIN. Knowing when to switch is the real skill.
LEFT JOIN: Keep Every Row from the Left Table
LEFT JOIN keeps every row from the table on the left (the first one you name), even if there is no match in the right table. Where there is no match, the right table's columns come back as NULL. This is the join you will use most in real work, because most of the time you want 'all the orders, with customer info when it exists' — not just the ones that happen to have a customer.
SELECT orders.order_id, orders.amount, customers.name FROM orders LEFT JOIN customers ON orders.customer_id = customers.id. The left table here is orders. This query says 'give me every order, and bring in the customer name if the customer exists.' Order is the left table because it comes first in the FROM clause.
Run it and you get three rows: order 1 (Anna, $120), order 2 (Ben, $85), and order 3 ($200 with a NULL customer name). Order 3 stays because you asked for every order; since customer 104 does not exist, its name is NULL. This is the 'all orders, with names when we have them' report that INNER JOIN dropped order 3 from.
The NULLs from a LEFT JOIN are how you find 'orphan' rows. To see all orders with no valid customer, add WHERE customers.id IS NULL to the LEFT JOIN. That single pattern — LEFT JOIN plus IS NULL — is one of the most useful queries in analytics, and it is exactly how you find broken data that an INNER JOIN would silently hide.
RIGHT JOIN: Keep Every Row from the Right Table
RIGHT JOIN is the mirror of LEFT JOIN: it keeps every row from the table on the right, filling unmatched left-table rows with NULL. In practice you will use RIGHT JOIN rarely, because you can almost always rewrite it as a LEFT JOIN by swapping the table order — which is clearer to read. But you should recognize it, because you will see it in other people's code and in generated queries.
SELECT orders.order_id, orders.amount, customers.name FROM orders RIGHT JOIN customers ON orders.customer_id = customers.id. Here the right table is customers, so you keep every customer. That means Cara (customer 103) appears even though she has no order — her order columns come back as NULL.
Run it and you get three rows: order 1 (Anna), order 2 (Ben), and customer 103 (Cara) with NULL order_id and amount. Notice the result is the same rows as the LEFT JOIN would give if you put customers on the left. That is the key insight: RIGHT JOIN and LEFT JOIN return the same data; they just start from different tables.
My advice: avoid RIGHT JOIN in your own queries and use LEFT JOIN instead by swapping the table order. LEFT JOIN is easier for most people to read because you read top-to-bottom with the primary table first. I would rather read 'FROM orders LEFT JOIN customers' than 'FROM customers RIGHT JOIN orders.' But when you meet RIGHT JOIN in someone else's code, now you know it just means 'keep the right table's rows.'
FULL OUTER JOIN: Keep Everything from Both Sides
FULL OUTER JOIN keeps every row from both tables, matching where it can and filling NULL where it cannot. This is the join for 'show me everything from both tables, matched where possible.' It is the least common in day-to-day work but invaluable for finding all the gaps — orders with no customer and customers with no orders, all in one result.
SELECT orders.order_id, orders.amount, customers.name FROM orders FULL OUTER JOIN customers ON orders.customer_id = customers.id. This keeps every order and every customer. Order 3 stays (with NULL name) because it has no customer; customer 103 (Cara) stays (with NULL order) because she has no order. Only order 1 and 2 have both sides filled.
Not every database supports FULL OUTER JOIN. MySQL and MariaDB do not support it directly — you have to emulate it with a UNION of a LEFT JOIN and a RIGHT JOIN. PostgreSQL, SQL Server, and Oracle do support it. If you work in MySQL and need the full outer behavior, search for 'emulate full outer join mysql' and you will find the standard UNION pattern.
FULL OUTER JOIN is your audit tool. Run it, then filter to the rows where either side is NULL, and you have a complete list of every broken relationship in your data — orders pointing to no customer, and customers with no orders. That is a great first step when you inherit a messy database and need to understand what you are working with.
Which Join Should You Use? (The Decision Rule)
Here is the mental shortcut that makes choosing a join automatic. First, decide which table is your primary table — the one whose rows you care about most. Then decide whether you want to keep unmatched rows from that primary table. That gives you your answer. The four joins are not four different tools; they are two decisions applied to two tables.
Your primary table is the one you are building the report around. If the report is 'all orders with customer info,' orders is primary. If it is 'all customers with their order totals,' customers is primary. Most of the time there is an obvious answer, and picking it first removes most of the confusion about which join to use.
Ask: do I want rows from my primary table even when there is no match on the other side? If yes, use LEFT JOIN (primary table on the left). If no — I only want rows that have a match — use INNER JOIN. That is the whole decision. RIGHT JOIN is only when someone wrote the query with the primary table on the right, and FULL OUTER JOIN is when you want both sides' unmatched rows.
After any join, check the row count. An INNER JOIN should return fewer or equal rows than the smaller side. A LEFT JOIN should return the same number of rows as your primary (left) table — unless the right table has duplicate keys. If the count is higher than expected, the right table has duplicates, and that is a separate bug (covered below). This count check catches most join mistakes in seconds.
Write a comment above every join stating the business question: '-- all orders, keeping unmatched orders (LEFT)' or '-- only orders with a valid customer (INNER)'. When you return to a query months later, or a colleague reads it, the comment tells you the intent. I cannot count the times a one-line comment saved me from re-deriving why I chose a particular join.
The Join Mistakes That Bite Everyone
The joins themselves are simple; the errors come from the data and from a few habits. I have made every one of these, and they all produce numbers that look right and are wrong. Watch for these three and you will skip the most frustrating debugging sessions. The common thread: a join is only as good as your keys.
If the customer table has customer 101 twice, every order by 101 will appear twice in the result — once per matching customer row. This is the number one join bug. It does not error; it just silently duplicates rows and inflates your totals. The fix: check for duplicates on the key before joining with SELECT id, COUNT(*) FROM customers GROUP BY id HAVING COUNT(*) > 1. If any come back, deduplicate first.
Joining on customer_id to order_id instead of customer_id to id produces nonsense matches or an empty result. The fix is to always state the join columns explicitly and verify they hold the same kind of value. If one is an integer ID and the other is a string like 'CUS-101', they will never match. Check the data types of both key columns before you join.
If a key column contains NULL, those rows will not match anything — NULL never equals NULL in a join. If some orders have a NULL customer_id, a LEFT JOIN shows them with NULL customer info, and an INNER JOIN silently drops them. Decide whether NULL-keyed rows should be kept, and make that explicit. This is a data-quality issue that a join exposes but does not fix.
Before you trust any joined total, sanity-check against a single-table number. If you know total orders is 1,000 from a simple COUNT(*) on orders, then a joined query that sums orders should still reference 1,000 rows. When a joined total is way off, it is almost always a duplicate-key problem. This validation loop — known count, joined count — is your safety net.
Practice: Join These Yourself
The fastest way to make joins stick is to run them on data where you already know the answer. Below are three practice tasks using the same two tables, designed to build from one join to combining several. Do them in order, and check each result against what you expect from the example data — that verification is the whole point.
Write the INNER JOIN that returns orders with a matching customer, and confirm you get exactly two rows (order 1 and 2). If you get order 3 too, you wrote a LEFT JOIN. If you get more rows, check for duplicate keys. This task confirms you can write the basic syntax and read the result.
Write the LEFT JOIN, then add WHERE customers.id IS NULL to find orders with no customer. Confirm you get exactly order 3. This is the orphan-finding pattern that is essential in real work, and doing it once by hand makes it stick.
Write a query that shows total amount per customer name using a LEFT JOIN and a GROUP BY: SELECT customers.name, SUM(orders.amount) FROM orders LEFT JOIN customers ON orders.customer_id = customers.id GROUP BY customers.name. This combines everything — join, group, and aggregate — into one realistic report. Notice how the orphan order shows up as a NULL-name group.
If you do not have a database handy, use an online SQL playground (like SQLite in the browser or a free PostgreSQL instance) and create the two small tables with CREATE TABLE and INSERT. Reproducing the exact example data means you can check your results against the known answers in this guide. The practice is what makes joins automatic; the setup is ten minutes once.
