I have previously written two articles on UNION and they are quite popular. I was reading SQL book Sams Teach Yourself Microsoft SQL Server T-SQL in 10 Minutes By Ben Forta and I came across three rules of UNION and I felt like mentioning them here.
- A UNION must be composed of two or more SELECT statements, each separated by the keyword UNION.
- Each query in a UNION must contain the same columns, expressions, or aggregate functions, and they must be listed in the same order.
- Column datatypes must be compatible: They need not be the same exact same type, but they must be of a type that SQL Server can implicitly convert.
SQL SERVER – Union vs. Union All – Which is better for performance?
SQL SERVER – Insert Multiple Records Using One Insert Statement – Use of UNION ALL
Reference : Pinal Dave (https://blog.sqlauthority.com), Ben Forta
But if union and then group by is good or directly using inner or outer join is better?
Suppose, I don’t have same number of parameters for second table. I want one field which is not present in second table. But, I want to keep that column. It should show null values for second table which doesn’t have that field and should show data for the table which has that field.
select col1, col2 from table1
select col1,null from table2