How to Use JOINs in MySQL

How do I use JOINs in MySQL to combine tables?

JOINs combine rows from two or more tables based on related columns so you can query relational data in a single result set.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

What does a JOIN do in MySQL?

JOIN returns a result set that merges rows from two or more tables whenever the join condition evaluates to TRUE. This lets you pull customer, order, and product details in one query instead of separate look-ups.

When should I use INNER JOIN?

Use INNER JOIN to show only rows with matches in both tables.For example, list customers who have placed at least one order by joining Customers with Orders on customer_id.

How do I write a LEFT JOIN?

LEFT JOIN keeps every row from the left table and adds matching rows—or NULLs—from the right table.It is perfect for finding customers who have never ordered.

Example LEFT JOIN

SELECT c.id, c.name, o.id AS order_id FROM Customers AS c LEFT JOIN Orders AS o ON o.customer_id = c.id;

Can I JOIN more than two tables?

Yes. Chain multiple JOINs.MySQL processes them left to right, so verify each ON clause uses columns from the two tables being joined at that step.

Three-table JOIN example

SELECT c.name, o.id AS order_id, p.name AS product, oi.quantity, p.price, (oi.quantity*p.price) AS line_total FROM Customers c JOIN Orders o ON o.customer_id = c.id JOIN OrderItems oi ON oi.order_id = o.id JOIN Products p ON p.id = oi.product_id;

Best practices for JOIN performance

Index foreign keys (customer_id, order_id, product_id) to speed lookups.Always qualify columns (table.column) to avoid ambiguity. Return only needed fields and filter early with WHERE.

Common mistakes to avoid

Do not forget the ON clause; that creates a Cartesian product. Also, avoid placing conditions on the right-table columns in the WHERE clause after a LEFT JOIN—move them into the ON clause to preserve unmatched rows.

Why How to Use JOINs in MySQL is important

How to Use JOINs in MySQL Example Usage


-- Show each order line with customer and product names
aSELECT c.name             AS customer,
       o.id               AS order_id,
       p.name             AS product,
       oi.quantity,
       (oi.quantity * p.price) AS line_total
FROM Customers   c
JOIN Orders      o  ON o.customer_id = c.id
JOIN OrderItems  oi ON oi.order_id   = o.id
JOIN Products    p  ON p.id          = oi.product_id
ORDER BY o.order_date DESC;

How to Use JOINs in MySQL Syntax


SELECT select_list
FROM table1 [AS] alias1
    {INNER | LEFT | RIGHT | CROSS} JOIN table2 [AS] alias2
    ON join_condition
    [JOIN table3 ON ...]
    [WHERE filter]
    [ORDER BY columns];

-- Ecommerce INNER JOIN
SELECT c.id, c.name, o.id AS order_id, o.total_amount
FROM Customers AS c
INNER JOIN Orders AS o ON o.customer_id = c.id;

Common Mistakes

Forgetting the ON clause causes a Cartesian product that multiplies rows and slows queries. Always specify the join condition after each JOIN keyword.
Filtering right-table columns in the WHERE clause after a LEFT JOIN converts it to an INNER JOIN. Move those filters into the ON clause to keep unmatched rows.