INNER JOIN

📌 Definition:

📖 Detailed Explanation:

Relational databases split data across tables to avoid duplication — users in one table, their orders in another, linked by a foreign key (orders.user_id references users.id). To validate 'did this user's order contain the right items', you must recombine those tables with a JOIN. INNER JOIN returns only matched pairs: an order with no matching user, or a user with no orders, is excluded. The join condition (ON orders.user_id = users.id) specifies how rows correspond; getting it wrong — joining on the wrong columns or omitting a condition — can produce a Cartesian explosion (every row paired with every other) or silently wrong matches. Table aliases (o, u) keep multi-table queries readable and are required when the same column name exists in both tables. For testers, joins turn fragmented data into a single verifiable picture of an end-to-end record.

🔑 Key Points:

• INNER JOIN returns only rows that match in BOTH tables
• Join on the foreign-key relationship (orders.user_id = users.id)
• Unmatched rows on either side are excluded (that's what makes it 'inner')
• A missing/incorrect ON condition can cause a Cartesian explosion or wrong matches
• Use table aliases and qualify columns (o.total, u.email) for clarity

🌍 Real-World Example:

📎 Code Example:

-- Order with its customer's details (only matched rows)
SELECT o.id AS order_id, u.email, u.name, o.total
FROM orders o
INNER JOIN users u ON u.id = o.user_id
WHERE o.id = 5001;

-- Order line items joined to product details
SELECT oi.order_id, p.name, oi.quantity, oi.price
FROM order_items oi
JOIN products p ON p.id = oi.product_id
WHERE oi.order_id = 5001;

-- Three-table join: customer + order + items
SELECT u.email, o.id AS order_id, p.name, oi.quantity
FROM users u
JOIN orders o       ON o.user_id = u.id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p     ON p.id = oi.product_id
WHERE u.email = 'qa@qapractice.com';