Set Operators in SQL Server (UNION, UNION ALL, INTERSECT, EXCEPT)

By Luke Alderton at 28 Aug 2010, 19:24 PM
  • SET operators are mainly used to combine the same type of data from two or more tables. Although more than one select statement will then be present, only one result set is returned.

    Rules on Set Operations:

    • The result sets of all queries must have the same number of columns.
    • In every result set the data type of each column must match the data type of its corresponding column in the first result set.
    • In order to sort the result, an ORDER BY clause should be part of the last statement.
    • The records from the top query must match the positional ordering of the records from the bottom query.
    • The column names or aliases must be found out by the first select statement.

    Four Set Operators:

    The four set operators union, union all, intersect and except allow us to serially combine two or more select statements.

    OperatorReturns
    UNIONCombine two or more result sets into a single set, without duplicates.
    UNION ALLCombine two or more result sets into a single set, including all duplicates.
    INTERSECTTakes the data from both result sets which are in common.
    EXCEPTTakes the data from first result set, but not the second (i.e. no matching to each other)

    SYNTAX

    For set operators, the syntax is simple.

    1. SELECT [Column_Name, . . . ] FROM [table1] [set operator]
    2. SELECT [Column_Namse, . . .] FROM [table2] [set operator]
    3. ...
    4. ...
    5. SELECT [Column_Name, . . . ] FROM [tableN]

    Example

    Create two tables with same column name and data type.

    1. CREATETABLE Students2000(
    2. NameVARCHAR(15),
    3. TotalMark INT)
    4. CREATETABLE Stundents2005(
    5. NameVARCHAR(15),
    6. TotalMark INT)

    Let us insert a few values into the tables.

    1. INSERTINTO Students2000 VALUES('Robert',1063);
    2. INSERTINTO Students2000 VALUES('John',1070);
    3. INSERTINTO Students2000 VALUES('Rose',1032);
    4. INSERTINTO Students2000 VALUES('Abel',1002);
    5. INSERTINTO Students2005 VALUES('Robert',1063);
    6. INSERTINTO Students2005 VALUES('Rose',1032);
    7. INSERTINTO Students2005 VALUES('Boss',1086);
    8. INSERTINTO Students2005 VALUES('Marry',1034);

    Result

    Result Set for Students2000 table

    undefined

    Result Set for Students2005 table

    undefined

    UNION ALL

    The SQL UNION ALL Operator is used to list all records from two or more select statements. All the records from both tables must be in the same order.

    1. SELECTName,TotalMarks FROM students2000 UNIONALL
    2. SELECTName,TotalMarks FROM students2005

    Result

    undefined


    Here Robert and Rose are stored in both tables. UNION ALL retuns all records (including duplicate records).

    UNION

    The SQL Union ALL Operator is used to combine two tables using select statement when both tables have the same number of columns.

    Union works like Distinct. Union all DOES NOT do distinct.

    1. SELECTName,TotalMarks FROM students2000 UNION
    2. SELECTName,TotalMarks FROM students2005

    Result

    undefined

    The Robert and Rose records are duplicate records. Thus, these are returned only once.

    INTERSECT

    INTERSECT returns any distinct values that are returned by both the query on the left and right sides of the INTERSECT operand.

    1. SELECTName,TotalMarks FROM students2000 INTERSECT
    2. SELECTName,TotalMarks FROM students2005

    Result

    undefined

    Only the Robert and Rose records are returned, because they are found in both tables.

    EXCEPT

    EXCEPT clause in SQL Server is working as like MINUS operation in Oracle. EXCEPT query returns all rows which are in the first query but those are not returned in the second query.

    Example 1

    1. SELECTName,TotalMarks FROM students2000 EXCEPT
    2. SELECTName,TotalMarks FROM students2005

    undefined

    Example 2

    EXCEPT returns any distinct values from the left select query that are not also found on the right select query.

    1. SELECTName,TotalMarks FROM students2005 EXCEPT
    2. SELECTName,TotalMarks FROM students2000

    undefined

    From the two results we understand that if any records are found in both tables, they are removed from the first table's record set.

    The four set operators (union, union all, intersect and except) in SQL all have the same precedence.


    Comments

     

    Post a comment

    Please correct the following: