AI Hints
Homeโ€บSQL Tutorialโ€บInterview Questions, Part 3
SQL ยท Lesson 101 of 126

Interview Questions, Part 3

The theory questions.

โฑ 2 min readโ–ถ Video at 2:22:18๐ŸŽ“ Free
Watch this lesson in the SQL courseWatch on YouTube โ†—Full SQL course

Not every interview question asks you to write code. Many ask you to explain an idea in one or two sentences. What's the difference between where and having? Between union and union all? These are quick wins, if you've practised the answers. Let's go through the most common ones.

Interview Questions, Part 3
From the video: Interview Questions, Part 3
  • Explain it in a sentence
  • Quick wins
  • The most common ones

WHERE vs HAVING

Question one. What's the difference between where and having? Answer. Where filters rows, before grouping. Having filters groups, after grouping, so it can use aggregates like count and sum.

WHERE

  • Filters rows
  • Before GROUP BY
  • No aggregates

HAVING

  • Filters groups
  • After GROUP BY
  • Uses COUNT, SUM ...

UNION vs UNION ALL

Question two. Union versus union all. Let's prove it. We stack the customers' countries on top of themselves, both ways. Run it. Union removes duplicates, leaving six countries. Union all keeps everything, one thousand rows. Union all is faster, because it doesn't have to check for duplicates.

query.sqlsql
SELECT COUNT(*) AS with_union
FROM (SELECT country FROM customers
      UNION
      SELECT country FROM customers);

SELECT COUNT(*) AS with_union_all
FROM (SELECT country FROM customers
      UNION ALL
      SELECT country FROM customers);
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚ with_union โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ 6          โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚ with_union_all โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ 1000           โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

INNER vs LEFT JOIN

Question three. Inner join versus left join. Answer. An inner join keeps only rows that match in both tables. A left join keeps every row from the left table, with nulls where there's no match.

INNER JOIN

  • Matches only
  • Unmatched rows dropped
  • Both sides needed

LEFT JOIN

  • All left rows
  • NULLs when no match
  • Find what's missing

Summary

Where versus having, union versus union all, inner versus left. Short, clear answers win interviews. More theory next.

Key takeaways
  • WHERE: rows, HAVING: groups
  • UNION dedupes, ALL keeps
  • INNER vs LEFT
Next up: More Theory Questions โ€” NULL, keys, and DELETE