SQL joins in MySQL
MySQL joins are SQL statements that combine rows from 2 or more tables by matching values in related columns, and MySQL supports 4 core join types: INNER JOIN, LEFT JOIN, RIGHT JOIN, and SELF JOIN (a join of a table against itself).
Each join returns a different shape of result set, and knowing which shape you need is what makes a join a working tool rather than a syntax trick. The examples on this page all run against a 3 table sample database of 12 customers, 4 employees, and 8 orders, so you can copy every query and check the row counts yourself.
MySQL joins explained: the four types and what each returns
The four join types differ only in which rows they keep when a match fails, and a single 4 row matrix captures the whole difference. The table below marks which side of each join survives when a left row has no matching right row, or vice versa.
| Join type | Left rows with no match | Right rows with no match | Typical job |
|---|---|---|---|
| INNER JOIN | dropped | dropped | matching pairs only |
| LEFT JOIN | kept, right columns set to NULL | dropped | keep every left row |
| RIGHT JOIN | dropped | kept, left columns set to NULL | keep every right row |
| SELF JOIN | depends on the join direction chosen | depends on the join direction chosen | compare rows within one table |
The join condition comes after the ON keyword and names the columns that must match, such as orders.customer_id = customers.id. When you write these 4 statements against the same 2 tables, the row counts move from 8 matching pairs for the inner join to 12 left rows and 12 right rows for the outer joins, which is the fastest way to internalize the difference.
Working through the four join examples
Run each join example below in the order presented, because each one reuses the same customers, employees and orders tables and the later examples build on the pattern shown first. In MySQL in practice, the first 30 seconds of reading a result set tell you whether the join type matches the question you asked, so treat row count as your primary sanity check.
INNER JOIN: matching pairs only
SELECT c.name, o.order_id FROM customers c INNER JOIN orders o ON c.id = o.customer_id; returns the 8 orders that actually exist, one result row per order, with the customer name beside it. Customers 9, 10, 11 and 12 have no orders and therefore never appear, which is exactly the behavior an INNER JOIN is for.
LEFT JOIN: keep every left row
SELECT c.name, o.order_id FROM customers c LEFT JOIN orders o ON c.id = o.customer_id; returns all 12 customers. The 4 customers without orders still appear, but their order_id column holds NULL instead of a number. That NULL is the signature of the join: it means the left row survived and the right side simply had nothing to offer. A follow-up filter of WHERE o.order_id IS NULL turns the same query into a list of customers with zero orders, which is the classic "find what is missing" report.
RIGHT JOIN: keep every right row
SELECT c.name, o.order_id FROM customers c RIGHT JOIN orders o ON c.id = o.customer_id; is the mirror of the left join: all 8 orders appear, and a right join against a table with unmatched rows would show those rows with NULL on the left side. Because MySQL optimizes RIGHT JOIN into a LEFT JOIN by swapping the table order, most teams in a MySQL in practice codebase standardize on LEFT JOIN and simply flip which table sits on the left, which keeps the query style consistent.
SELF JOIN: a table against itself
SELECT e.name, m.name FROM employees e LEFT JOIN employees m ON e.manager_id = m.id; joins the employees table to itself, which is why the table appears twice, once aliased as e and once as m. The 4 employees resolve into 3 who have a manager and 1 who does not; that last row carries a NULL manager name. The aliases are not decoration: without two different names for the same table, the query cannot say which copy supplies the employee and which copy supplies the manager, and the 4 employee rows would collapse into an ambiguous 1 row mess.
How the join order and the ON clause shape the result
The ON clause decides which columns must match, and the join type decides what happens when they do not, so the 2 controls work independently. In this example schema, the chain runs customers to orders to line_items: orders.customer_id points back to customers.id, and line_items.order_id points back to orders.id. A 3 table query such as SELECT c.name, l.quantity FROM customers c JOIN orders o ON c.id = o.customer_id JOIN line_items l ON l.order_id = o.id walks that chain in 2 join steps and returns 14 line items spread across the 8 orders. The order of the joins matters for readability and for index use, and MySQL lets you read the plan it actually chose with EXPLAIN, which shows the join sequence and the index on each step in a single query.
Joining on more than one column
Multicolumn joins match on 2 or more columns at once, and the ON clause lists each pair with AND. A typical case is a table of monthly invoices where the same customer number reappears every month, so the true key is the customer number plus the month, and the join condition becomes ON i.customer_id = c.id AND i.month = c.billing_month. The discipline here is to match every column that participates in the key: skip one, and a single customer with 12 monthly rows multiplies against the wrong rows and the result silently inflates. The same rule scales to 3 columns and beyond, and it is the difference between a join that returns 14 rows and one that returns 48.
NULLs, duplicate rows, and the three failure modes to check first
Three failure modes account for most wrong join results, and each one has a 30 second test. The first is NULL in the join column: a comparison such as a.col = b.col is never true when either side is NULL, so the row vanishes from an INNER JOIN and appears as an empty right side in a LEFT JOIN, which you confirm with WHERE a.col IS NULL. The second is a one to many match: joining customers to orders multiplies every customer who has orders, so a customer with 5 orders shows up 5 times, and you collapse it with COUNT or GROUP BY if you expected one row per customer. The third is a missing index on the join column, which forces a full scan of the larger table and shows up in EXPLAIN as type ALL on a table of 100,000 rows; adding the index typically drops the scan to a few hundred row lookup. Check these 3 in order, and most "wrong" result sets fix themselves.
Where joins fit in a full MySQL workflow
Joins sit in the middle of a normal MySQL workflow, between modeling the tables and reading the data back, and the rest of the stack shows up in the same session. If you are new to the environment, a MySQL Workbench walkthrough covers the 5 steps from an empty instance to a connected schema: create the instance, set the root password, import the database, open the SQL editor, and run EXPLAIN against your first join. For teams choosing the engine itself, Choosing between MySQL and PostgreSQL comes down to replication model, type system, and tooling rather than join performance, because both engines execute the same 4 join types on the same SQL. The server underneath runs on standard Linux distros such as Ubuntu, Debian and Rocky Linux, which is why a join written in a MySQL session behaves identically from a laptop to a production box. Put the 4 join types, the ON clause discipline, and the 3 failure checks together and you cover the daily work of combining tables.