I’m confused about the difference between a cross join and union/union ALL. is the aim of both not to have a table with all columns and all information for all columns?
Would appreciate it if someone could explain briefly the objective of cross join and union/union all and therefore when to use each and how they differ.
Not exactly, they just function in a different way. But don’t worry – it is a common topic to learn how to distinguish between the two.
CROSS JOIN adds records from both sides of the join, depending on the matching records designated in the ON clause (something like running a LEFT and RIGHT join simultaneously, if that helps).
UNION/UNION ALL simply adds data vertically. So, Table_1 UNION Table_2 will only deliver an output if Table_1 and Table_2 have matching field (or fields) and will display the data of Table_2 below the data of Table_1.
Hope this helps.