SQL Tutorial SQL Advanced SQL Database SQL References

SQL - INTERSECT Keyword



The SQL INTERSECT keyword is used to return the intersection of result-set of two or more SQL SELECT statements. If a record exists in both result-sets, it will be included in the INTERSECT results. However, if a record exists in one result-set and not in the other, it will be removed from the INTERSECT results.

While using INTERSECT keyword, the following criteria must be satisfied:

  • Number of columns in each SELECT statement should equal.
  • Datatype of columns in each SELECT statement should match.
  • Order of columns in each SELECT statement should match.
SQL INTERSECT

Syntax

The syntax for using INTERSECT keyword is given below:

SELECT column1, column2, ... FROM table1
INTERSECT [ALL]
SELECT column1, column2, ... FROM table2

Note: By default, INTERSECT clause only takes distinct values. To select duplicate values, INTERSECT ALL clause can be used.

Example:

Consider database tables called Product and Inventory with the following records:

Table 1: Product table

NameBrandPriceQuantity
AppleDelicious1.540
AppleHarvest1.650
OrangeDelicious2.040
OrangeHarvest1.950
WatermelonLocalFarm4.050
MangoLocalFarm3.560

Table 2: Inventory table

NameBrandPriceQuantity
AppleDelicious1.520
OrangeLocalFarm2.040
WatermelonLocalFarm4.020

  • Using INTERSECT keyword: To get the records of Name column which are present in both record-sets, the following SQL code can be used:

    SELECT Name FROM Product
    INTERSECT
    SELECT Name FROM Inventory
    ORDER BY Name;
    

    This will produce the result as shown below:

    Name
    Apple
    Orange
    Watermelon
  • Using INTERSECT keyword with WHERE clause: To fetch the records of Name column which are present in both record-sets with the specified condition, the following SQL code can be used:

    SELECT Name FROM Product
    INTERSECT
    SELECT Name FROM Inventory
    WHERE Quantity < 30
    ORDER BY Name;
    

    This will produce the following result:

    Name
    Apple
    Watermelon
  • Using INTERSECT keyword with multiple columns: To fetch the records of Name and Brand columns which are present in both record-sets, the following SQL code can be used:

    SELECT Name, Brand FROM Product
    INTERSECT
    SELECT Name, Brand FROM Inventory
    ORDER BY Name;
    

    This will produce the following result:

    NameBrand
    AppleDelicious
    WatermelonLocalFarm

❮ SQL Keywords