UNION and UNION ALL Operator in SQL:
(1) “UNION” & “UNION ALL” operators are used to combine two SELECT statement results.
(2) The difference between “UNION” and UNION ALL” is that “UNION” select only unique values from two columns having the same datatype and same sequence whereas “UNION ALL” select all values whether it is duplicate or
not.
(3) For using this operator, both tables have the same sequence of columns and datatypes.
(4) Syntax of “UNION” & “UNION ALL” statements are:
| SELECT Col1, Col2, …ColN FROM table_name1 UNION SELECT Col1, Col2, …ColN FROM table_name2 SELECT Col1, Col2, …ColN FROM table_name1 UNION ALL SELECT Col1, Col2, …ColN FROM table_name2. |
(5) Let’s take an example, Show City name for two tables VENDOR and CUSTOMER.
(6) Select the database in the SQL server and click on the “New Query” button as shown.
(7) After that new query editor open as shown.
(8) After doing the above steps, write the query in the editor, as shown.
Source Code:
| –// With “UNION” operator Select ‘Vendor’ AS [Source Type],City from dbo.[CRONUS India Ltd_$Vendor$437dbf0e-84ff-417a-965d-ed2bb9650972] where City = ‘LONDON’ UNION Select ‘Customer’, City from dbo.[CRONUS India Ltd_$Customer$437dbf0e-84ff-417a-965d-ed2bb9650972] where City = ‘LONDON’ –// With “UNION” operator –// With “UNION ALL” operator Select ‘Vendor’ AS [Source Type],City from dbo.[CRONUS India Ltd_$Vendor$437dbf0e-84ff-417a-965d-ed2bb9650972] where City = ‘LONDON’ UNION ALL Select ‘Customer’, City from dbo.[CRONUS India Ltd_$Customer$437dbf0e-84ff-417a-965d-ed2bb9650972] where City = ‘LONDON’ –// With “UNIONALL” operator |
(9) After writing the query, then execute the query (Shortcut key is F5) as shown.
(10) After executing the two queries in the same editor, SQL gives two results. Note: Recommended write and execute query one by one for reporting.
