SQL "Difference Between" Questions — Interview Questions

How to use this lesson

"What's the difference between X and Y?" is the most common SQL interview format. Answer with a one-line contrast, then give one example, then state one consequence (performance, NULL behaviour, or whether it can be rolled back). That three-part shape works for every question here.

Q1. Two tables, shop_1 and shop_2, have the same structure. How do you find customers who appear in both?

Short answer: Use an INNER JOIN on the customer key, INTERSECT, or EXISTS.

-- 1) INNER JOIN (works everywhere); DISTINCT guards against duplicates in either table
SELECT DISTINCT s1.customer_id, s1.customer_name
FROM shop_1 s1
JOIN shop_2 s2 ON s2.customer_id = s1.customer_id;

-- 2) INTERSECT (PostgreSQL, Oracle, SQL Server, and MySQL 8.0.31+): returns distinct rows present in both
SELECT customer_id, customer_name FROM shop_1
INTERSECT
SELECT customer_id, customer_name FROM shop_2;

-- 3) EXISTS: a semi-join, which never duplicates rows
SELECT s1.customer_id, s1.customer_name
FROM shop_1 s1
WHERE EXISTS (SELECT 1 FROM shop_2 s2 WHERE s2.customer_id = s1.customer_id);

Key points to cover:

• Match on the ID, not the name. Two different customers can share a name.
• Older answers say MySQL lacks INTERSECT. It was added in MySQL 8.0.31.

Learn it in depth → SQL Joins

Q2. What's the difference between INNER JOIN and OUTER JOIN?

Short answer: An INNER join returns only the rows that match in both tables. An OUTER join also keeps the non-matching rows, filling the missing side with NULLs:

• LEFT: all rows from the left table.
• RIGHT: all rows from the right table.
• FULL: all rows from both tables.

-- customers and their orders, INCLUDING customers who never ordered
SELECT c.name, o.id AS order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;

-- anti-join: customers with NO orders
SELECT c.name FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

Common trap: putting a filter on the right table in WHERE (WHERE o.status = 'PAID') after a LEFT JOIN. It removes the NULL rows, and silently turns the query into an inner join. Put such conditions in the ON clause instead.

Key points to cover:

• MySQL has no FULL OUTER JOIN. Emulate it with a LEFT JOIN UNION a RIGHT JOIN.

Q3. WHERE vs HAVING?

Short answer: WHERE filters rows before grouping, and can't contain aggregates. HAVING filters groups after GROUP BY, and typically uses aggregates.

SELECT customer_id, COUNT(*) AS orders
FROM orders
WHERE created_at >= '2026-01-01'       -- rows
GROUP BY customer_id
HAVING COUNT(*) > 5;                   -- groups

Q4. UNION vs UNION ALL?

Short answer: Both stack the results of two SELECTs, which must have the same number of columns with compatible types. UNION removes duplicates, which needs a sort or hash step, so it's slower. UNION ALL keeps everything, and is faster.

Key points to cover:

• Use UNION ALL whenever duplicates are impossible, or acceptable. It's a frequent, free performance win.

Q5. DELETE vs TRUNCATE?

Short answer:

Q6. PRIMARY KEY vs UNIQUE?

Short answer: A primary key is the row's identity. There's one per table, it's NOT NULL, and in InnoDB it's the clustered index. A UNIQUE constraint prevents duplicate values. There can be many per table, and it allows NULLs.

Common trap: "UNIQUE allows exactly one NULL." That's true in SQL Server. In MySQL and PostgreSQL, NULLs don't count as equal, so multiple NULLs are allowed (PostgreSQL 15+ can opt into NULLS NOT DISTINCT).

Q7. DROP vs TRUNCATE?

Short answer: DROP TABLE removes the table itself: its data, structure, indexes, constraints, triggers and privileges. TRUNCATE removes all rows but keeps the table definition, ready for reuse.

Key points to cover:

• Both are DDL, and in MySQL both commit implicitly.

Q8. VARCHAR vs CHAR?

Short answer: CHAR(n) is fixed length: values are right-padded with spaces to n characters (and trailing spaces are removed when read in MySQL). VARCHAR(n) is variable length: it stores only the actual characters, plus 1–2 bytes recording the length.

Key points to cover:

• Use CHAR for genuinely fixed-width codes: ISO country code CHAR(2), currency CHAR(3). Use VARCHAR for everything else.
• The n is in characters, not bytes. With utf8mb4, a character can take up to 4 bytes.

Q9. IN vs EXISTS?

Short answer: IN checks whether a value is in a list or a subquery's results. EXISTS checks whether a (usually correlated) subquery returns at least one row, and stops at the first match. Modern optimisers often turn both into the same semi-join, so performance is usually similar. The real difference is NULL handling with NOT IN.

-- employees who are not managers
SELECT * FROM employees e
WHERE e.id NOT IN (SELECT m.employee_id FROM managers m);        -- ❌ returns NOTHING if any employee_id is NULL

SELECT * FROM employees e
WHERE NOT EXISTS (SELECT 1 FROM managers m WHERE m.employee_id = e.id);   -- ✅ NULL-safe

Common trap: x NOT IN (1, 2, NULL) is never TRUE, because x <> NULL is UNKNOWN. Prefer NOT EXISTS, or a LEFT JOIN … IS NULL anti-join.

Q10. JOIN vs subquery?

Short answer: A JOIN combines columns from several tables into one result. A subquery is a query nested inside another: in WHERE (filtering), FROM (a derived table) or SELECT (a scalar value). Use a join when you need columns from both tables. Use a subquery when you only need to filter on the other table's data, or when it reads more clearly.

-- join: data from both tables
SELECT e.name, d.name AS department FROM employees e JOIN departments d ON d.id = e.department_id;

-- subquery: filter only
SELECT name FROM employees WHERE department_id IN (SELECT id FROM departments WHERE location = 'Pune');

Key points to cover:

• Optimisers frequently rewrite one form into the other. Check EXPLAIN rather than assuming.
• Correlated subqueries in SELECT can run once per row, so watch for them on large tables.

Follow-up questions this topic invites — and their answers

Q: What is a CROSS JOIN? A: The Cartesian product: every row of A paired with every row of B. It's useful for generating combinations (sizes × colours), and dangerous by accident, for example when a join condition is missing.

Q: What's the difference between COUNT(*), COUNT(1) and COUNT(col)? A: The first two count every row, and perform the same. COUNT(col) counts only the non-NULL values of that column.

Q: DELETE without a WHERE vs TRUNCATE: which should you use to empty a table? A: TRUNCATE is faster, and resets the identity, but it bypasses triggers, and (in MySQL) can't be rolled back. DELETE is safer inside transactional workflows, and when triggers or foreign-key cascades must run.

Q: What's the difference between UNION and a JOIN? A: UNION appends rows vertically (same columns, more rows). A JOIN combines columns horizontally (more columns, matched rows).