SQL JOINs: How to Join Two Tables Without Losing or Doubling Rows

1. What a join actually does to your rows

Before the explanation: you join two tables and get a result with more rows than either of them had. Nothing errored. What did the database do to produce extra rows out of nowhere?

The Venn diagram is where most people meet joins, and it is a reasonable start. It just describes the wrong thing. It shows which keys survive. What you need to predict is how many rows come out, and those are different questions.

Here is the rule that answers both. A join walks the left table, and for every row it finds every row on the right whose key matches. Then it writes one output row per pair. That single sentence explains everything on this page.

• A left row that finds no match produces zero output rows. It disappears.
• A left row that finds three matches produces three output rows. It is duplicated, and every one of its column values is duplicated with it.

So the output row count is not the left count, and it is not the right count. It is the number of matching pairs, which can be smaller than either table or larger than both.

Grain, the idea that makes joins make sense

The grain of a table is what one row means. Say it out loud before you join anything.

• games has a grain of one row per game. 125,855 rows, 125,855 games.
• recommendations has a grain of one row per player review. 41,154,794 rows, and a popular game owns thousands of them.

Joining those two gives you a table whose grain is one row per review, not one row per game. The game columns are still there, but they now repeat once for every review that game received. The moment you forget that, a SUM on a game column stops meaning what its name says.

Every query on this page nicknames its tables. FROM hidden_gems AS g means "for the rest of this query, call that table g", so g.Name is the Name column of hidden_gems. Two joined tables almost always need those nicknames, because both can carry a column of the same name. SQL aliases, and reading a query out loud covers the whole habit if it is new.

2. The six join types, and what each one keeps

Before the explanation: five of the six join types below match rows on a key. One of them does not match on anything at all. Which do you think it is, and what could it possibly be for?

Each type keeps a different set of rows. The pair rule from section one still applies to all of them. All they change is what happens to rows that found no partner.

CROSS JOIN is the answer to the prequestion, and it is worth knowing precisely because of the accident. Two tables of 1,000 rows each cross-joined produce a million rows. If you ever write a join and forget the ON clause, some databases give you exactly that, and the first sign is a query that never finishes.

A note on the word "outer". LEFT, RIGHT and FULL are all outer joins, and you will see them written as LEFT OUTER JOIN. The word OUTER is optional and means the same thing. Similarly, a plain JOIN with no word in front of it is an INNER JOIN, which is worth remembering because that default is what causes the first failure below.

Two more pieces of syntax show up constantly. ON states the matching condition, as in ON g.AppID = r.app_id. USING (app_id) is a shorthand for when the column has the same name in both tables. And when a join condition is not a plain equals, for example matching a date to a range, that is a non-equi join. It works the same way and it fans out enthusiastically, so count your rows.

3. Failure one: the silent delete

Before the explanation: you join two tables and get fewer rows back than you started with. No error, no warning. Where did the missing rows go, and who decided they should go?

A plain JOIN is an INNER JOIN. It keeps only rows that matched on both sides. Rows that matched nothing are not flagged, not counted, and not mentioned. They are simply absent.

On my two datasets, that mattered enormously. The two tables were collected by different people at different times, so their coverage does not line up. Checking how many games had any matching review at all:

SELECT COUNT(DISTINCT g.AppID) AS games_with_reviews
FROM games_raw AS g
JOIN recommendations AS r ON g.AppID = r.app_id;

The answer was 36,255 out of 125,855 games, which is about 29%. Roughly seven out of ten games in the left table would vanish from any inner join with the review table. Narrowed to the 175 games I actually cared about, about 57 of them had no reviews. An inner join quietly reduced my population by a third before I calculated anything, and the result set looked finished. The columns were right, the ordering was right, the numbers were plausible.

The check that catches it

Two lines, run before you trust anything downstream. First, how many rows went in. Second, how many distinct keys came out.

SELECT COUNT(*) AS rows_in FROM hidden_gems;

SELECT COUNT(DISTINCT g.AppID) AS keys_out
FROM hidden_gems AS g
JOIN recommendations AS r ON g.AppID = r.app_id;

If keys_out is smaller than rows_in, your join is dropping records, and you now have a decision to make rather than an accident to discover later. Either the drop is fine and you say so in writing, or it is not and you switch to a LEFT JOIN.

Write the match rate down either way. "The playtime analysis covers 118 of 175 games, or 67%, because the review dataset does not include the rest" is a sentence that makes a reviewer trust the whole document. Leaving it out is what makes them stop trusting it.

The anti-join, and why an empty column is a finding

A LEFT JOIN keeps every row from the left table whether it matched or not, filling the right side with NULL. That gives you a way to ask the question an inner join cannot answer: which rows found nothing? Keep everything, then keep only the ones where the right side came back empty. This pattern is called an anti-join, and it shows up in interviews constantly.

SELECT g.Name, g.pct_positive
FROM hidden_gems AS g
LEFT JOIN recommendations AS r ON g.AppID = r.app_id
WHERE r.app_id IS NULL
ORDER BY g.pct_positive DESC;

Read the WHERE clause carefully, because it is the part that surprises people. r.app_id IS NULL cannot be true for any row that matched, since a matched row has a real value there. It is only true for rows the LEFT JOIN invented to keep an unmatched left row alive. So the filter means "keep the rows that found nothing".

When I ran it, the 57 missing games were not noise. They fell into three groups, and every group told me something. Several were mainstream blockbusters that an earlier data-quality pass had already flagged for impossible price and ownership values, and a completely separate method fingering the same rows is strong confirmation those rows are broken. Others were released after the reviews were collected in August 2024, so they could not possibly match, which turned a vague limitation into a nameable list. The rest were genuinely uncovered titles the review dataset never sampled.

None of that would have existed in an inner join. An empty column is not a hole in your work. It is often the most interesting result you have, and it belongs in your write-up: documenting data limitations covers how.

4. Failure two: the silent multiply

Before the explanation: one customer has three orders. You join customers to orders, then sum a column that lives on the customer, like their credit limit. What number comes out, and why is it not the credit limit?

When a game with 2,498 reviews is joined to the review table, that one game becomes 2,498 rows. Its name repeats 2,498 times. Its price repeats 2,498 times. This is called fan-out, and the trouble starts the moment you aggregate a column that came from the one-row-per-game side.

Here is the wrong query. It looks entirely reasonable.

SELECT g.Name, SUM(g.Price) AS total_price
FROM hidden_gems AS g
JOIN recommendations AS r ON g.AppID = r.app_id
GROUP BY g.AppID, g.Name;

For one game in my data, priced at $3.49 with 2,498 matched reviews, that SUM returns 8,718.02. The price of the game is still $3.49. The query added it up once per review, because after the join there are 2,498 rows carrying that price. No error, no warning, and a number roughly 2,500 times too big sitting in a column labelled total price.

Averages hide this better than sums, which makes them more dangerous. AVG(g.Price) across 2,498 duplicated rows returns $3.49, the right answer, so nothing looks wrong. Then the same pattern meets a game with two rows in a second joined table. The duplication stops being uniform, and the average silently starts weighting some records more than others. Say out loud why uneven duplication breaks an average but even duplication does not, before you read on. That one is the hinge of this whole guide.

The check that catches it

Compare total rows to distinct keys. If they differ, you have fan-out, and you should know its size before you aggregate.

SELECT COUNT(*) AS joined_rows,
       COUNT(DISTINCT g.AppID) AS distinct_games
FROM hidden_gems AS g
JOIN recommendations AS r ON g.AppID = r.app_id;

If joined_rows is much larger than distinct_games, the join expanded your table, which is completely fine as long as you meant it. Fan-out is not a bug. It is what a one-to-many join is for. It only becomes a bug when you aggregate as if it did not happen.

The other half of the fix is HAVING. Once you group, a filter on an aggregate has to run after the grouping, which is what HAVING does and WHERE cannot.

SELECT g.Name,
       g.Price,
       COUNT(r.review_id) AS num_reviews,
       ROUND(AVG(r.hours), 1) AS avg_hours
FROM hidden_gems AS g
JOIN recommendations AS r ON g.AppID = r.app_id
GROUP BY g.AppID, g.Name, g.Price
HAVING COUNT(r.review_id) >= 50
ORDER BY avg_hours DESC;

That floor of 50 matters more than it looks. Without it, a game with three reviews and one obsessive player outranks everything else. With it, the top of my list became games with hundreds or thousands of reviews behind their averages, which is the difference between a finding and an artifact. There is more on choosing that number honestly in data-driven thresholds.

5. The full before and after

Same two tables, same question. The first version runs and lies. The second runs, tells the truth, and states its own coverage.

Before

SELECT g.Name,
       SUM(g.Price) AS total_price,
       AVG(r.hours) AS avg_hours
FROM hidden_gems AS g
JOIN recommendations AS r ON g.AppID = r.app_id
GROUP BY g.AppID, g.Name
ORDER BY avg_hours DESC;

Three problems, none of which produce an error. SUM(g.Price) is multiplied by the review count. The inner join has removed every game with no reviews, unmentioned. And with no minimum review count, a game with two reviews can top the ranking.

After

-- ============================================================
-- STEP 6: Which games do players actually keep playing?
-- WHY: A high rating says people liked it once. Average hours
--      says they stayed. Joining the game list to real playtime
--      is the only way to tell those apart. The 50-review floor
--      keeps a single obsessive player from topping the chart.
-- COVERAGE: 118 of 175 games matched the review data (67%).
--      The 57 unmatched games are listed separately, not dropped.
-- ============================================================
--SELECT the game name, its price, how many reviews matched,
--   and the average hours those reviewers played:
--   COUNT(r.review_id) = number of matched review rows per game
--   AVG(r.hours)       = mean hours across those reviews
--   ROUND(..., 1)      = round to one decimal place
--FROM the hidden_gems list (g), joined to reviews (r) on the game id
--GROUP BY the game, so each game collapses to one row
--HAVING at least 50 matched reviews, so the average is trustworthy
--ORDER BY average hours, highest first
SELECT g.Name,
       g.Price,
       COUNT(r.review_id) AS num_reviews,
       ROUND(AVG(r.hours), 1) AS avg_hours
FROM hidden_gems AS g
JOIN recommendations AS r ON g.AppID = r.app_id
GROUP BY g.AppID, g.Name, g.Price
HAVING COUNT(r.review_id) >= 50
ORDER BY avg_hours DESC;

The price column is grouped rather than summed, so it stays $3.49. The coverage line names what the join removed, so a reader knows the scope. The floor stops the ranking from being decided by a sample of two. The comment format is the one from how to comment SQL so it teaches.

Now picture the last join you wrote against your own data. Which of those three problems is most likely already sitting in it, and what would the row count have told you?

6. Edge cases that break joins in real data

Before the explanation: a join returns zero rows. Not few. Zero. The tables are both full and the ids look identical on screen. What would you check first?

Seven things that have each stopped someone for an afternoon.

The key columns are named differently. games_raw.AppID and recommendations.app_id are the same number from the same source, spelled two ways because two people built the datasets. What matters is that they hold the same identifier, not that they share a name.

The key columns have different types. This is the answer to the prequestion. An id stored as text in one table and a number in the other matches nothing at all, and the result is an empty table rather than an error. If a join returns zero rows and you expected thousands, check the types before anything else.

A boolean is stored as text. In the review table, is_recommended holds the lowercase text 'true' and 'false', not 1 and 0. Filtering on is_recommended = 1 matches nothing and returns a count of zero, which reads as a real answer.

NULLs in the join key. A NULL key never matches anything, including another NULL, because NULL means unknown rather than empty. Rows with a missing key drop out of an inner join without a word. Count them first with SELECT COUNT(*) FROM t WHERE key_column IS NULL.

Joining many to many. If both sides hold several rows per key, the output multiplies both ways: three rows on the left and four on the right produce twelve. This is almost never what anyone wants and it is the classic cause of an inflated total. Aggregate one side down to one row per key first, then join.

The join is slow because one side is huge. Matching 125,855 rows against 41 million takes real work. An index on the key column of the large table turns a full scan into a lookup: CREATE INDEX idx_rec_appid ON recommendations(app_id);. Build it once and every join afterward is quick. Handling large datasets covers the rest of working at this size.

The two tables describe different moments. My game list and my review list were collected at different times, so some unmatched rows are not data errors, they are calendar. A join makes it trivially easy to combine sources that were never meant to be combined, which is exactly why the write-up has to say where each source came from.