How to Use Joins in MySQL: INNER, LEFT, RIGHT, and FULL Explained

When you work with relational databases like MySQL, you’ll often store data across multiple related tables. To pull that data together in a meaningful way, you need joins. Joins let you combine rows from two or more tables based on a related column, usually a primary and foreign key.

In this article, we’ll break down four common types of joins:

• INNER JOIN
• LEFT JOIN
• RIGHT JOIN
• FULL JOIN (using a workaround in MySQL)

I’ll also include examples with output and explain exactly what’s happening.

Let’s Start with Sample Tables

Here are two simple tables to work with:

Customers

Orders

These two tables are related by the customer_id in orders referencing the id in customers.

1. INNER JOIN – Only Matching Rows from Both Tables

Use INNER JOIN when you only want the records that have matches in both tables.

SELECT customers.name, orders.product
FROM customers
INNER JOIN orders
ON customers.id = orders.customer_id;

What this does:

• It looks for rows in customers and orders where customers.id = orders.customer_id.
• If a customer hasn’t placed an order, they’re excluded.

Output:

✅ Use case: When you're only interested in customers who have placed orders.

2. LEFT JOIN – All from the Left Table, Even Without Matches

Use LEFT JOIN to get all records from the left table (customers), and matching records from the right (orders). If there's no match, you'll get NULL.

SELECT customers.name, orders.product
FROM customers
LEFT JOIN orders
ON customers.id = orders.customer_id;

What this does:

• It returns all customers.
• If a customer has no matching order, it still shows the customer but with NULL in the product column.

Output:

✅ Use case: When you want a complete list of customers, even if some haven't ordered anything.

3. RIGHT JOIN – All from the Right Table, Even Without Matches

RIGHT JOIN is like LEFT JOIN, but flipped. It returns all rows from the right table (orders) and matching data from the left (customers).

SELECT customers.name, orders.product
FROM customers
RIGHT JOIN orders
ON customers.id = orders.customer_id;

What this does:

• It returns all orders.
• If an order doesn't have a matching customer (which shouldn’t happen in well-designed databases), it still shows up.

Output:

✅ Use case: Useful when you're focused on all orders, even if the customer info is missing or optional.

4. FULL JOIN – All from Both Tables, Matching When Possible

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

What this does:

• It returns all records from both tables.
• If a row from either side doesn’t match the other, it still shows up with NULL.

Output:

✅ Use case: When you want everything, all customers and all orders, whether or not there’s a match.

Quick Summary Table

Final Tips

• Always be clear on which table is “left” and which is “right”.
• Use aliases (like c and o) in larger queries to keep things clean.
• Practice by creating sample tables and testing joins yourself.