Set Theory Basics: Why Databases Think in Unions and Intersections
Every SQL JOIN and UNION you've ever written is set theory wearing a friendlier name.
Picture two roommates comparing grocery lists before a shopping trip. If they want everything either of them needs, they combine the lists and cross out duplicates -- that combined list is a union. If they only want to buy what they're both already low on, they keep just the items appearing on both lists -- that's an intersection. Nobody needs a math class to do this instinctively with paper lists; it turns out databases do it exactly the same way, just with rows instead of groceries.
A set, in the math sense, is just a collection of distinct items where order doesn't matter and duplicates don't count twice. That's already how a lot of programmers think about arrays or lists intuitively, but a database table is really a set of rows, and a query result is a new set built from combining or filtering other sets.
SQL's UNION keyword is a union in the literal set-theory sense: it takes the rows returned by two queries and combines them into one result, dropping duplicate rows the same way our roommates would cross a repeated item off the combined grocery list. If you don't want that deduplication -- if you're fine with the same row showing up twice -- UNION ALL skips the cleanup step and keeps every row from both queries.
An INNER JOIN, meanwhile, behaves like an intersection: it keeps only the rows that satisfy a matching condition in both tables, discarding anything that doesn't have a counterpart on the other side. Push further and EXCEPT (or NOT IN, used carefully) mirrors set difference -- "give me what's in my list that isn't in yours," the same question you'd ask before heading to the store alone for the one ingredient your roommate already has.
None of this is a coincidence or a cute metaphor bolted on after the fact -- relational databases were explicitly designed around set theory from the start, which is why query results compose so cleanly: the output of one set operation is just another set, ready to feed into the next UNION, JOIN, or filter. Once a query stops looking like fill-in-the-blank syntax and starts looking like a Venn diagram, a lot of "why is this returning duplicates" bugs get easier to reason about.