You have two tables, shop_1 and shop_2, both having the same structure with customer_id and customer_name. Write a query that retrieves the names of customers who appear in both tables.

Question

Accepted Answer

Finding customers present in both shop_1 and shop_2 is a classic set-intersection problem. It can be solved with an INNER JOIN on customer_id (portable across all databases) or with the INTERSECT operator (simpler syntax, but not supported by MySQL).

Key Points:
• INNER JOIN matches rows from both tables on a shared key and works in every major SQL database, including MySQL.
• INTERSECT returns rows that appear in the result sets of both SELECT statements and reads very cleanly, but MySQL only added support for it in version 8.0.31+; older versions need a workaround.
• When joining, select DISTINCT customer_name if a customer could have multiple matching rows to avoid duplicate output.
• An alternative to INTERSECT in databases that lack it is a WHERE ... IN (subquery) pattern.

Example:
If shop_1 and shop_2 both list "Alice" as a customer, an INNER JOIN on customer_id between the two tables returns her name once in the result set, while customers unique to only one shop are excluded.

Code Example:
```
-- Using INNER JOIN (works everywhere, including MySQL)
SELECT DISTINCT s1.customer_name
FROM shop_1 s1
JOIN shop_2 s2 ON s1.customer_id = s2.customer_id;

-- Using INTERSECT (not available in MySQL)
SELECT customer_name FROM shop_1
INTERSECT
SELECT customer_name FROM shop_2;
```

Interview Tip:
A concise interview answer is:

"I'd join shop_1 and shop_2 on customer_id and select the distinct customer_name, which works on any database. If the database supports it, INTERSECT is a cleaner way to express the same intent, but MySQL didn't support it until fairly recent versions, so the JOIN approach is the safer default."