Understand MySQL Joins

What Are MySQL Joins?

In MySQL, joins are used to combine data from two or more tables based on a related column between them.

For example, imagine you have:

• A users table
• An orders table

You can use a JOIN to connect each user to their orders.

Example Tables

users

user_id	name
1	Ada
2	Ben
3	Chika

orders

order_id	user_id	item
101	1	Laptop
102	1	Keyboard
103	2	Mouse

Types of Joins in MySQL

There are four main types of joins you’ll use:

1. INNER JOIN

Returns only the rows that have matching values in both tables.

Query:

SELECT users.name, orders.item
FROM users
INNER JOIN orders
ON users.user_id = orders.user_id;

Result:

name	item
Ada	Laptop
Ada	Keyboard
Ben	Mouse

Explanation:

• Only users who have placed orders appear (Chika is excluded).

2. LEFT JOIN (or LEFT OUTER JOIN)

Returns all rows from the left table, and matching rows from the right table.
If there’s no match, the result is NULL for columns from the right table.

Query:

SELECT users.name, orders.item
FROM users
LEFT JOIN orders
ON users.user_id = orders.user_id;

Result:

name	item
Ada	Laptop
Ada	Keyboard
Ben	Mouse
Chika	NULL

Explanation:

• All users are shown.
• Chika appears even though she has no orders.

3. RIGHT JOIN (or RIGHT OUTER JOIN)

Returns all rows from the right table, and matching rows from the left table.
If no match exists, you’ll get NULL from the left table.

Query:

SELECT users.name, orders.item
FROM users
RIGHT JOIN orders
ON users.user_id = orders.user_id;

Result:

name	item
Ada	Laptop
Ada	Keyboard
Ben	Mouse

Explanation:

Returns all orders, even if the user table had missing data (though not in this example).

4. FULL JOIN (or FULL OUTER JOIN)

Returns all rows from both tables, with NULL where there’s no match.

Note: MySQL doesn’t support FULL OUTER JOIN directly,
but you can simulate it using a UNION of LEFT JOIN and RIGHT JOIN.

SELECT users.name, orders.item
FROM users
LEFT JOIN orders ON users.user_id = orders.user_id
UNION
SELECT users.name, orders.item
FROM users
RIGHT JOIN orders ON users.user_id = orders.user_id;

5. CROSS JOIN

Creates a Cartesian product — every row from the first table joins with every row from the second table.

Query:

SELECT users.name, orders.item
FROM users
CROSS JOIN orders;

Result:
If there are 3 users and 3 orders, you’ll get 9 rows (3 × 3).

Use case: Rare — typically used for generating combinations or test data.

Summary Table

Join Type	Returns	Example Use
INNER JOIN	Only matching rows	Users who made purchases
LEFT JOIN	All left table rows, plus matches	All users + their orders
RIGHT JOIN	All right table rows, plus matches	All orders + user details
FULL JOIN	All rows from both tables	Combine all users and all orders
CROSS JOIN	Every possible pair	Generate combinations

Best Practices

Always use aliases for cleaner queries:

SELECT u.name, o.item
FROM users u
LEFT JOIN orders o
ON u.user_id = o.user_id;

Use JOINs with indexes to improve performance.

Avoid unnecessary joins; each one adds computational cost.

Test with small datasets before scaling up.