Why relational queries use joins
Relational data is usually split across tables so each fact has a clear place and constraints can protect consistency. A customer row stores customer attributes, while an order row refers to its customer through a key. A join combines related rows at query time so a report can show both order and customer information. Joins are not simply a way to connect names: the join condition defines which rows correspond, and an incorrect condition can produce missing records or a large Cartesian product.
Use explicit JOIN ... ON syntax. It separates relationship logic from filters and makes the query easier to review. Qualify columns with table aliases when names repeat. Before joining, understand the cardinality: one customer can have many orders, so a customer joined to orders appears once per matching order. A result with repeated customer details is expected for a one-to-many relationship, not automatically a duplicate-data error.
INNER JOIN for matching rows
INNER JOIN returns rows for which the ON condition matches on both sides. It is suitable when the result should include only records with a related row, such as orders with a valid customer. If the relationship is enforced by a foreign key and both tables are consistent, every order may have a customer; in other cases, unmatched records are excluded. NULL join keys do not match through ordinary equality.
SELECT o.order_id, o.created_at, c.email
FROM orders AS o
INNER JOIN customers AS c
ON c.customer_id = o.customer_id
WHERE o.status = 'paid';The query uses short aliases, a qualified join predicate, and a separate row filter. If multiple columns define the relationship, include each required equality. Do not omit a key from the ON clause because the output may still look plausible while combining unrelated rows.
LEFT JOIN to preserve unmatched rows
LEFT JOIN keeps every row from its left input. When no right-side row matches, MySQL returns NULL for right-side columns. This is useful for showing all customers with optional order information or finding customers with no orders. When testing for no match, check a right-side key that cannot be NULL in a real matched row.
SELECT c.customer_id, c.email
FROM customers AS c
LEFT JOIN orders AS o
ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;A frequent mistake is placing a right-table filter in WHERE after a LEFT JOIN. A condition such as WHERE o.status = 'paid' rejects the NULL-extended rows and often turns the result into the behavior of an inner join. If customers without a paid order should remain, put the paid-status condition in ON or use a deliberate EXISTS/NOT EXISTS pattern. See NULL behavior and EXISTS subqueries.
Joining multiple tables and aggregation
A query can join several tables, but each additional relationship changes row cardinality. Joining orders to order_items returns one row per item, not one row per order. If a parent total is summed after that join, the value may be counted multiple times. Decide the grain of the result, aggregate at the correct level, or use a derived table/CTE to calculate a child summary first. This issue is common in dashboards that combine products, orders, and customers.
Use consistent data types for related key columns and define indexes to support foreign keys and common joins. MySQL may reorder joins as part of optimization, so written order is not a guarantee of execution order. Run EXPLAIN to inspect the plan. A join can be efficient even on large data when predicates and indexes fit the workload; a table scan is not automatically wrong if much of the table is needed.
Join design checklist
Name each table and alias by its role, write the key relationship in ON, and select only the columns required. Verify whether unmatched records should appear, whether repeated rows are expected, and how NULL keys should behave. Test with sample data containing zero, one, and multiple related rows. Check foreign-key definitions and run an execution plan when performance matters. These steps are more reliable than adding DISTINCT to hide unexpected row multiplication.
Joins let normalized tables answer real application questions without duplicating all data in one structure. Learn the complementary design concepts in table relationships and foreign keys, then review EXPLAIN plans.