1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Learn to use Union, Intersect, and Except Clauses

Discussion in 'Database' started by Sagar Jaybhay, May 6, 2020.

Tags:
  1. Sagar Jaybhay

    Sagar Jaybhay New Member

    Joined:
    Jan 28, 2019
    Messages:
    29
    Likes Received:
    17
    Trophy Points:
    3
    Gender:
    Male
    Occupation:
    Sr. Software Developer
    Location:
    Pune
    Home Page:
    Intersect operator

    The intersect operator retrieves the common records between the left and right queries of the intersect operator.

    1) This is introduced in SQL Server 2005.
    2) The number of columns and order of columns should be the same.
    3) The data types must be the same or least compatible.
    4) It filters duplicate records and selects only distinct records that are common in the left and right queries. But if you use inner join then it will not filter distinct records.
    5) To use inner join behave like intersect you need to use distinct records

    Code:
    select * from Employee where EmpID between 1 and 100
    intersect
    select * from Employee where EmpID between 1 and 10;
    In this we also get the result using inner join and below is a result

    Code:
    select * from Employee as e
    inner join (select * from Employee where EmpID between 1 and 10) as a on e.EmpID=a.EmpID
    In an inner join, it treats 2 Null values are different and if you join 2 tables on that column on which column has null values then inner join will not include that null value records where intersect treat null as same and return matching records.


    What is the difference between Union, Intersect, and Except operator in SQL Server?

    Union:
    The Union operator will return all unique rows from the left query and right query and union all operator will include duplicate also.

    Intersect :
    The intersect operator will retrieve all unique rows from the left and right queries.

    Except:
    The Except operator will retrieve all the unique rows from the left query that are not present in the right query result set.
     
    shabbir likes this.

Share This Page