SQL INNER JOIN vs LEFT JOIN vs RIGHT JOIN vs FULL OUTER JOIN: Differences With Examples (Tested in MySQL 8.4 and SQLite)

INNER JOIN returns only the rows that match in both tables. LEFT JOIN returns every row from the left table, with NULL where the right table has no match. RIGHT JOIN is the mirror image: every row from the right table. FULL OUTER JOIN returns every row from both tables, matched where possible. On my four customers and five orders, those joins returned 4, 5, 5 and 6 rows. MySQL doesn't support FULL OUTER JOIN at all, so there you combine a LEFT JOIN and a RIGHT JOIN with UNION ALL.

Joins are where a lot of SQL bugs hide. A report silently drops customers who never ordered, a WHERE clause quietly turns a LEFT JOIN back into an INNER JOIN, or MySQL happily runs a "FULL JOIN" that isn't one. I'll show each of those with real output.

Every query below ran against the same two tables on MySQL 8.4.8 and SQLite 3.46.1, and the result tables are pasted straight from the mysql and sqlite3 command-line clients.

In this guide:

• INNER vs LEFT vs RIGHT vs FULL OUTER JOIN at a glance
• The two tables used in every example
• INNER JOIN: only rows that match
• LEFT JOIN: keep every row from the first table
• RIGHT JOIN: keep every row from the second table
• FULL OUTER JOIN: keep everything from both sides
• How to do a FULL OUTER JOIN in MySQL
• The MySQL "FULL JOIN" trap
• Find rows with no match (the LEFT JOIN ... IS NULL pattern)
• The WHERE clause trap that turns LEFT JOIN into INNER JOIN
• COUNT(*) vs COUNT(column) after a LEFT JOIN
• Why joins can multiply rows (and what CROSS JOIN does)
• Which join should you use?
• Frequently asked questions

INNER vs LEFT vs RIGHT vs FULL OUTER JOIN at a glance

LEFT JOIN and LEFT OUTER JOIN are the same thing; the word OUTER is optional. The same goes for RIGHT and FULL. And a plain JOIN means INNER JOIN. RIGHT and FULL OUTER JOIN arrived in SQLite with version 3.39.0, as the SQLite 3.39.0 release notes put it: "Add (long overdue) support for RIGHT and FULL OUTER JOIN."

The two tables used in every example

Here's the schema and data. It runs unchanged on both MySQL and SQLite:

CREATE TABLE customers (
  id   INT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  city VARCHAR(50)
);
CREATE TABLE orders (
  id          INT PRIMARY KEY,
  customer_id INT,
  total       DECIMAL(8,2),
  status      VARCHAR(20)
);
INSERT INTO customers VALUES
  (1, 'Ayesha', 'London'),
  (2, 'Ben',    'Leeds'),
  (3, 'Chloe',  'London'),
  (4, 'Dev',    'Bristol');
INSERT INTO orders VALUES
  (101, 1,    25.00, 'paid'),
  (102, 1,    40.00, 'refunded'),
  (103, 2,    15.50, 'paid'),
  (104, 3,    60.00, 'paid'),
  (105, NULL, 12.00, 'paid');

The data is built to show every edge case:

• Ayesha has two orders, so she can appear twice in a join.
• Ben and Chloe have one order each.
• Dev has no orders at all.
• Order 105 has no customer (customer_id is NULL), like a guest checkout.

A NULL never equals anything in SQL, not even another NULL, so order 105 can never match a customer through o.customer_id = c.id.

INNER JOIN: only rows that match

An INNER JOIN pairs each customer with each of their orders and throws away anything without a partner on the other side:

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

MySQL 8.4.8:

+--------+----------+-------+
| name   | order_id | total |
+--------+----------+-------+
| Ayesha |      101 | 25.00 |
| Ayesha |      102 | 40.00 |
| Ben    |      103 | 15.50 |
| Chloe  |      104 | 60.00 |
+--------+----------+-------+

Dev is gone because he has no orders, and order 105 is gone because it has no customer. Ayesha appears twice because she matches two orders. That's normal: a join returns one row per matching pair, not one row per customer.

Use INNER JOIN when a row is only meaningful with its partner, like "orders with the customer's name". The MySQL 8.4 JOIN reference notes that in MySQL, JOIN, CROSS JOIN and INNER JOIN are syntactic equivalents, though standard SQL treats them differently.

LEFT JOIN: keep every row from the first table

Swap INNER for LEFT and every customer stays, with or without orders:

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

+--------+----------+-------+
| name   | order_id | total |
+--------+----------+-------+
| Ayesha |      101 | 25.00 |
| Ayesha |      102 | 40.00 |
| Ben    |      103 | 15.50 |
| Chloe  |      104 | 60.00 |
| Dev    |     NULL |  NULL |
+--------+----------+-------+

Dev is back, with NULL in every column that comes from orders. Here's the same query in SQLite 3.46.1 using .mode table:

+--------+----------+-------+
|  name  | order_id | total |
+--------+----------+-------+
| Ayesha | 101      | 25    |
| Ayesha | 102      | 40    |
| Ben    | 103      | 15.5  |
| Chloe  | 104      | 60    |
| Dev    |          |       |
+--------+----------+-------+

The rows are identical. Two display differences are worth knowing: the SQLite shell shows NULL as an empty cell by default (run .nullvalue NULL to change that), and SQLite has no true DECIMAL type, so 25.00 prints as 25.

LEFT JOIN is the join you'll write most after INNER JOIN. Any time the question is "all X, plus their Y if they have any", it's a LEFT JOIN.

RIGHT JOIN: keep every row from the second table

RIGHT JOIN flips the rule. Now every order is kept, even the guest order with no customer:

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

+--------+----------+-------+
| name   | order_id | total |
+--------+----------+-------+
| Ayesha |      101 | 25.00 |
| Ayesha |      102 | 40.00 |
| Ben    |      103 | 15.50 |
| Chloe  |      104 | 60.00 |
| NULL   |      105 | 12.00 |
+--------+----------+-------+

Order 105 appears with a NULL name, and Dev has disappeared, because nothing on the right side points at him.

Any RIGHT JOIN can be rewritten as a LEFT JOIN by swapping the table order: FROM orders o LEFT JOIN customers c ON ... returns the same rows. The MySQL manual even recommends using LEFT JOIN instead of RIGHT JOIN to keep code portable. Most teams follow that, because queries are easier to read when the "keep everything" table always comes first.

FULL OUTER JOIN: keep everything from both sides

A FULL OUTER JOIN is a LEFT JOIN and a RIGHT JOIN combined. SQLite 3.46.1 runs it directly:

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

+--------+----------+-------+
|  name  | order_id | total |
+--------+----------+-------+
| Dev    |          |       |
| Ayesha | 101      | 25    |
| Ayesha | 102      | 40    |
| Ben    | 103      | 15.5  |
| Chloe  | 104      | 60    |
|        | 105      | 12    |
+--------+----------+-------+

Six rows: the four matches, Dev without an order, and order 105 without a customer. Dev sorts first because NULL values come first in an ascending ORDER BY in both SQLite and MySQL.

Now the same query in MySQL 8.4.8:

ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FULL OUTER JOIN orders o ON o.customer_id = c.id
ORDER BY o.id, c.id' at line 3

MySQL has no FULL OUTER JOIN. PostgreSQL does, and its table expressions documentation covers all the outer join types, but I only tested MySQL and SQLite here.

How to do a FULL OUTER JOIN in MySQL

The standard workaround: take the LEFT JOIN, then add only the right-side rows that had no match. The WHERE c.id IS NULL on the second half is what stops the matched rows appearing twice:

SELECT c.name, o.id AS order_id, o.total
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
UNION ALL
SELECT c.name, o.id, o.total
FROM customers c
RIGHT JOIN orders o ON o.customer_id = c.id
WHERE c.id IS NULL
ORDER BY order_id, name;

MySQL 8.4.8:

+--------+----------+-------+
| name   | order_id | total |
+--------+----------+-------+
| Dev    |     NULL |  NULL |
| Ayesha |      101 | 25.00 |
| Ayesha |      102 | 40.00 |
| Ben    |      103 | 15.50 |
| Chloe  |      104 | 60.00 |
| NULL   |      105 | 12.00 |
+--------+----------+-------+

The same six rows SQLite gave with a real FULL OUTER JOIN. I ran this exact query in SQLite too and got the same six rows, so it's a portable pattern.

You'll often see this written with plain UNION and no WHERE. That also removes the duplicated matches, but it removes any identical rows, including real duplicates in your data, and it makes the database do extra work comparing every row to find duplicates. UNION ALL with WHERE c.id IS NULL keeps every genuine row and skips that deduplication step.

The MySQL "FULL JOIN" trap

This one surprised me. Write FULL JOIN (without OUTER) in MySQL and leave out the table alias, and you don't get a syntax error. You get a different, confusing error:

SELECT customers.name, orders.id AS order_id
FROM customers
FULL JOIN orders ON orders.customer_id = customers.id
ORDER BY order_id;

ERROR 1054 (42S22) at line 1: Unknown column 'customers.name' in 'field list'

MySQL read FULL as an alias for customers, so customers.name no longer exists. Use the alias it accidentally created, and the query runs, but as a plain inner join:

SELECT FULL.name, orders.id AS order_id
FROM customers
FULL JOIN orders ON orders.customer_id = FULL.id
ORDER BY order_id;

+--------+----------+
| name   | order_id |
+--------+----------+
| Ayesha |      101 |
| Ayesha |      102 |
| Ben    |      103 |
| Chloe  |      104 |
+--------+----------+

Four rows, no Dev, no order 105. If you ever port a query from PostgreSQL or SQLite and MySQL suddenly complains about an unknown column, check for FULL JOIN. SQLite, for the record, ran the first version as a true full join and rejected the second with no such column: FULL.name.

Find rows with no match (the LEFT JOIN ... IS NULL pattern)

One of the most useful things a LEFT JOIN does is find what's missing. Join, then keep only the rows where the right side came back empty:

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

+------+
| name |
+------+
| Dev  |
+------+

That's "customers who never ordered", and both databases returned the same single row. Check a column that can't be NULL in a real match, like the primary key o.id. The same pattern finds orphaned records before you add a foreign key, and pairs well with cleaning up data as in my guide to deleting duplicate rows in MySQL.

The WHERE clause trap that turns LEFT JOIN into INNER JOIN

Say you want every customer and their paid orders. This looks right, but it isn't:

SELECT c.name, o.id AS order_id, o.status
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'paid'
ORDER BY c.id;

+--------+----------+--------+
| name   | order_id | status |
+--------+----------+--------+
| Ayesha |      101 | paid   |
| Ben    |      103 | paid   |
| Chloe  |      104 | paid   |
+--------+----------+--------+

Dev vanished. The LEFT JOIN did keep him, with o.status as NULL, but then WHERE o.status = 'paid' ran on the result, and NULL = 'paid' isn't true. So the WHERE filtered out exactly the rows the LEFT JOIN was there to keep.

Fix: move conditions on the right-hand table into the ON clause:

SELECT c.name, o.id AS order_id, o.status
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'paid'
ORDER BY c.id;

+--------+----------+--------+
| name   | order_id | status |
+--------+----------+--------+
| Ayesha |      101 | paid   |
| Ben    |      103 | paid   |
| Chloe  |      104 | paid   |
| Dev    |     NULL | NULL   |
+--------+----------+--------+

Rule of thumb: conditions on the table you're keeping go in WHERE; conditions on the optional table go in ON. If you build these queries from PHP, pass the 'paid' value as a bound parameter, as shown in my PDO prepared statements guide.

COUNT(*) vs COUNT(column) after a LEFT JOIN

Counting orders per customer is a classic LEFT JOIN job, and a classic mistake:

SELECT c.name, COUNT(*) AS count_star, COUNT(o.id) AS count_orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY c.id;

+--------+------------+--------------+
| name   | count_star | count_orders |
+--------+------------+--------------+
| Ayesha |          2 |            2 |
| Ben    |          1 |            1 |
| Chloe  |          1 |            1 |
| Dev    |          1 |            0 |
+--------+------------+--------------+

COUNT(*) counts rows, and Dev still has one row (the one full of NULLs), so it says 1. COUNT(o.id) skips NULL values and correctly says 0. Always count a column from the optional table. If you'd rather list the order IDs in one cell, GROUP_CONCAT with ORDER BY works on the same join.

Why joins can multiply rows (and what CROSS JOIN does)

Every join returns one row per matching pair. Ayesha has two orders, so she shows up twice, and if you joined a third table where she also had two rows, you'd get four Ayesha rows. That's why a SUM() over a join with two "many" tables can come out too high: rows are repeated before the sum runs. Aggregate each child table first in a subquery, then join the totals.

A CROSS JOIN takes that to the extreme. It has no ON condition and pairs every row with every row:

SELECT COUNT(*) AS cross_rows FROM customers CROSS JOIN orders;

+------------+
| cross_rows |
+------------+
|         20 |
+------------+

4 customers × 5 orders = 20. Forgetting the ON clause gives you this by accident: SELECT COUNT(*) FROM customers JOIN orders; with no ON also returned 20 in both MySQL 8.4.8 and SQLite 3.46.1, with no error or warning. That's how a query on two 10,000-row tables suddenly tries to return 100 million rows. Microsoft's guide to how joins are processed explains how engines execute these, if you want to go deeper.

Which join should you use?

• You need only rows that exist on both sides (orders with a known customer): INNER JOIN.
• You need every row from one table, plus extra details where they exist (all customers and any orders): LEFT JOIN, with the "keep everything" table first.
• You're tempted to write RIGHT JOIN: swap the table order and use LEFT JOIN instead. Same result, easier to read.
• You need to compare two lists and see what's missing on either side (reconciling two exports): FULL OUTER JOIN, or LEFT JOIN + UNION ALL + RIGHT JOIN ... WHERE left.id IS NULL in MySQL.
• You need every combination (sizes × colours): CROSS JOIN.

Need quick helpers while working on a database-backed site, like a slug generator or UUID generator? They're on the free tools page.

Frequently asked questions

What is the difference between INNER JOIN and LEFT JOIN?

INNER JOIN returns only rows that have a match in both tables. LEFT JOIN returns every row from the left table and fills the right table's columns with NULL when there's no match. In my test, INNER JOIN returned 4 rows and LEFT JOIN returned 5, because the customer with no orders was kept.

Is LEFT JOIN the same as LEFT OUTER JOIN?

Yes. OUTER is optional, so LEFT JOIN and LEFT OUTER JOIN are identical. The same applies to RIGHT JOIN and RIGHT OUTER JOIN, and to FULL JOIN and FULL OUTER JOIN in databases that support it.

Does MySQL support FULL OUTER JOIN?

No. MySQL 8.4.8 returned ERROR 1064 for FULL OUTER JOIN. Emulate it with a LEFT JOIN, then UNION ALL, then a RIGHT JOIN filtered with WHERE left_table.id IS NULL.

Does SQLite support RIGHT JOIN and FULL OUTER JOIN?

Yes, since SQLite 3.39.0. Older versions only supported INNER, LEFT and CROSS joins. Run SELECT sqlite_version(); to check which version you have.

Is RIGHT JOIN ever necessary?

Not really. Any RIGHT JOIN can be written as a LEFT JOIN by swapping the order of the tables, and the MySQL manual recommends LEFT JOIN for portability.

Why did my LEFT JOIN return fewer rows than expected?

Usually a WHERE condition on the right-hand table. Unmatched rows have NULL there, and the WHERE filters them out, turning the query into an INNER JOIN. Move that condition into the ON clause.

Is a plain JOIN an INNER JOIN?

Yes. JOIN on its own means INNER JOIN in standard SQL, MySQL and SQLite. In MySQL, JOIN, INNER JOIN and CROSS JOIN are syntactic equivalents.

Which join is fastest?

It depends on indexes, data size and the query plan, not the join keyword. Index the columns in the ON clause (such as orders.customer_id) and check the plan with EXPLAIN. INNER JOIN can return fewer rows, which helps, but choose the join for correctness first.

Last checked: 1 November 2026. Figures, settings and guidance can change, so confirm anything important with the sources linked above.