JOIN Type 1

INNER JOIN — Only Matching Rows

INNER JOIN returns only rows that have matching values in BOTH tables. If a row in either table has no match, it is excluded from results.

SQL — INNER JOIN
— Orders with their customer names
SELECT
  c.customer_name,
  o.order_date,
  o.order_amount,
  o.status
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id
ORDER BY o.order_date DESC;
 
— Join 3 tables
SELECT c.customer_name, p.product_name, oi.quantity
FROM customers c
INNER JOIN order_items oi ON c.id = oi.customer_id
INNER JOIN products p ON oi.product_id = p.id;
Use when: You only want records that exist in BOTH tables. Any customer with no orders, or any order with no customer record, will be excluded.
JOIN Type 2 (Most Used)

LEFT JOIN — All Left Rows + Matching Right

LEFT JOIN returns ALL rows from the left table. For rows with no match in the right table, the right columns are filled with NULL. This is the most commonly used JOIN in analytics.

SQL — LEFT JOIN
— ALL customers, even those with zero orders
SELECT
  c.customer_name,
  COUNT(o.order_id) AS total_orders,
  COALESCE(SUM(o.order_amount), 0) AS total_revenue
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.customer_name
ORDER BY total_revenue DESC;
Why LEFT JOIN + COALESCE: When there are no matching orders, SUM returns NULL. COALESCE(SUM(…), 0) converts that NULL to 0 — essential for clean reporting.
Pattern — Very Common in Interviews

Anti-JOIN — Find Records That DON’T Match

The anti-join pattern (LEFT JOIN + WHERE IS NULL) finds rows in the left table that have NO match in the right table. This is one of the most commonly asked interview patterns.

SQL — Anti-JOIN
— Customers who have NEVER placed an order
SELECT c.customer_name, c.city, c.join_date
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.order_id IS NULL;
 
— Products that have NEVER been ordered
SELECT p.product_name, p.category
FROM products p
LEFT JOIN order_items oi ON p.id = oi.product_id
WHERE oi.order_id IS NULL;
Interview gold: Any time you see ‘never’, ‘no orders’, ‘not purchased’, ‘missing’ — think LEFT JOIN + WHERE right.id IS NULL. This pattern is asked at almost every company.
Advanced Pattern

SELF JOIN — A Table Joined to Itself

SQL — SELF JOIN
— Find each employee and their manager name
SELECT
  e.emp_name AS employee,
  m.emp_name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.emp_id;
Self JOINs are used for hierarchical data (employees-managers, categories-subcategories) or comparing rows in the same table (e.g. finding pairs, finding rows within N days of each other).
Interview Questions

Practice Questions — JOINs

Easy
Find the names and order amounts of all orders placed by customers in Bengaluru.
FlipkartInfosys
+
JOIN customers and orders, then filter by city in WHERE.
SELECT c.customer_name, o.order_amount, o.order_date
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id
WHERE c.city = ‘Bengaluru’;
Medium
Find all customers who have NOT placed any orders.
AmazonMeeshoCRED
+
Classic anti-join pattern: LEFT JOIN + WHERE order_id IS NULL.
SELECT c.customer_name, c.city
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.order_id IS NULL;
Hard
For each customer, show their total orders, total revenue, and the date of their most recent order.
GooglePhonePe
+
LEFT JOIN so customers with no orders are included. Use COALESCE to handle NULLs cleanly.
SELECT
  c.customer_name,
  COUNT(o.order_id) AS total_orders,
  COALESCE(SUM(o.order_amount), 0) AS total_revenue,
  MAX(o.order_date) AS last_order_date
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.customer_name;

Struggling with JOINs?

Book a free mock session. We’ll walk you through JOIN questions from Flipkart, Amazon and more.

Book Free Session
← PreviousGROUP BY & AggregationsNext →Subqueries & CTEs