So after the SQL Joins Cheatsheet, we need the same for UNION. Again, I’ve chosen a slightly different way of visualising the output of the union. The reason for this is ‘keep it simple’, if you know the data items you start with and the results, then you can easily see how the join works – well thats the idea anyway…
I’ve tried to show the differing terminology, which is used by different versions of SQL.
The UNION (as well as EXCEPT and INTERSECT) keywords are used in an SQL statement to combine data from two or more sets of data (i.e. two or more tables), based on the output; these columns DO NOT need to be table keys, but the output format needs to match.
e.g. If you select a Number from Table1, then try to Union with the output of a Character from Table2, then this will fail…both the same type.
Different SQL UNIONs
The below shows the types of UNION you can use, and the differences between them.
- UNION: Joins outputs from one or more SELECT, returning unique/distinct data
- UNION ALL: Joins outputs from one or more SELECT, returning all data (if the same output is included more than once, then it will be included more than once)
- INTERSECT: Joins outputs from one or more SELECT, returning only data which is included in both outputs (the intersection)
- EXCEPT: Joins outputs from one or more SELECT, returning only data from the 1st output, not in the 2nd output
I’d appreciate your feedback, as this is one of first blog posts;
how does it read? is it pitched right? or too technical?
have you tried the code? does it work for you?
* The SQL provided is quite generic, as I tend to use SNOWFLAKE, T-SQL and DB2-SQL but similar types of joins are available in other forms of SQL