Lesson

MySQL Joins Overview

Q: Does MySQL support FULL OUTER JOIN ?

No, MySQL lacks a FULL OUTER JOIN keyword; emulate it by combining a LEFT JOIN and a RIGHT JOIN with UNION .

Q: Does JOIN without a prefix mean INNER JOIN in MySQL?

Yes, writing JOIN alone is a shorthand synonym for INNER JOIN .

10 min reading

Free Course

MySQL Joins: Combining Related Data Across Tables

A MySQL JOIN combines columns from two or more tables using a shared key column such as customer_id. Because relational databases split data across normalized tables like demo_customers and demo_orders, joins let you fetch connected records in a single query.

Comparing MySQL Join Types

flowchart LR
    Left["Left Table (demo_customers)"] --- Inner["INNER JOIN: Only Matching Rows"]
    Left --- LeftJ["LEFT JOIN: All Left + Matching Right"]
    Right["Right Table (demo_orders)"] --- Inner
    Right --- RightJ["RIGHT JOIN: All Right + Matching Left"]

INNER JOIN: Returns only rows that have matching keys in both tables.
LEFT JOIN: Returns every row from the left table, filling unmatched right columns with NULL.
RIGHT JOIN: Returns every row from the right table, filling unmatched left columns with NULL.
CROSS JOIN: Pairs every row of the first table with every row of the second table.
Self Join: Joins a table to itself using two different table aliases (for employee-manager hierarchies).

Practical SQL Example

This query links demo_orders to demo_customers so each order displays the buyer's name and email:

SELECT
    o.id AS order_id,
    c.first_name,
    c.email,
    o.total_amount,
    o.status
FROM demo_orders AS o
INNER JOIN demo_customers AS c
    ON o.customer_id = c.id
ORDER BY o.id DESC;

order_id	first_name	email	total_amount	status
105	Jane	[email protected]	149.50	paid
104	John	[email protected]	54.50	pending

Common Mistakes and Best Practices

Index Every Foreign Key Column: Joining unindexed columns forces slow nested-loop scans across thousands of rows.
Qualify Ambiguous Column Names: When both tables have an id or created_at column, always prefix them with table aliases (o.id, c.id).
Avoid Comma Joins: Always write explicit INNER JOIN ... ON ... syntax instead of legacy FROM a, b WHERE a.id = b.a_id so join logic never accidentally turns into a cross join.