Skip to content
Tutorials Center
My learning

Topics

Lesson 17 of 40 · Joins and Subqueries

Joins in MariaDB – INNER, LEFT, RIGHT, CROSS and Self Joins

Combine tables in MariaDB with INNER, LEFT, RIGHT, CROSS and self joins, ON versus USING, joins over several tables, anti-joins, a FULL OUTER JOIN emulation and multi-table UPDATE and DELETE.

A relational database spreads facts over several tables so nothing is stored twice: customers in one table, orders in another, products in a third. A join puts them back together at query time by matching a key in one table with a key in another. Joins are the heart of SQL, and understanding which rows survive each kind of join prevents most reporting errors.

All the examples run on the sample shop database, where orders.customer_id points to customers.id.

flowchart LR
  C["customers"] -->|"id = customer_id"| O["orders"]
  O -->|"id = order_id"| I["order_items"]
  P["products"] -->|"id = product_id"| I
  CA["categories"] -->|"id = category_id"| P

The shop schema: each arrow is a join condition.

INNER JOIN

An inner join returns only rows with a match on both sides. Rows without a partner disappear.

SQL
SELECT c.first_name, o.id, o.status
FROM customers AS c
INNER JOIN orders AS o ON o.customer_id = c.id;

INNER is optional, so plain JOIN means the same thing. Use short aliases (c, o) and qualify every column that exists in more than one table.

SQL INNER JOIN · database: shop

Press Run (or Ctrl + Enter) to see the result here.

LEFT JOIN

A left join keeps every row of the left table. Where the right table has no match, its columns are NULL. It answers "show me all customers, and their orders if any".

SQL LEFT JOIN keeps customers without orders · database: shop

Press Run (or Ctrl + Enter) to see the result here.

Notice COUNT(o.id) rather than COUNT(*): counting o.id ignores the NULL that a missing order produces, so a customer with no orders gets 0, not 1.

The anti-join: rows with no match

Add WHERE right.key IS NULL to a left join to find rows that have no partner:

SQL Customers who never ordered · database: shop

Press Run (or Ctrl + Enter) to see the result here.

A classic trap: WHERE versus ON

Conditions on the right table belong in ON, not WHERE, or you accidentally turn the left join back into an inner join:

SQL Filter in ON keeps the left rows · database: shop

Press Run (or Ctrl + Enter) to see the result here.

Move o.status = 'delivered' into WHERE and every customer without a delivered order vanishes from the result.

RIGHT JOIN

RIGHT JOIN is a left join with the tables swapped. It keeps every row of the right table. Most people rewrite it as a LEFT JOIN to read the query in one direction, but MariaDB fully supports it:

SQL
SELECT c.first_name, o.id
FROM orders AS o
RIGHT JOIN customers AS c ON c.id = o.customer_id;     -- same as customers LEFT JOIN orders

FULL OUTER JOIN (emulation)

MariaDB has no FULL OUTER JOIN. Build it from a left join and an anti-joined right join:

SQL
SELECT c.id, o.id
FROM customers c LEFT JOIN orders o ON o.customer_id = c.id
UNION ALL
SELECT c.id, o.id
FROM customers c RIGHT JOIN orders o ON o.customer_id = c.id
WHERE c.id IS NULL;

The WHERE c.id IS NULL leg picks only the orders without a customer, so rows matched in both directions are not repeated. Using UNION instead of UNION ALL also works, but costs a duplicate check.

Joining several tables

Add one join per relationship. Read the query as a chain: start with the table you want one row per, and join outward.

SQL Four tables in a chain · database: shop

Press Run (or Ctrl + Enter) to see the result here.

A join on a "one to many" relationship repeats the parent row once per child. That is correct for listing, but dangerous for totals: summing o.shipping after joining the order items counts the shipping once per item. Aggregate in a subquery first, or sum only values from the many side.

USING, NATURAL JOIN and non-equi joins

When both tables name the join column identically, USING (col) is a shorthand and returns the column once:

SQL
SELECT * FROM orders JOIN order_items USING (id);   -- only if the column names truly match

NATURAL JOIN joins on all same-named columns, which silently changes meaning when someone adds a column. Avoid it. Joins do not have to use equality; ranges work too:

SQL A non-equi join: price bands · database: shop

Press Run (or Ctrl + Enter) to see the result here.

CROSS JOIN

A cross join pairs every row of one table with every row of another (the Cartesian product). It is rarely an accident you want, and often a deliberate way to build combinations:

SQL CROSS JOIN: all size and colour combinations

Press Run (or Ctrl + Enter) to see the result here.

A forgotten ON clause in MariaDB behaves as a cross join too (an INNER JOIN without ON is allowed), turning 10,000 x 10,000 rows into 100 million. If a query suddenly explodes in size, look for a missing join condition.

Self joins

Join a table to itself to follow relationships inside it, such as employees and their managers. You must alias both copies:

SQL Employees and their managers · database: company

Press Run (or Ctrl + Enter) to see the result here.

Joins in UPDATE and DELETE

MariaDB can join inside data-changing statements, a handy extension:

SQL
UPDATE products AS p
JOIN categories AS c ON c.id = p.category_id
SET p.price = ROUND(p.price * 0.9, 2)
WHERE c.name = 'Outdoor';

DELETE o FROM orders AS o
LEFT JOIN customers AS c ON c.id = o.customer_id
WHERE c.id IS NULL;                -- remove orphan orders

The portable forms use a subquery (WHERE category_id IN (SELECT ...)), which also works in SQLite.

Which join?

You want Use
Only matching rows INNER JOIN
All rows from A, matches from B if any LEFT JOIN
Rows in A with no match in B LEFT JOIN ... WHERE B.key IS NULL
Every combination CROSS JOIN
Relationships within one table self join
All rows from both sides LEFT + RIGHT with UNION

Performance rule of thumb: join on indexed columns (primary and foreign keys), select only the columns you need, and check the plan with EXPLAIN (a later chapter).

Check your understanding

Quick check · 5 questions

of 5 answered You scored / 5 · perfect!

  1. 1. Which join returns rows from the left table even when there is no matching row on the right?

    A left join keeps all left rows and fills the right side's columns with NULL when nothing matches.
  2. 2. How do you find customers that have no orders?

    Rows without a partner get NULLs on the right, and the WHERE clause keeps exactly those rows.
  3. 3. Why can a WHERE condition on the right table break a LEFT JOIN?

    Comparisons with NULL are not true, so unmatched rows are filtered out. Put the condition in ON instead.
  4. 4. How do you emulate FULL OUTER JOIN in MariaDB?

    Combining the two directions, with the second restricted to unmatched rows, gives every row from both tables.
  5. 5. What does a CROSS JOIN of a 5-row and a 4-row table return?

    Every row is paired with every row: 5 x 4 = 20.

Summary

  • INNER JOIN keeps matches only; LEFT/RIGHT JOIN keep every row of one side and use NULL for missing partners.
  • Anti-join: left join plus WHERE right.key IS NULL. Filters on the right table go in ON.
  • No FULL OUTER JOIN: combine a left and a right join with UNION ALL.
  • CROSS JOIN builds all combinations; self joins need aliases; MariaDB allows joins inside UPDATE and DELETE.

Next: Subqueries