What is the difference between INNER JOIN and LEFT JOIN?

INNER JOIN returns only rows that have matches in BOTH tables. LEFT JOIN returns ALL rows from the left table, plus matching rows from the right table. If no match exists, LEFT JOIN shows NULL for right table columns. INNER JOIN excludes non-matching rows entirely.

What happens if you forget the ON clause in an INNER JOIN?

You get a cartesian product - every row from the first table is combined with every row from the second table. For 1000 orders and 500 customers, you get 500,000 result rows instead of the expected ~1000. This is usually unintended and can crash applications or consume excessive resources.

Why is indexing important for JOIN performance?

Without indexes, the database must scan entire tables to find matching rows. With proper indexes on join columns (foreign keys and primary keys), the database can quickly locate matching rows using index lookups instead of full table scans. This can improve performance by 100x or more.

Write a query to show customer names with their order totals, but only for customers who have placed orders.

SELECT c.customer_name, o.order_id, o.total_amount

FROM customers c

INNER JOIN orders o ON c.customer_id = o.customer_id

ORDER BY c.customer_name;

INNER JOIN ensures only customers with orders appear. The ON clause matches customer IDs between tables.

Join three tables: orders, customers, and products. Show customer name, product name, and order date.

SELECT c.customer_name, p.product_name, o.order_date

FROM orders o

INNER JOIN customers c ON o.customer_id = c.customer_id

INNER JOIN order_items oi ON o.order_id = oi.order_id

INNER JOIN products p ON oi.product_id = p.product_id;

Multiple INNER JOINs chain together. Each JOIN adds another table to the result set.