LEFT JOIN vs RIGHT JOIN in SQL

Short answer

• A LEFT JOIN returns every row from the left table, the table named first, plus matching rows from the right table. A left row with no match still appears, with NULL in the right table columns.
• A RIGHT JOIN returns every row from the right table, the table named second, plus matching rows from the left table. A right row with no match still appears, with NULL in the left table columns.
• The two joins are mirror images. Any RIGHT JOIN can be rewritten as a LEFT JOIN by swapping the two tables, and the result is identical.
• An INNER JOIN keeps only matched pairs, so it drops unmatched rows from both sides. A LEFT JOIN keeps unmatched left rows, a RIGHT JOIN keeps unmatched right rows, and a FULL OUTER JOIN keeps unmatched rows from both sides.
• LEFT JOIN and LEFT OUTER JOIN mean the same thing, and the same holds for RIGHT JOIN and RIGHT OUTER JOIN. The word OUTER is optional.
• A filter on the right table placed in the WHERE clause can undo a LEFT JOIN, because it removes the NULL rows the join just preserved. Put right table filters in the ON clause when the preserved rows must survive.
• Most teams write LEFT JOIN and avoid RIGHT JOIN, because a query reads more naturally when the preserved table comes first. MySQL documentation recommends the same choice for portability.

A query that joins two tables can return three rows, five rows, or four rows from the same data, depending on one word in the FROM clause. That word decides which table is allowed to keep its unmatched rows, and the rows it drops disappear silently, with no error and no warning. The difference between LEFT JOIN and RIGHT JOIN is exactly that choice: which side survives when there is no match.

This guide works through one small dataset again and again, so every result can be checked by hand. It covers what each join returns, the mirror rule that turns any RIGHT JOIN into a LEFT JOIN, the common patterns for finding unmatched rows, the ON versus WHERE trap that quietly converts an outer join back into an inner join, and the places where database systems disagree. Every result table in this guide is the printed output of a query run against SQLite 3.45.1, not a hand written guess.

What a LEFT JOIN Returns

A LEFT JOIN starts with an inner join and then adds back the left rows that found no partner. PostgreSQL documentation states the rule in two sentences: first, an inner join is performed; then, for each row in the first table that does not satisfy the join condition with any row in the second table, a joined row is added with null values in the columns of the second table. The joined table therefore always has at least one row for each row in the first table.

The word “left” refers to position in the query text, not to importance. The left table is the table named right after FROM. The right table is the table named after the JOIN keyword. In customers LEFT JOIN orders, customers is the left table and orders is the right table, so every customer is kept.

Use a LEFT JOIN when the left table is the list that must stay complete. A list of all customers, including customers who have never ordered, is the classic case. A list of all products, including products that have never sold, is another. A list of all departments, including departments with no employees, is a third. In each case the right table adds information where it exists, and NULL marks the places where it does not.

The NULL values in a LEFT JOIN result are not missing data in the source tables. They are the join saying, in the only way it can, that no matching row exists. That distinction matters when you read the result. A NULL order total for Sofia Diaz does not mean her order total is unknown. It means she has no order row at all. If you treat that NULL as a zero in a sum, or filter it out without thinking, you change the answer the query gives.

What a RIGHT JOIN Returns

A RIGHT JOIN applies the same rule from the other side. PostgreSQL documentation defines it as the converse of a left join: first, an inner join is performed; then, for each row in the second table that does not satisfy the join condition with any row in the first table, a joined row is added with null values in the columns of the first table. The result table will always have a row for each row in the second table.

In customers RIGHT JOIN orders, orders is the right table, so every order is kept, including an order whose customer does not exist. The customer columns for that order appear as NULL. Nothing about the data changed between the two queries. Only the preserved side changed, and with it the row count and the NULL pattern.

Use a RIGHT JOIN when the right table is the list that must stay complete. A list of all orders, including orders with no matching customer, is the natural case. That situation arises more often than beginners expect: imported orders can point to a customer record that was deleted, a guest checkout can leave an order with no customer row, or a data load can carry a mistyped identifier. A RIGHT JOIN keeps those rows visible so they can be investigated, instead of dropping them silently the way an inner join would.

The practical consequence is that a RIGHT JOIN is a diagnostic tool as much as a reporting tool. When a total looks too small, or a count looks too low, switching the join to the side you suspect is losing rows is often the fastest way to see what is missing. The unmatched rows appear as NULL on the preserved side, and the NULL pattern tells you exactly which side failed to match.

The Sample Tables Used Throughout This Guide

Every worked example uses the same small tables, so each result can be traced back to the same source rows. The tables are the same customers and orders tables used in the companion guide to Inner Join in SQL, which explains the matching only case in full. Reusing the same data makes the comparison direct: the only thing that changes between the guides is the join type.

Customers:

Orders:

Priya Sharma has two orders. Marcus Lee has one order. Sofia Diaz and Tom Reed have no orders. Order 104 points to customer_id 99, which does not exist in the customers table. That orphan order is deliberate. It lets you see that a LEFT JOIN drops unmatched rows from the right side, while a RIGHT JOIN keeps them, and that the two joins are not interchangeable.

Two extra tables appear later, for the three table example. Order items record which products appear on each order, and products record product names and prices.

Order items:

Products:

First Worked Example: The INNER JOIN Baseline

Before comparing LEFT JOIN and RIGHT JOIN, fix the baseline. An inner join keeps only the pairs that match, so it drops Sofia Diaz, Tom Reed, and order 104 all at once.

SELECT c.name, o.order_id, o.total
FROM customers AS c
INNER JOIN orders AS o
  ON c.customer_id = o.customer_id
ORDER BY o.order_id;

Result:

Three rows. Priya Sharma appears twice because she has two matching orders. Sofia Diaz and Tom Reed do not appear, because they have no orders. Order 104 does not appear, because customer 99 does not exist. No error is raised for any of the three dropped rows. They are simply absent, which is why an inner join result can look correct while silently hiding missing data. The full behavior of the inner join, including duplicate keys, NULL keys, and grouping, is covered in the companion guide to Inner Join in SQL.

Second Worked Example: LEFT JOIN Keeps Every Customer

Change one word, from INNER JOIN to LEFT JOIN, and the result changes in a specific way: the two customers with no orders appear, with NULL in the order columns.

SELECT c.name, o.order_id, o.total
FROM customers AS c
LEFT JOIN orders AS o
  ON c.customer_id = o.customer_id
ORDER BY c.customer_id, o.order_id;

Result:

Five rows. The first three rows are identical to the inner join result. The two new rows are Sofia Diaz and Tom Reed, each with NULL for order_id and total. Order 104 is still absent, because a LEFT JOIN preserves the left table only, and order 104 is a right table row with no left partner.

Count the rows before you trust the result. Four customers produce five result rows, because Priya Sharma matches twice and the two unmatched customers each contribute one NULL row. If a LEFT JOIN returns exactly as many rows as the left table has, every left row matched at most once. If it returns more, some left row matched more than once. If it returns fewer, something is wrong, because a LEFT JOIN can never return fewer rows than the left table contains.

Third Worked Example: RIGHT JOIN Keeps Every Order

Now change the join to RIGHT JOIN, keeping the same tables in the same order. The preserved side flips, and so does the NULL pattern.

SELECT c.name, o.order_id, o.total
FROM customers AS c
RIGHT JOIN orders AS o
  ON c.customer_id = o.customer_id
ORDER BY o.order_id;

Result:

Four rows. The first three rows are identical to the inner join result again. The new row is order 104, with NULL in the customer name column. Sofia Diaz and Tom Reed are now absent, because a RIGHT JOIN preserves the right table only, and they are left table rows with no right partner.

The total column tells the same story in numbers. Summing the totals in the LEFT JOIN result gives 135.75, which is 42.5 plus 18.0 plus 75.25. Summing the totals in the RIGHT JOIN result gives 195.75, which adds the 60.0 from order 104. Neither sum is wrong. They answer different questions. The LEFT JOIN sum is the total of orders placed by known customers. The RIGHT JOIN sum is the total of all orders in the table. A report that uses one sum while the reader expects the other is wrong by 60.0, and the query will not warn you.

All Four Joins Side by Side

The same tables and the same condition produce four different results, one per join type. A FULL OUTER JOIN keeps unmatched rows from both sides, so it returns six rows: the three matched pairs, the two unmatched customers, and the one orphan order.

SELECT c.name, o.order_id, o.total
FROM customers AS c
FULL OUTER JOIN orders AS o
  ON c.customer_id = o.customer_id
ORDER BY o.order_id;

Result:

The comparison table below collects the four results. Read it as a decision aid: pick the join whose kept rows match the question you are asking.

Two facts follow from the table. First, the inner join result is contained inside every outer join result for the same tables and condition, because every outer join starts by performing the inner join and then adds rows. Second, the LEFT JOIN and RIGHT JOIN results overlap but neither contains the other: the LEFT JOIN has the two unmatched customers and lacks the orphan order, while the RIGHT JOIN has the orphan order and lacks the two unmatched customers. Only the FULL OUTER JOIN contains both.

The Mirror Rule: Every RIGHT JOIN Is a LEFT JOIN With the Tables Swapped

A RIGHT JOIN is never the only way to write a query. Swap the two tables, change RIGHT JOIN to LEFT JOIN, and the result is the same set of rows. This is the most useful fact in this guide, because it means you can always write the join that reads most clearly, and you can always check a RIGHT JOIN by rewriting it as a LEFT JOIN.

These two queries return identical rows:

SELECT o.order_id, c.name, o.total
FROM orders AS o
RIGHT JOIN customers AS c
  ON o.customer_id = c.customer_id
ORDER BY c.customer_id, o.order_id;

Result:

SELECT o.order_id, c.name, o.total
FROM customers AS c
LEFT JOIN orders AS o
  ON c.customer_id = o.customer_id
ORDER BY c.customer_id, o.order_id;

Result:

The two result tables match row for row. The first query preserves customers by naming them second and using RIGHT JOIN. The second query preserves customers by naming them first and using LEFT JOIN. Most readers find the second version easier to follow, because the preserved table is introduced first and the reading order matches the logic: start with all customers, then attach their orders.

That reading order is the practical reason teams standardize on LEFT JOIN. A query with several joins is read from left to right, and a LEFT JOIN keeps the table you are already reading. A RIGHT JOIN forces the reader to hold the first table in mind while the second table is declared the preserved one. MySQL documentation makes the same recommendation for a different reason, portability: it says that to keep code portable across databases, you should use LEFT JOIN instead of RIGHT JOIN. Both reasons point the same way. Use RIGHT JOIN when it genuinely clarifies a query, and rewrite it as a LEFT JOIN when it does not.

When to Use a LEFT JOIN

Choose a LEFT JOIN when the first table is the complete list and the second table is optional extra information. The test is simple: if a row from the first table must appear in the result even when nothing matches it, the join is a LEFT JOIN.

Common cases include the following. A customer list that must show every customer, including customers with no orders, so a marketing list does not silently lose people. A product catalog that must show every product, including products that have never sold, so a stock report does not hide slow lines. An employee directory that must show every employee, including employees with no assigned project. A page that must show every article, including articles with no comments yet. In each case, the left table defines the population, and the right table enriches it.

A LEFT JOIN is also the standard tool for the “find the ones with no match” pattern, which is covered in detail below. The join first preserves every left row, and a filter on the right key being NULL then isolates exactly the rows that found no partner. That two step pattern, preserve then filter, is more reliable than trying to express the same idea with a subquery, because the preserved rows are visible in the intermediate result and can be inspected before the filter is applied.

When to Use a RIGHT JOIN

Choose a RIGHT JOIN when the second table is the complete list and the first table is optional extra information. The test mirrors the LEFT JOIN test: if a row from the second table must appear even when nothing matches it, the join is a RIGHT JOIN.

Common cases include the following. An order list that must show every order, including orders with no matching customer, so revenue is never understated. An event log that must show every event, including events with no matching user record. A payment list that must show every payment, including payments whose invoice was deleted. A reconciliation report that must show every transaction on one side, including transactions the other side cannot explain. In each case, dropping the unmatched rows would hide exactly the rows that need attention.

In practice, most of these queries are written as LEFT JOINs with the tables swapped, so the preserved table comes first. Writing orders LEFT JOIN customers instead of customers RIGHT JOIN orders gives the same rows and reads more directly: start with all orders, then attach the customer where one exists. Keep RIGHT JOIN for the cases where swapping would make the query harder to read, for example when the query is built around a chain of joins that already establishes a reading order, or when you are editing an existing query and a RIGHT JOIN is the smallest change that fixes a missing row problem. The result is what matters, and the mirror rule guarantees that either spelling can produce it.

Finding Rows With No Match

The most valuable pattern in outer join work is the anti-join: find the rows on one side that have no partner on the other side. The pattern is a LEFT JOIN followed by a filter that keeps only the rows where the right key is NULL, or a RIGHT JOIN followed by a filter that keeps only the rows where the left key is NULL.

Customers with no orders:

SELECT c.name
FROM customers AS c
LEFT JOIN orders AS o
  ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

Result:

Orders with no matching customer:

SELECT o.order_id, o.total
FROM customers AS c
RIGHT JOIN orders AS o
  ON c.customer_id = o.customer_id
WHERE c.customer_id IS NULL;

Result:

Notice the second query carefully. The RIGHT JOIN preserves orders, so order 104 survives the join with NULL customer columns, and the WHERE filter then keeps only that row. If the tables were written the other way round, with orders on the left and a RIGHT JOIN preserving customers, order 104 would be dropped by the join before the filter ever ran, and the query would return no rows at all. That is not a hypothetical mistake. The first version of this query written for this guide made exactly that error, and it returned an empty result. The preserved side is decided by the join type and the table positions together, and both must point at the table you want to keep.

The same two questions can be answered with a LEFT JOIN by swapping the tables. The LEFT JOIN version of the orphan order check is orders LEFT JOIN customers with a filter on the customer key being NULL, and it returns the same single row, order 104 for 60.0. Use whichever spelling your team reads more easily, and verify the result by counting: the anti-join must return exactly the rows you expect to be unmatched, no more and no fewer.

The ON Versus WHERE Trap

The most common outer join mistake is placing a filter on the right table in the WHERE clause instead of the ON clause. The two placements look similar, but they produce different results, because a restriction in the ON clause is processed before the join, while a restriction in the WHERE clause is processed after the join. PostgreSQL documentation states that difference plainly, and adds that it does not matter with inner joins but matters a lot with outer joins.

Suppose the question is: list every customer, and show only their orders over 50. The filter belongs in the ON clause, because it restricts which orders are attached, not which customers are listed.

SELECT c.name, o.order_id, o.total
FROM customers AS c
LEFT JOIN orders AS o
  ON c.customer_id = o.customer_id AND o.total > 50
ORDER BY c.customer_id, o.order_id;

Result:

Four rows, one per customer. Priya Sharma appears with NULL order columns, because neither of her orders is over 50, so no order is attached. Marcus Lee appears with order 103, the only order over 50. Sofia Diaz and Tom Reed appear with NULL, as before.

Now move the same filter to the WHERE clause:

SELECT c.name, o.order_id, o.total
FROM customers AS c
LEFT JOIN orders AS o
  ON c.customer_id = o.customer_id
WHERE o.total > 50
ORDER BY c.customer_id, o.order_id;

Result:

One row. The WHERE clause runs after the join, and it removes every row whose total is NULL or 50 or less. That removes the NULL rows the LEFT JOIN just created, which silently converts the LEFT JOIN back into an inner join for practical purposes. The query still says LEFT JOIN, but it behaves like an inner join with a filter.

The rule to remember is short. Keep the relationship between the tables in the ON clause, and keep filters on the final result in the WHERE clause, but when a filter restricts the optional side of an outer join and the preserved rows must survive, the filter belongs in the ON clause. If you move a filter between the two clauses and the row count changes, the query has an outer join, and the change in count is the evidence that the placement matters.

GROUP BY and Aggregates With Outer Joins

Grouping happens after the join, so the join type decides which rows reach the grouping step. With an INNER JOIN, customers with no orders never reach GROUP BY at all, so they never appear with a count of zero. They simply do not appear. With a LEFT JOIN, they survive to the grouping step, and their order count is zero.

SELECT c.name, COUNT(o.order_id) AS order_count, SUM(o.total) AS total_spent
FROM customers AS c
LEFT JOIN orders AS o
  ON c.customer_id = o.customer_id
GROUP BY c.name
ORDER BY c.name;

Result:

All four customers appear. Sofia Diaz and Tom Reed have an order count of zero and a total of NULL, because SUM over no rows is NULL, not zero. If a report must show 0.00 instead of NULL, wrap the sum in COALESCE, as in COALESCE(SUM(o.total), 0). The companion guide to GROUP BY in SQL covers grouping in full, including the HAVING clause that filters groups after they are formed, which is also explained in the guide to HAVING in SQL.

Compare the INNER JOIN version of the same query:

SELECT c.name, COUNT(o.order_id) AS order_count, SUM(o.total) AS total_spent
FROM customers AS c
INNER JOIN orders AS o
  ON c.customer_id = o.customer_id
GROUP BY c.name
ORDER BY c.name;

Result:

Two rows. A reader who counts the rows in this result and describes it as a count of all customers is wrong. It is a count of customers who have at least one matching order. The same trap applies to averages: an average computed over an inner join excludes the unmatched population entirely, so it describes only the matched subset. When a report must describe the whole population, including the zeros, the join must be an outer join.

The SUM trap from the earlier section returns here in a second form. With a RIGHT JOIN, the total across all orders is 195.75, because the orphan order is included. With a LEFT JOIN, the total is 135.75, because the orphan order is excluded. With an INNER JOIN, the total is also 135.75, because the orphan order is excluded there too. Three queries, two different totals, and the difference is entirely the join type. Check the join before doubting the source data whenever a total looks wrong.

Chaining Outer Joins Across Three Tables

Real queries often need more than two tables, and outer joins chain the same way inner joins do, with each new join adding one table and one matching rule. The key difference is that NULL values propagate down the chain: once a row has no match at one link, the columns from every later table in the chain are NULL for that row as well.

This query lists every customer, their orders, and the products on each order:

SELECT c.name, o.order_id, oi.product_id, oi.quantity
FROM customers AS c
LEFT JOIN orders AS o
  ON c.customer_id = o.customer_id
LEFT JOIN order_items AS oi
  ON o.order_id = oi.order_id
ORDER BY c.customer_id, o.order_id, oi.product_id;

Result:

Six rows. Priya Sharma order 101 has two products, so it appears twice, once per product. Her order 102 has no order items in this dataset, so it appears once with NULL product columns. Sofia Diaz and Tom Reed have no orders, so they appear once each with NULL in every later column. The NULL spreads from left to right through the chain, which is the correct behavior: there is no product for an order that does not exist.

If the second join were an INNER JOIN instead of a LEFT JOIN, the chain would break at that link. Order 102 would disappear, because it has no order items, and Sofia Diaz and Tom Reed would disappear too, because their NULL order_id matches no order item. A chain of joins is only as preserving as its least preserving link. When a query mixes join types, read the chain link by link and ask, for each link, which rows survive it.

USING With LEFT and RIGHT JOIN

The ON clause is the most general way to state a join condition, and it is the right default. The USING clause is a shorthand for one specific situation: both tables use the same name for the joining column. Because customers and orders both have a column called customer_id, the LEFT JOIN can be written with USING.

SELECT name, order_id, total
FROM customers AS c
LEFT JOIN orders AS o
  USING (customer_id)
ORDER BY customer_id, order_id;

Result:

The rows are the same as the ON version of the LEFT JOIN. The difference is in the columns, not the rows. PostgreSQL documentation explains that JOIN USING suppresses the redundant column, so the shared column appears only once in the result, while JOIN ON produces all columns from the first table followed by all columns from the second table. That is why the query above can select name, order_id, total without qualifying customer_id, and why ORDER BY customer_id works without a table prefix.

For outer joins, USING has one extra behavior worth knowing. The single coalesced column takes the value from whichever side is not NULL, so an unmatched left row still shows its own key value in the shared column, and an unmatched right row still shows its key value. With ON, the two key columns stay separate, and the unmatched side shows NULL. Neither behavior is wrong. USING is shorter when the shared column name is deliberate and stable, and ON is clearer when the columns have different names or when you need to see both key columns in the result. The same USING shorthand works with RIGHT JOIN, and the same column suppression applies.

Which Systems Support RIGHT JOIN

Support for RIGHT JOIN is broad, but it is not universal, and the gaps matter when code moves between systems.

PostgreSQL documentation defines all four join types, with INNER as the default and OUTER as an optional word in every form. MySQL supports LEFT JOIN and RIGHT JOIN, and its documentation recommends LEFT JOIN instead of RIGHT JOIN to keep code portable. MySQL has no FULL OUTER JOIN in its join syntax, so a full outer join there is built by combining a LEFT JOIN and a RIGHT JOIN with UNION. SQLite added RIGHT JOIN and FULL OUTER JOIN in release 3.39.0, dated 2022-06-25, which its release history describes as long overdue support. Any SQLite older than 3.39.0 rejects RIGHT JOIN, so check the version before relying on it. The queries in this guide ran on SQLite 3.45.1.

The practical rule is to write the join your system supports and your team can read, and to keep RIGHT JOIN out of code that must run on the oldest system in your stack. Because every RIGHT JOIN can be rewritten as a LEFT JOIN, portability never forces you to lose a result. It only forces you to choose the spelling that runs everywhere you need it to run.

Common Mistakes

The mistakes below cause most of the wrong row counts and missing rows that outer joins produce. Each one is easy to make and easy to check once you know what to look for.

First, reading a LEFT JOIN as if it kept both sides. A LEFT JOIN drops unmatched right rows. In this guide, order 104 disappears from the LEFT JOIN result even though it is a real order with a real total. If every order must appear, the join is a RIGHT JOIN or a FULL OUTER JOIN, not a LEFT JOIN.

Second, filtering the preserved rows away in the WHERE clause. A condition on the right table in the WHERE clause removes the NULL rows a LEFT JOIN just created, which turns the outer join into an inner join in effect. Put optional side filters in the ON clause, as shown in the trap section above.

Third, writing the preserved table on the wrong side. A RIGHT JOIN preserves the second table, not the first. The orphan order query in this guide returned no rows at all in its first draft, because the tables were ordered so that the RIGHT JOIN preserved customers instead of orders. Check which table the join preserves before you check anything else.

Fourth, assuming a RIGHT JOIN is needed when a LEFT JOIN with swapped tables gives the same result. The two spellings return identical rows, and the LEFT JOIN version usually reads better. Reach for RIGHT JOIN when it clarifies the query, not by habit.

Fifth, counting an inner join result as if it described the whole population. An inner join drops unmatched rows from both sides, so a count or a sum over its result describes only the matched subset. When a report must include the zeros, use an outer join and check that the row count matches the population you intend to describe.

Sixth, treating NULL in an outer join result as missing source data. The NULL marks a row with no partner, not a value that was lost. A NULL total for a customer with no orders means there is no order, and converting it to zero without thinking changes the meaning of the number. Use COALESCE deliberately when a report needs zero instead of NULL, and say so in the query.

Seventh, joining on a key that is not stored consistently on both sides. A customer identifier stored as text on one side and as a number on the other, or a code with a trailing space or a different case, can fail to match even though a human reader sees the same value. The equality test compares stored values, not intended meanings. Store keys in one consistent type and format, or convert explicitly in the query with full awareness of what the conversion does. The guide to SQL Query With CASE shows how conditional expressions can clean up inconsistent values before they reach a join, and the guide to Count of Count in SQL covers nested aggregation for readers who need to count the results of a grouped query.

FAQ

What is the difference between LEFT JOIN and RIGHT JOIN in SQL?

A LEFT JOIN keeps every row from the first table named in the query and fills in NULL where the second table has no match. A RIGHT JOIN keeps every row from the second table named in the query and fills in NULL where the first table has no match. Matched pairs appear in both results. The difference is only which side survives when there is no match.

Is RIGHT JOIN the same as LEFT JOIN with the tables swapped?

Yes. Swapping the two tables and changing RIGHT JOIN to LEFT JOIN returns the identical set of rows, as the executed examples in this guide show. The two spellings are interchangeable, and most teams prefer the LEFT JOIN spelling because the preserved table comes first and the query reads more naturally.

When should I use LEFT JOIN instead of RIGHT JOIN?

Use a LEFT JOIN when the table you cannot lose rows from is the first one in the query, and use a RIGHT JOIN when it is the second one. In practice, most queries are written with the preserved table first and a LEFT JOIN, so RIGHT JOIN appears mainly when an existing query is being fixed or when a join chain already establishes a reading order. Either spelling gives the same rows.

Does a LEFT JOIN ever return fewer rows than the left table has?

No. A LEFT JOIN always returns at least one row per left table row, because every left row appears at least once, with NULL in the right columns when it has no match. If a LEFT JOIN returns fewer rows than the left table contains, the query is not the one you think it is, or a filter after the join removed rows. Count the left table first, then count the result, and compare the two numbers.

Why does my LEFT JOIN return fewer rows than I expected?

The most common reason is a filter in the WHERE clause on a right table column, which removes the NULL rows the join preserved and turns the outer join into an inner join in effect. Move the filter into the ON clause and check the count again. Other causes are a join condition that matches fewer rows than expected, a key stored in different types or formats on the two sides, and a later INNER JOIN in a chain that breaks the preservation.

What does NULL mean in a LEFT JOIN or RIGHT JOIN result?

NULL in an outer join result means that no matching row exists on the other side. It does not mean the source data is missing or unknown. A NULL order total for a customer with no orders means that customer has no order row at all. Treat that NULL as information about the match, not as a value to be averaged or summed without thought.

Can I use RIGHT JOIN in MySQL and SQLite?

Yes for both, with limits. MySQL supports RIGHT JOIN, and its documentation recommends LEFT JOIN instead for portability. SQLite supports RIGHT JOIN only in version 3.39.0 and later, released 2022-06-25, so older SQLite versions reject it. MySQL has no FULL OUTER JOIN, so a full outer join there is built by combining a LEFT JOIN and a RIGHT JOIN with UNION.

How do I find rows with no match using LEFT JOIN or RIGHT JOIN?

Use the anti-join pattern: join with an outer join, then filter on the key of the side that may be missing being NULL. Customers with no orders are found with a LEFT JOIN from customers to orders and a filter on the order key being NULL. Orders with no matching customer are found with a RIGHT JOIN that preserves orders and a filter on the customer key being NULL. Both patterns return exactly the unmatched rows.

Sources & references

• PostgreSQL Documentation 16, "7.2. Table Expressions", section 7.2.1.1 Joined Tables: definition of LEFT OUTER JOIN, RIGHT OUTER JOIN, and FULL OUTER JOIN, the rule that OUTER is optional, the rule that INNER is the default, the ON versus WHERE processing note for outer joins, and the USING column suppression rule. https://www.postgresql.org/docs/16/queries-table-expressions.html
• MySQL 8.0 Reference Manual, "15.2.13.2 JOIN Clause": joined table syntax with LEFT and RIGHT OUTER JOIN, the recommendation to use LEFT JOIN instead of RIGHT JOIN for portability, the LEFT JOIN anti-join example with a NULL filter, and the USING coalesced column rule. https://dev.mysql.com/doc/refman/8.0/en/join.html
• SQLite Release History, version 3.39.0 (2022-06-25): addition of support for RIGHT and FULL OUTER JOIN. https://sqlite.org/changes.html