Chapter 11 Intermediate 55 Questions

Practice Questions — JOINS - INNER, LEFT, RIGHT, FULL OUTER, CROSS

← Back to Notes

14 Easy

14 Medium

4 Hard

Topic-Specific Questions

Question 1

Easy

Given the sample tables (5 customers, 5 orders), how many rows does this query return?

SELECT c.name, o.amount
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id;

INNER JOIN only returns rows where both sides match. Order 105 has no matching customer.

4 rows

Question 2

Easy

How many rows does this query return?

SELECT c.name, o.amount
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id;

LEFT JOIN keeps every customer. Unmatched customers appear once with NULLs.

6 rows

Question 3

Easy

How many rows does this return?

SELECT *
FROM customers c
RIGHT JOIN orders o ON c.id = o.customer_id;

RIGHT JOIN keeps every order.

5 rows

Question 4

Easy

What is the result count of this CROSS JOIN? customers has 5 rows, products has 2 rows.

SELECT * FROM customers CROSS JOIN products;

CROSS JOIN multiplies row counts.

10 rows

Question 5

Easy

Which customers appear in the result?

SELECT c.name
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.id IS NULL;

This is the 'customers with no orders' pattern.

Meera Iyer
Vikram Singh

Question 6

Easy

True or False: JOIN and INNER JOIN mean the same thing in MySQL.

Think about default JOIN type in MySQL.

True

Question 7

Easy

What does this query compute?

SELECT c.name, COUNT(o.id) AS num_orders
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.name;

LEFT JOIN + COUNT on the right key.

Number of orders for each customer, including 0 for customers without orders

Question 8

Easy

What is the output of this query given our sample data?

SELECT COUNT(*) 
FROM customers c
JOIN orders o ON c.id = o.customer_id;

INNER JOIN rows.

4

Question 9

Easy

What is the keyword to join two tables and return only matching rows?

The default JOIN.

INNER JOIN (or just JOIN)

Question 10

Medium

What does this query return given our sample data?

SELECT c.name, SUM(o.amount) AS total
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.name
HAVING total IS NULL;

SUM of NULL values is NULL, not 0.

Meera Iyer (total NULL)
Vikram Singh (total NULL)

Question 11

Medium

Why does this give no error but wrong results?

SELECT c.name, o.amount
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.amount > 500;

WHERE on right-table column after LEFT JOIN.

The WHERE filter drops rows where amount is NULL, converting LEFT JOIN into INNER JOIN. Customers with no orders disappear.

Question 12

Medium

What does this query find? (assume standard customers/orders tables)

SELECT DISTINCT c.name
FROM customers c
JOIN orders o ON c.id = o.customer_id
WHERE o.amount > 2000;

Distinct customers with at least one high-value order.

Aarav Kumar (order 102, 2300) and Rohan Mehta (order 104, 5400)

Question 13

Medium

What does this return?

SELECT c.city, COUNT(DISTINCT c.id) AS customer_count,
       COUNT(o.id) AS order_count
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.city;

Group by city, count distinct customers and total orders.

Each city with its customer count and total order count. Cities with no orders show order_count=0.

Question 14

Medium

Which JOIN should you use for 'show every department and its employees, including empty departments'?

Every department must appear, even empty ones.

LEFT JOIN from departments to employees

Question 15

Medium

What is wrong with this query?

SELECT name, amount
FROM customers
JOIN orders ON customers.id = orders.customer_id;

Two tables have a column named 'name' or similar? What about id?

No error here because only customers has 'name' and only orders has 'amount'. But the query breaks readability; always qualify columns in JOIN queries.

Question 16

Medium

How many rows does this produce given our sample tables?

SELECT c.id
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
UNION
SELECT c.id
FROM customers c
RIGHT JOIN orders o ON c.id = o.customer_id;

UNION removes duplicates. Think about the distinct customer IDs produced.

6 rows

Question 17

Medium

Why does COUNT(*) vs COUNT(o.id) differ on a LEFT JOIN?

NULL handling in COUNT.

COUNT(*) counts all rows including unmatched ones (where o.id is NULL). COUNT(o.id) ignores NULLs, giving only matched rows.

Question 18

Medium

Predict the output count for this query given 3 customers with 2, 3, and 0 orders respectively, using INNER JOIN.

Only matching rows.

5 rows

Question 19

Hard

What is the output of this query given our sample data?

SELECT c.name, o.amount
FROM customers c
LEFT JOIN orders o 
  ON c.id = o.customer_id AND o.amount > 1000
ORDER BY c.id;

The amount filter is in ON, not WHERE.

Aarav 1500, Aarav 2300, Priya NULL, Rohan 5400, Meera NULL, Vikram NULL

Question 20

Hard

What does this query find?

SELECT c.name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

NOT EXISTS equivalent of LEFT JOIN + IS NULL.

Meera Iyer, Vikram Singh

Mixed & Application Questions

Question 1

Easy

Which JOIN returns rows only when both tables have matching values?

The default JOIN.

INNER JOIN

Question 2

Easy

Which JOIN keeps all rows from the left table even if there is no match on the right?

All-left-rows join.

LEFT JOIN (or LEFT OUTER JOIN)

Question 3

Easy

Does MySQL support FULL OUTER JOIN directly?

MySQL specifically.

No. MySQL does not support FULL OUTER JOIN. Simulate with LEFT JOIN UNION RIGHT JOIN.

Question 4

Easy

What is the result size of CROSS JOIN between a table with 100 rows and a table with 50 rows?

Cartesian product.

5000 rows

Question 5

Easy

What does this query do?

SELECT c.name, o.id
FROM customers c, orders o
WHERE c.id = o.customer_id;

This is the old implicit join syntax.

Same as INNER JOIN — returns matching customer/order pairs.

Question 6

Medium

What is the output given customers(1,2,3,4,5) and orders with customer_ids (1,1,2,3,7)?

SELECT COUNT(*) FROM customers c
RIGHT JOIN orders o ON c.id = o.customer_id;

RIGHT JOIN keeps every order.

5

Question 7

Medium

Find customers in Bengaluru who have at least one order. Write the query.

JOIN + WHERE on city + DISTINCT.

SELECT DISTINCT c.name
FROM customers c
JOIN orders o ON c.id = o.customer_id
WHERE c.city = 'Bengaluru';

Question 8

Medium

Write a query to find each customer's most recent order date. Customers without orders should still appear.

LEFT JOIN + MAX + GROUP BY.

SELECT c.name, MAX(o.order_date) AS last_order
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.name;

Question 9

Medium

What is the difference between ON and WHERE in a LEFT JOIN?

Think about when filtering happens.

ON filters during the join — unmatched rows from the left table are still kept (as NULL). WHERE filters after the join — it can drop those NULL rows, effectively turning LEFT JOIN into INNER JOIN.

Question 10

Medium

Why is RIGHT JOIN rarely used in production code?

Readability and team conventions.

Any RIGHT JOIN can be rewritten as LEFT JOIN by swapping the table order. LEFT JOIN reads more naturally because the 'main' table is on the left.

Question 11

Hard

Write a query to find the most valuable customer (highest total spend).

GROUP BY customer, ORDER BY total DESC, LIMIT 1.

SELECT c.name, SUM(o.amount) AS total
FROM customers c
JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.name
ORDER BY total DESC
LIMIT 1;

Question 12

Hard

Given customers (5 rows) and orders (5 rows, one with invalid customer_id=7), what is the result count of a MySQL FULL OUTER JOIN simulation?

LEFT JOIN UNION RIGHT JOIN; UNION deduplicates.

7 distinct rows

Multiple Choice Questions

MCQ 1

Which JOIN returns only rows with matching values in both tables?

A. LEFT JOIN
B. RIGHT JOIN
C. INNER JOIN
D. FULL OUTER JOIN

Answer: C
C is correct. INNER JOIN returns only rows where the ON condition matches on both sides. A, B, D all keep non-matching rows by design.

MCQ 2

Which JOIN keeps every row from the left table even if there is no match?

A. INNER JOIN
B. LEFT JOIN
C. RIGHT JOIN
D. CROSS JOIN

Answer: B
B is correct. LEFT JOIN preserves every row from the left table. Right-side columns are NULL when no match exists.

MCQ 3

What does SELECT * FROM a CROSS JOIN b return when a has 10 rows and b has 4 rows?

A. 10 rows
B. 14 rows
C. 40 rows
D. 4 rows

Answer: C
C is correct. CROSS JOIN produces the cartesian product: every row of a combined with every row of b = 10 * 4 = 40 rows.

MCQ 4

Which statement is TRUE about MySQL and FULL OUTER JOIN?

A. MySQL supports FULL OUTER JOIN with the keyword FULL
B. MySQL does not support FULL OUTER JOIN directly; use UNION of LEFT and RIGHT JOINs
C. FULL OUTER JOIN returns only matching rows
D. FULL OUTER JOIN is the same as CROSS JOIN

Answer: B
B is correct. MySQL has no native FULL OUTER JOIN. Simulate it with LEFT JOIN ... UNION RIGHT JOIN .... PostgreSQL and SQL Server support it natively.

MCQ 5

Which keyword is used to specify the join condition?

A. WHERE
B. HAVING
C. ON
D. WITH

Answer: C
C is correct. The ON clause specifies how tables are matched. WHERE filters rows after the join; HAVING filters groups; WITH defines CTEs.

MCQ 6

If INNER JOIN and JOIN are written, what is the difference in MySQL?

A. INNER JOIN is slower
B. They are identical — INNER is optional
C. JOIN means CROSS JOIN
D. JOIN only works on 2 tables; INNER JOIN allows more

Answer: B
B is correct. In MySQL, JOIN and INNER JOIN are synonyms. INNER is optional. Use whichever feels clearer.

MCQ 7

After a LEFT JOIN, what do unmatched right-table columns contain?

A. 0
B. Empty string
C. NULL
D. Error

Answer: C
C is correct. Unmatched right rows in a LEFT JOIN produce NULL for every right-table column. Use COALESCE or IFNULL to replace with a default value.

MCQ 8

Which query finds customers with no orders?

A. SELECT * FROM customers JOIN orders ON customers.id = orders.customer_id WHERE orders.id IS NULL
B. SELECT * FROM customers LEFT JOIN orders ON customers.id = orders.customer_id WHERE orders.id IS NULL
C. SELECT * FROM customers RIGHT JOIN orders ON customers.id = orders.customer_id WHERE orders.id IS NULL
D. SELECT * FROM customers CROSS JOIN orders WHERE orders.id IS NULL

Answer: B
B is correct. LEFT JOIN keeps every customer. WHERE orders.id IS NULL keeps only those without a matching order. A would produce no rows (INNER JOIN drops NULLs). C is wrong direction. D is cartesian.

MCQ 9

Given 10 customers and 5 orders (all with valid customer_ids), how many rows does INNER JOIN return?

A. 10
B. 5
C. 50
D. 15

Answer: B
B is correct. INNER JOIN returns one row per matching pair. If each of the 5 orders matches exactly one customer, the result is 5 rows (not 10 — unmatched customers are dropped).

MCQ 10

What happens when you filter a LEFT JOIN's right table in WHERE?

A. The filter applies only to matched rows
B. NULLs from unmatched rows fail the filter, turning LEFT JOIN into INNER JOIN
C. The query fails
D. The filter is ignored

Answer: B
B is correct. Any comparison with NULL is not true. So unmatched rows (with NULL in right columns) get dropped by WHERE, effectively making the LEFT JOIN act like INNER JOIN. Move filters to ON or add IS NULL checks in WHERE.

MCQ 11

Which query correctly counts the number of orders per customer including customers with zero orders?

A. SELECT c.name, COUNT(*) FROM customers c JOIN orders o ON c.id = o.customer_id GROUP BY c.id
B. SELECT c.name, COUNT(o.id) FROM customers c LEFT JOIN orders o ON c.id = o.customer_id GROUP BY c.id, c.name
C. SELECT c.name, COUNT(*) FROM customers c LEFT JOIN orders o ON c.id = o.customer_id GROUP BY c.id
D. SELECT c.name, COUNT(o.id) FROM customers c INNER JOIN orders o ON c.id = o.customer_id GROUP BY c.id

Answer: B
B is correct. LEFT JOIN keeps every customer. COUNT(o.id) returns 0 for unmatched rows (COUNT ignores NULLs). C is almost right but COUNT(*) would return 1 for customers with no orders (counting the NULL row). D uses INNER JOIN and drops zero-order customers.

MCQ 12

What is the result of USING (customer_id) vs ON a.customer_id = b.customer_id?

A. USING requires columns to have the same name; ON is general
B. USING is faster; ON is slower
C. ON only works with primary keys
D. They produce different result sets

Answer: A
A is correct. USING is shorthand when both tables have a column with the exact same name (the joined column appears only once in the result). ON accepts any boolean expression and does not require matching column names.

MCQ 13

What does this query return?

SELECT COUNT(*) FROM customers c LEFT JOIN orders o ON c.id = o.customer_id;

Given 5 customers and 4 orders (each matching a distinct customer).

A. 4
B. 5
C. 9
D. 20

Answer: B
B is correct. 4 matched rows + 1 unmatched customer = 5 rows total. Every customer appears at least once.

MCQ 14

Which pattern correctly simulates FULL OUTER JOIN in MySQL?

A. SELECT * FROM a FULL OUTER JOIN b ON a.id = b.id
B. SELECT * FROM a LEFT JOIN b ON a.id = b.id UNION SELECT * FROM a RIGHT JOIN b ON a.id = b.id
C. SELECT * FROM a CROSS JOIN b
D. SELECT * FROM a INNER JOIN b ON a.id = b.id OR a.id IS NULL

Answer: B
B is correct. MySQL does not support FULL OUTER JOIN natively. UNION of LEFT JOIN and RIGHT JOIN (UNION deduplicates automatically) gives the correct result. A would fail; C is cartesian; D is nonsense.

MCQ 15

A table of 10,000 rows is CROSS JOINed with a table of 1,000 rows. What is the result size and is this safe?

A. 11,000 rows; safe
B. 10,000 rows; safe
C. 10,000,000 rows; dangerous
D. 1,000 rows; safe

Answer: C
C is correct. 10,000 * 1,000 = 10 million rows. This will likely hang your session and stress disk/memory. CROSS JOIN without a filter on large tables is an anti-pattern.

Coding Challenges

Challenge 1: List Customers With Their Orders

Easy

Given the sample customers and orders tables, write a query that lists each order along with the customer's name and city. Use INNER JOIN.

SELECT c.name, c.city, o.id AS order_id, o.amount, o.order_date
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id
ORDER BY c.name, o.order_date;

Challenge 2: Find Customers With No Orders

Easy

Write a query that lists all customers who have never placed an order.

SELECT c.id, c.name, c.city
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.id IS NULL;

Challenge 3: Total Revenue Per City

Medium

Calculate the total revenue from each city. Cities with no orders should still appear with 0.

SELECT c.city, COALESCE(SUM(o.amount), 0) AS total_revenue
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.city
ORDER BY total_revenue DESC;

Challenge 4: Most Valuable Customer

Medium

Find the customer who has spent the most in total.

SELECT c.name, SUM(o.amount) AS total_spent
FROM customers c
JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.name
ORDER BY total_spent DESC
LIMIT 1;

Challenge 5: Customers Who Placed Orders Over 2000

Medium

Find the distinct names of customers who have placed at least one order of amount greater than 2000.

SELECT DISTINCT c.name
FROM customers c
JOIN orders o ON c.id = o.customer_id
WHERE o.amount > 2000;

Challenge 6: Orphan Orders Detection

Hard

Find all orders whose customer_id does NOT exist in the customers table. Return order_id, customer_id, and amount.

SELECT o.id AS order_id, o.customer_id, o.amount
FROM orders o
LEFT JOIN customers c ON c.id = o.customer_id
WHERE c.id IS NULL;

Challenge 7: Simulate FULL OUTER JOIN

Hard

Return every customer and every order, matching where possible. Include unmatched customers (NULL order info) and orphan orders (NULL customer info).

SELECT c.id AS cust_id, c.name, o.id AS order_id, o.amount
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
UNION
SELECT c.id, c.name, o.id, o.amount
FROM customers c
RIGHT JOIN orders o ON c.id = o.customer_id;

Challenge 8: Common Customers Across Two Periods

Hard

Using the orders table, find customers who placed orders in BOTH January 2026 and February 2026.

SELECT DISTINCT c.name
FROM customers c
JOIN orders o1 ON c.id = o1.customer_id
     AND o1.order_date BETWEEN '2026-01-01' AND '2026-01-31'
JOIN orders o2 ON c.id = o2.customer_id
     AND o2.order_date BETWEEN '2026-02-01' AND '2026-02-28';

Need to Review the Concepts?

Go back to the detailed notes for this chapter.

Read Chapter Notes

Want to master SQL and databases with a mentor?

Explore our MySQL Masterclass