MySQL Joins Cheatsheet

This cheatsheet covers the different types of joins in MySQL, their syntax, and examples.

Types of Joins
INNER JOIN
LEFT JOIN (LEFT OUTER JOIN)
RIGHT JOIN (RIGHT OUTER JOIN)
FULL JOIN (simulated in MySQL)
CROSS JOIN
SELF JOIN
Multiple Joins
Using WHERE with Joins

Types of Joins

MySQL supports the following types of joins:

INNER JOIN
LEFT JOIN (or LEFT OUTER JOIN)
RIGHT JOIN (or RIGHT OUTER JOIN)
FULL JOIN (simulated in MySQL)
CROSS JOIN
SELF JOIN

INNER JOIN

Returns only the rows where there is a match in both tables.

SELECT columns
FROM table1
INNER JOIN table2
ON table1.column = table2.column;

-- Example
SELECT orders.order_id, customers.customer_name
FROM orders
INNER JOIN customers
ON orders.customer_id = customers.customer_id;

LEFT JOIN (LEFT OUTER JOIN)

Returns all rows from the left table and the matched rows from the right table. If there's no match, NULL values are returned for columns from the right table.

SELECT columns
FROM table1
LEFT JOIN table2
ON table1.column = table2.column;

-- Example
SELECT customers.customer_name, orders.order_id
FROM customers
LEFT JOIN orders
ON customers.customer_id = orders.customer_id;

RIGHT JOIN (RIGHT OUTER JOIN)

Returns all rows from the right table and the matched rows from the left table. If there's no match, NULL values are returned for columns from the left table.

SELECT columns
FROM table1
RIGHT JOIN table2
ON table1.column = table2.column;

-- Example
SELECT orders.order_id, customers.customer_name
FROM orders
RIGHT JOIN customers
ON orders.customer_id = customers.customer_id;

FULL JOIN (Simulated in MySQL)

MySQL doesn't support FULL JOIN directly, but it can be simulated using LEFT JOIN and UNION.

SELECT columns
FROM table1
LEFT JOIN table2
ON table1.column = table2.column
UNION
SELECT columns
FROM table1
RIGHT JOIN table2
ON table1.column = table2.column
WHERE table1.column IS NULL;

-- Example
SELECT customers.customer_name, orders.order_id
FROM customers
LEFT JOIN orders
ON customers.customer_id = orders.customer_id
UNION
SELECT customers.customer_name, orders.order_id
FROM customers
RIGHT JOIN orders
ON customers.customer_id = orders.customer_id
WHERE customers.customer_id IS NULL;

CROSS JOIN

Returns the Cartesian product of both tables (every row from the first table combined with every row from the second table).

SELECT columns
FROM table1
CROSS JOIN table2;

-- Example
SELECT products.product_name, categories.category_name
FROM products
CROSS JOIN categories;

SELF JOIN

A join of a table with itself. Useful for comparing rows within the same table.

SELECT columns
FROM table1 AS t1
JOIN table1 AS t2
ON t1.column = t2.column;

-- Example (finding employees and their managers)
SELECT e1.name AS employee, e2.name AS manager
FROM employees e1
LEFT JOIN employees e2
ON e1.manager_id = e2.employee_id;

Multiple Joins

You can chain multiple joins in a single query.

SELECT columns
FROM table1
JOIN table2 ON table1.column = table2.column
JOIN table3 ON table2.column = table3.column;

-- Example
SELECT orders.order_id, customers.customer_name, products.product_name
FROM orders
JOIN customers ON orders.customer_id = customers.customer_id
JOIN order_items ON orders.order_id = order_items.order_id
JOIN products ON order_items.product_id = products.product_id;

Using WHERE with Joins

You can add WHERE clauses after your joins to further filter the results.

SELECT columns
FROM table1
JOIN table2 ON table1.column = table2.column
WHERE condition;

-- Example
SELECT orders.order_id, customers.customer_name
FROM orders
JOIN customers ON orders.customer_id = customers.customer_id
WHERE orders.order_date > '2023-01-01';

📚Cheatsheets