# 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

| Join | Rows it keeps | Unmatched side shows | Rows in my test | MySQL 8.4 | SQLite |
| --- | --- | --- | --- | --- | --- |
| `INNER JOIN` | Only rows that match in both tables | Not returned | 4 | Yes | Yes |
| `LEFT JOIN` | All left rows + matching right rows | `NULL` in right columns | 5 | Yes | Yes |
| `RIGHT JOIN` | All right rows + matching left rows | `NULL` in left columns | 5 | Yes | Yes (3.39.0+) |
| `FULL OUTER JOIN` | All rows from both tables | `NULL` on whichever side is missing | 6 | No (use `UNION ALL`) | Yes (3.39.0+) |
| `CROSS JOIN` | Every left row paired with every right row | No matching at all | 20 | Yes | Yes |

`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](https://www.sqlite.org/releaselog/3_39_0.html) 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](https://dev.mysql.com/doc/refman/8.4/en/join.html) 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](https://www.postgresql.org/docs/current/queries-table-expressions.html) 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](https://mfaysal.com/blog/mysql-delete-duplicate-rows).

## 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](https://mfaysal.com/blog/php-pdo-prepared-statements).

## 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 `NULL`s), 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](https://mfaysal.com/blog/mysql-group-concat-order-by-separator) 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](https://learn.microsoft.com/en-us/sql/relational-databases/performance/joins) 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](https://mfaysal.com/free-tools/).

## 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.*

---

*Originally published on [mfaysal.com](https://mfaysal.com/blog/sql-inner-join-vs-left-join-vs-right-join-vs-full-outer-join).*

I'm learning cybersecurity on TryHackMe — follow my progress at [tryhackme.com/p/mfaysal.dev](https://tryhackme.com/p/mfaysal.dev).