UNION, UNION ALL, EXCEPT, INTERSECT

LAB SCRIPT:

UNION:
 UNION puts multiple records into one stack.
 UNION always selects the DISTINCT records.
 Each query must have the same structure, same number of fields with compatible data types.
 The column must be appear in the same order in all the queries.
 Field names are optional as long as all the queries have same structure. If needed then field alias at the first query will be able to show the field names.
Maximum 256 tables can be applied to this operator.
 ORDER by clause can be specified at the bottom of the query and this sorts the records on basis of the first query.

FirstName LastName Position PhoneNumber EmailAddress
Aditya Potdar Sales Representative 517-555-0117 a.Potdar@sqlindia.com
Hardik Vadher General Manager 330-555-0120 h.vadher@sqlindia.com
Sanjay Prajapati Research and Development Manager 330-555-2568 ss.prajapati@sqlindia.com
Saurabh Savaliya Senior Tool Designer 612-555-0100 s.savaliya@sqlindia.com
Savan Prajapati Facilty Supervisor 954-838-2899 s.prajapati@sqlindia.com

UNION ALL:
 UNION ALL and UNION shares the same rules and properties except one, i.e UNION ALL fetches all records including duplicates where in UNION fetches only DISTINCT values.

FirstName LastName Position PhoneNumber EmailAddress
Aditya Potdar Sales Representative 517-555-0117 a.Potdar@sqlindia.com
Hardik Vadher General Manager 330-555-0120 h.vadher@sqlindia.com
Sanjay Prajapati Research and Development Manager 330-555-2568 ss.prajapati@sqlindia.com
Savan Prajapati Facilty Supervisor 954-838-2899 s.prajapati@sqlindia.com
Aditya Potdar Sales Representative 517-555-0117 a.Potdar@sqlindia.com
Saurabh Savaliya Senior Tool Designer 612-555-0100 s.savaliya@sqlindia.com
Saurabh Savaliya Senior Tool Designer 612-555-0100 s.savaliya@sqlindia.com

EXCEPT:
 Most people refer this operator as [First Query] – [Second Query].
 It subtracts every record in your second query from your first query.
 All queries must have same number of columns in the select list with compatible data types.
 The fields in all queries must appear in the same order.

FirstName LastName Position PhoneNumber EmailAddress
Saurabh Savaliya Senior Tool Designer 612-555-0100 s.savaliya@sqlindia.com

INTERSECT:
 This operator behaves like INNER JOIN between two tables.
 INTERSECT finds all records that tow tables have in common.
 Like EXCEPT operator, all queries must have same number of columns in the select list with compatible data types and all queries must appear in the same order too.

FirstName LastName Position PhoneNumber EmailAddress
Aditya Potdar Sales Representative 517-555-0117 a.Potdar@sqlindia.com

Prasad Sahoo

Prasad Sahoo, is an enthusiast of Microsoft technologies in general and a passionate database professional, mainly focusing on SQL Server performance tuning and business intelligence. He has contributed a long time of his career working on SQL Server and other RDBMS. If he is not busy with SQL stuff then he must be watching some Hollywood movies.

Leave a Reply

%d bloggers like this: