MariaDB Tutorial MariaDB Advanced MariaDB Database Account Management MariaDB References

MariaDB - INTERSECT Keyword



The MariaDB INTERSECT keyword is used to return the intersection of result-set of two or more 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.
MariaDB INTERSECT

Syntax

The syntax for using INTERSECT keyword in MariaDB is given below:

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

Note: By default, INTERSECT clause only takes distinct values. To select duplicate values, INTERSECT ALL clause can be used. To force this clause to select distinct values INTERSECT DISTINCT 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 query 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 statement 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 query will be:

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

    This will produce the following result:

    NameBrand
    AppleDelicious
    WatermelonLocalFarm

❮ MariaDB Keywords