> They aren't sets because they are ordered, they are
> lists.
Half correct - the other reason the results aren't sets are because they are also able to return duplicates, as evidenced by UNION ALL and INTERSECT ALL.
Were I being pedantic, I would turn to the (horrifyingly inconsistently written) SQL1992 standard, which explicitly says that your columnar inputs are multisets (which is a posh way of saying "not a set").
Coming back to your original assertion:
>> you will still have to understand the basics of set
>> theory. Unions, intersects
In 15 years of software development, open-source and commercial, mostly with databases, I've only used UNION, INTERSECT, and EXCEPT in SQL when doing academic homework, translating from relational algebra and relational calculus. I note the world's most popular open-source database doesn't support INTERSECT.
But most people don't write SQL by hand any more, they use an ORM - most of which have scant support for UNION, INTERSECT, and EXCEPT, because they are obvious vestigial details left over from archaic database theory. I have resisted the temptation to become actually pedantic by discussing the SQL treatment of NULLs.
This constitutes "almost no practical overlap".
But look, here's the meat: Fundamentally, sets are distinguished from other list-like data types by having no ordering, and having no repetition. Practical SQL usage requires you to understand that you will have duplicates, that you may well need to aggregate those duplicates somehow, and that order matters.
