MySQL JOINs Explained: INNER, LEFT, RIGHT and CROSS with Simple Examples

A MySQL JOIN lets you combine rows from two or more tables in one query, based on a matching column — usually an ID that appears in both tables. Instead of running separate queries and joining the pieces together in your code, you ask MySQL for one tidy result: for example, every order together with the name of the student who placed it. There are four join types you need as a beginner: INNER JOIN keeps only matching rows, LEFT JOIN keeps every row from the left table, RIGHT JOIN keeps every row from the right table, and CROSS JOIN pairs every row with every other row. This guide explains all four with one tiny dataset, so you can see exactly what each query returns.

The Example Dataset: Students and Orders

Imagine a small training website: one table stores students, another stores their orders. Here is the students table:

And here is the orders table. Notice order 104: its student_id is 5, but there is no student 5. That deliberate mistake shows how each join behaves when a match is missing.

Key facts: Aarav has two orders, Meera has one, Tom and Sana have none, and one order belongs to a missing student. Every result below follows from those facts.

You could create these tables like this:

CREATE TABLE students (
  student_id INT PRIMARY KEY,
  name VARCHAR(50) NOT NULL
);

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  student_id INT,
  item VARCHAR(80) NOT NULL
);

The linking column is student_id. In a real database you would declare it as a foreign key, which would stop orphan rows like order 104 — but for learning, that orphan makes the join differences visible.

INNER JOIN: Only the Matches

An INNER JOIN returns a row only when the matching column has a value in both tables. It is the join you will use most.

SELECT students.name, orders.item
FROM students
INNER JOIN orders
  ON students.student_id = orders.student_id;

This returns exactly three rows: Aarav with PHP Book, Aarav with MySQL Book, and Meera with PHP Book. Tom and Sana disappear, because they have no orders, and order 104 disappears because its student does not exist. No match, no row. Writing just JOIN in MySQL means INNER JOIN.

Use INNER JOIN when you only care about complete, connected data: orders with their buyers, or posts with their authors.

LEFT JOIN: Keep Everything on the Left

A LEFT JOIN returns every row from the left table (the one written first, after FROM), plus matching rows from the right table. Where there is no match, the right-hand columns come back as NULL.

SELECT students.name, orders.item
FROM students
LEFT JOIN orders
  ON students.student_id = orders.student_id;

This returns five rows: the three matched rows, plus Tom with NULL as the item, and Sana with NULL. Order 104 still does not appear, because LEFT JOIN protects the left table only. Use it for questions like "show me all students, and their orders if they have any" — or to find customers who never bought anything, by checking where the item IS NULL.

RIGHT JOIN: Keep Everything on the Right

A RIGHT JOIN is the mirror image: it returns every row from the right table (the one after the JOIN keyword), plus matches from the left. Unmatched left-hand columns come back as NULL.

SELECT students.name, orders.item
FROM students
RIGHT JOIN orders
  ON students.student_id = orders.student_id;

This returns four rows: the three matched rows, plus NULL as the name with JavaScript Book — our orphan order 104, now visible. Tom and Sana disappear, because the left table is no longer protected. Many developers rewrite RIGHT JOINs as LEFT JOINs by swapping the table order, because it reads more naturally. Both are equally correct.

CROSS JOIN: Every Row Meets Every Row

A CROSS JOIN needs no matching column at all. It pairs every row of the first table with every row of the second table — the full Cartesian product.

SELECT students.name, orders.item
FROM students
CROSS JOIN orders;

With 4 students and 4 orders, this returns 16 rows: every student paired with every item. Most are nonsense — Tom never ordered the MySQL Book — so CROSS JOIN is rare in everyday work. It is useful for generating combinations, such as every shirt size with every colour. Be careful with big tables: 10,000 rows crossed with 10,000 rows makes 100 million rows.

The Four Joins Compared

To choose a join, ask: "Should a row with no match still appear?" If yes, note which table it lives in — that decides between LEFT and RIGHT. If you still need to set up your connection, our guide on how to connect PHP with a MySQL database walks through it step by step.

Using JOINs from PHP with PDO

In PHP you run the same SQL through PDO and loop over the results — the join itself does not change. The full syntax for every join form is in the MySQL JOIN documentation on dev.mysql.com, worth bookmarking for trickier forms such as self joins.

$sql = "SELECT students.name, orders.item
        FROM students
        LEFT JOIN orders
          ON students.student_id = orders.student_id
        ORDER BY students.student_id";

$stmt = $pdo->query($sql);

while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    echo $row['name'] . ' - ' . ($row['item'] ?? 'No orders yet') . "<br>";
}

The ?? 'No orders yet' part handles the NULLs a LEFT JOIN produces, so Tom and Sana print a friendly message. For prepared statements and error handling, the PDO section of the PHP manual on php.net is the official reference. And never glue user input straight into SQL: our article on how attackers hack websites using SQL injection shows why prepared statements are non-negotiable.

Common JOIN Mistakes Beginners Make

Forgetting the ON condition. A join without ON quietly becomes a cross join, and your page suddenly shows thousands of repeated rows. If a result looks far too big, check the ON clause first.

Expecting empty strings instead of NULL. Unmatched columns are NULL, not blank. Test with IS NULL in SQL, or the null coalescing operator in PHP.

Ambiguous column names. When both tables have a column called name, MySQL complains it is ambiguous. Prefix the table name, as in students.name, or use short aliases.

Duplicated rows that are actually correct. Aarav appears twice because he genuinely has two orders. A join returns one row per combination, not one row per student. For one row per student, look into GROUP BY once joins feel comfortable.

FAQ

What is the difference between INNER JOIN and LEFT JOIN?

INNER JOIN returns only rows that match in both tables. LEFT JOIN returns every row from the left table as well, filling the missing right-hand columns with NULL. In our example, INNER JOIN gives 3 rows and LEFT JOIN gives 5.

Does the order of tables matter in a join?

For INNER JOIN and CROSS JOIN, no — swapping the tables gives the same rows. For LEFT and RIGHT JOIN, yes: the protected side changes. A RIGHT JOIN with the tables swapped is the same query as the original LEFT JOIN.

Can I join more than two tables?

Yes. You simply add another JOIN clause for each extra table, each with its own ON condition. Real queries often join three or four tables, such as orders, students, courses and payments, in a single statement.

What does NULL mean in a join result?

NULL means "no value" — in an outer join it tells you there was no matching row in the other table. It is not zero and not an empty string, and you test for it with IS NULL rather than the equals sign.

Which join should I learn first?

Learn INNER JOIN first, because it is used most often, then LEFT JOIN, because "show everything, with matches if any" is the second most common question. RIGHT and CROSS joins are quick to pick up once those two feel natural.

Conclusion

JOINs stop being scary once you tie each one to a plain question. INNER asks for matches only, LEFT and RIGHT protect one side and pad the gaps with NULL, and CROSS pairs everything with everything. Practise on this tiny dataset until you can predict each result — then real project queries will feel like the same idea at a bigger size.