Ian Klosowicz
SQL joins let you combine rows from 2 or more tables based on a related column. The one you'll use 90% of the time is the INNER JOIN, which returns only rows where there's a match in both tables. Everything else, LEFT, RIGHT, FULL OUTER, handles the cases where a match doesn't exist on one side or the other.
That's the short answer. But if you keep blanking on which join does what the moment you sit down to write a query, this post is for you.
A join tells SQL how to combine rows from 2 tables. You pick a column that both tables share, usually an ID, and SQL uses that column to match rows together.
The classic example: you've got a customers table and an orders table. Both have a customer_id column. A join on that column lets you pull a customer's name alongside their order details in a single result set.
Without a join, you'd be running 2 separate queries and piecing things together in a spreadsheet. That's how people worked before SQL made it unnecessary.
The type of join you pick controls what happens when a match doesn't exist. A customer with no orders. An order with no matching customer record. The join type decides whether those rows show up or get dropped.
An INNER JOIN returns only the rows where a match exists in both tables. If a customer has no orders, they don't appear. If an order has no matching customer, it doesn't appear either. You get the overlap, nothing else.
SELECT customers.name, orders.order_total
FROM customers
INNER JOIN orders ON customers.customer_id = orders.customer_id;
In practice, I write this at least a dozen times a week. At work I'm pulling from Snowflake, joining fact tables to dimension tables, product IDs to product names, user IDs to user attributes. Almost all of it is INNER JOIN because the data is clean and the matches are expected to exist.
When to use it: you only care about records that have a match on both sides. You don't need the rows that don't connect.
A LEFT JOIN returns all rows from the left table, plus matching rows from the right. Where there's no match on the right, SQL fills those columns with NULL.
SELECT customers.name, orders.order_total
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id;
Result: every customer shows up. Customers who placed orders get their order data. Customers with no orders get NULL in the order columns.
This is the join analysts reach for when they need to find gaps. Who hasn't placed an order? Which users never completed onboarding? Which salespeople have 0 deals this quarter? You LEFT JOIN, then filter for WHERE right_table.id IS NULL.
The "left" and "right" just refer to which table is listed first (left) and which comes after JOIN (right). That's it.
A RIGHT JOIN is the mirror of a LEFT JOIN. It returns all rows from the right table, plus matching rows from the left. Where there's no match on the left, you get NULL.
SELECT customers.name, orders.order_total
FROM customers
RIGHT JOIN orders ON customers.customer_id = orders.customer_id;
Every order shows up. Orders with a matching customer get the customer name. Orders with no matching customer get NULL in the customer columns.
Honestly, most analysts rarely write RIGHT JOIN. If you find yourself reaching for it, you can almost always just swap the table order and write a LEFT JOIN instead. The output is identical, and LEFT JOIN is easier to read because the "primary" table is always listed first.
When to use it: mostly when someone else wrote the query with that table order and you're editing it. Or when a specific tool generates it for you.
A FULL OUTER JOIN returns all rows from both tables. Where a match exists, the columns fill in. Where a match doesn't exist on either side, you get NULL for that table's columns.
SELECT customers.name, orders.order_total
FROM customers
FULL OUTER JOIN orders ON customers.customer_id = orders.customer_id;
You get customers with no orders (NULLs on the right) AND orders with no matching customer (NULLs on the left) all in one result set.
This one comes up less often in day-to-day analysis. Where it's useful: data reconciliation. You're comparing 2 data sources and need to surface every discrepancy, records that exist in one system but not the other, and records that match on both sides. FULL OUTER JOIN gives you the complete picture at once.
Note: MySQL doesn't support FULL OUTER JOIN natively. You'd need to simulate it with a UNION of a LEFT JOIN and a RIGHT JOIN. BigQuery, Snowflake, PostgreSQL, and SQL Server all support it directly.
A CROSS JOIN returns every possible combination of rows from both tables. No matching column needed. If the left table has 5 rows and the right table has 4 rows, you get 20 rows back.
SELECT products.name, colors.color
FROM products
CROSS JOIN colors;
This generates every product-color combination. If you have 10 products and 6 colors, you get 60 rows.
When is this useful? Generating date spines (every possible date in a range), building permutation tables, filling in gaps in time-series data. It's a niche tool, but when you need it, nothing else does the job the same way.
The danger: accidentally cross joining 2 large tables. 1 million rows times 1 million rows is a trillion-row result that will take down your query and possibly your weekend.
The mental model that clicks for most people: think about which rows survive when there's no match.
The Venn diagram everyone draws is useful, but only up to a point. What actually sticks is running these against real data and seeing what disappears.
I've heard from a lot of people trying to break into data who study joins in isolation, get them right on a quiz, and then blank during an interview when asked to explain the difference. The fix is always the same: write the query, look at the output, delete a row from one table, run it again, and see what changes. 10 minutes of that beats 2 hours of reading.
If you want to build this kind of practical fluency fast, the program at Analyst Hive covers exactly the SQL skills that show up in analyst interviews, not all of SQL, just the parts that actually matter on the job.
Once joins click, window functions are the next concept that separates candidates who can only follow a tutorial from ones who can actually write analyst SQL.
What's the difference between INNER JOIN and JOIN?
They're the same thing. Writing JOIN without a keyword in front of it defaults to INNER JOIN in every major SQL dialect. Most analysts just write JOIN, and the query behaves identically. The INNER keyword is optional and mainly there to make the query easier to read.
When should I use a LEFT JOIN instead of an INNER JOIN?
Use LEFT JOIN when you need all records from the first table regardless of whether a match exists in the second. The most common case: finding records that are missing in the second table. INNER JOIN is right when you only want rows where a match exists on both sides.
Why do I get duplicate rows when I JOIN?
Duplicates after a join usually mean the right table has multiple rows matching a single row in the left table. Check whether the column you're joining on is actually unique in both tables. If one side has duplicates, the join multiplies them. Fix it by deduplicating before joining or using an aggregation.
Does the order of tables in a JOIN matter?
For INNER JOIN, no, you get the same rows either way. For LEFT and RIGHT JOIN, yes, the table listed first (left) or second (right) determines which side keeps all its rows. For readability, put the primary table first and join to secondary tables after.
What's a self join?
A self join is when you join a table to itself. This comes up with hierarchical data, like an employee table where each row has a manager_id that references another employee's id in the same table. You alias the table twice and join on the relationship column. It's less common but shows up in interviews.
How many joins can I have in one query?
As many as you need. Most analytical queries join 3 to 6 tables. Beyond that, performance can suffer depending on your database and whether the join columns are indexed. On Snowflake and other cloud warehouses, multi-table joins at scale are generally handled well. The practical limit is readability, if a query has 12 joins, it's usually a sign something should be restructured upstream.
If you're working through SQL as part of a job search, the joins above cover the vast majority of what comes up in take-home assessments and technical interviews. INNER JOIN and LEFT JOIN account for probably 80% of real-world queries. Get those 2 solid before spending much time on the others.
Joins are the backbone of the SQL analysts write every day, so they are worth drilling until they are automatic.
The Analyst Hive program gives you a 90-day daily structure for building exactly this kind of job-ready SQL skill, alongside resume, portfolio, and interview prep. If you're trying to break in without a degree or a bootcamp, that's what it's built for.
