Joins are one of MySQL's useful functions. This guide provides a complete overview of different types of joins in MySQL including INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN, CROSS JOIN. For each type, we provide a practical example.
Understanding Joins in MySQL
A join in MySQL is used to combine rows from two or more tables based on a related column between them. The most common types of joins are:
- INNER JOIN
- LEFT JOIN (or LEFT OUTER JOIN)
- RIGHT JOIN (or RIGHT OUTER JOIN)
- FULL OUTER JOIN (not supported directly in MySQL)
- CROSS JOIN
1. INNER JOIN
An INNER JOIN selects records with matching values in both tables.
Example: Joining Two Tables
Imagine two tables: Users and Orders. To list all users who have placed an order:
SELECT Users.Name, Orders.OrderID
FROM Users
INNER JOIN Orders ON Users.UserID = Orders.UserID;This query combines rows from Users and Orders where the UserID matches in both tables.
2. LEFT JOIN (LEFT OUTER JOIN)
A LEFT JOIN returns all records from the left table, and the matched records from the right table. If there's no match, the result is NULL on the right side.
Example: Users and Their Orders
To list all users and their orders, including users who haven't placed any orders:
SELECT Users.Name, Orders.OrderID
FROM Users
LEFT JOIN Orders ON Users.UserID = Orders.UserID;Users without orders will show NULL for OrderID.
3. RIGHT JOIN (RIGHT OUTER JOIN)
A RIGHT JOIN returns all records from the right table, and the matched records from the left table. If there's no match, the result is NULL on the left side.
Example: Orders and Users
To list all orders and the users who placed them, including orders not yet linked to a user:
SELECT Users.Name, Orders.OrderID
FROM Users
RIGHT JOIN Orders ON Users.UserID = Orders.UserID;Orders without a user will show NULL for Name.
4. FULL OUTER JOIN
MySQL doesn't natively support FULL OUTER JOIN, but you can simulate it using a combination of LEFT JOIN and RIGHT JOIN.
Example: Simulating FULL OUTER JOIN
To list all users and all orders, including unmatched records:
SELECT Users.Name, Orders.OrderID
FROM Users
LEFT JOIN Orders ON Users.UserID = Orders.UserID
UNION
SELECT Users.Name, Orders.OrderID
FROM Users
RIGHT JOIN Orders ON Users.UserID = Orders.UserID;5. CROSS JOIN
A CROSS JOIN returns a Cartesian product of the two tables, meaning it combines each row of the first table with all rows in the second table.
Example: Combining Products and Stores
If you have Products and Stores tables and want to list all possible combinations:
SELECT Products.ProductID, Stores.StoreID
FROM Products
CROSS JOIN Stores;This query combines each product with each store.