SQL allows us to use set operators within our queries. Set operators combine the results of two or more sub-queries into a single result table. Queries containing set operators are referred to as compound queries.
Set operators in SQL include:
We use the UNION
operator to return a result table that contains all rows from the referenced tables.
UNION
removes all duplicate rows from the result table.
Here’s an example:
SELECT 2 NUM FROM DUAL UNION SELECT 4 FROM DUAL UNION SELECT 3 FROM DUAL UNION SELECT 2 FROM DUAL UNION SELECT 2 FROM DUAL;
In the above example, notice how the result contains no duplicates. Each unique data row from each referenced table becomes part of the result table.
Another variation of the UNION
operator is the UNION ALL
operator.
The UNION ALL
operator also includes duplicate rows in the result table. It is faster than the UNION
operator, since it does not spend time removing duplicates.
Running the same query with UNION ALL
, we get the following:
SELECT 2 NUM FROM DUAL UNION ALL SELECT 4 FROM DUAL UNION ALL SELECT 3 FROM DUAL UNION ALL SELECT 2 FROM DUAL UNION ALL SELECT 2 FROM DUAL;
We use the INTERSECT
operator to return a result table containing only matching rows from the referenced tables.
The result table contains no duplicates. Let’s look at an example:
CREATE TABLE Fruits( Fruit_name varchar(20) PRIMARY KEY ); CREATE TABLE Citrus_Fruits( Fruit_name varchar(20) PRIMARY KEY ); INSERT INTO Fruits VALUES ('Apple'); INSERT INTO Fruits VALUES ('Mango'); INSERT INTO Fruits VALUES ('Orange'); INSERT INTO Fruits VALUES ('Lemon'); INSERT INTO Fruits VALUES ('Pear'); INSERT INTO Fruits VALUES ('Cherry'); INSERT INTO Citrus_Fruits VALUES ('Orange'); INSERT INTO Citrus_Fruits VALUES ('Lemon'); INSERT INTO Citrus_Fruits VALUES ('Lime'); INSERT INTO Citrus_Fruits VALUES ('Grape Fruit'); /*USING INTERSECT OPERATOR*/ SELECT f.Fruit_name FROM Fruits f INTERSECT SELECT c.Fruit_name FROM Citrus_Fruits c;
The above query returns the following table:
Fruit_name |
Lemon |
Orange |
In this example, the result table contains data rows that are common to both the Fruits and Citrus_Fruits tables.
Once again, the result table does not contain duplicates.
Note: The
INTERSECT
operator is not supported by MySQL. However, we can easily simulate this type of query using either theIN
clause or theEXISTS
clause.
We use the MINUS
operator to return a table containing data rows representative of the set difference between the referenced tables.
Put simply, MINUS
returns rows that are present in the first query but absent in the second query, with no duplicates.
The following example shows the behavior of the MINUS
clause:
CREATE TABLE Fruits( Fruit_name varchar(20) PRIMARY KEY ); CREATE TABLE Citrus_Fruits( Fruit_name varchar(20) PRIMARY KEY ); INSERT INTO Fruits VALUES ('Apple'); INSERT INTO Fruits VALUES ('Mango'); INSERT INTO Fruits VALUES ('Orange'); INSERT INTO Fruits VALUES ('Lemon'); INSERT INTO Fruits VALUES ('Pear'); INSERT INTO Fruits VALUES ('Cherry'); INSERT INTO Citrus_Fruits VALUES ('Orange'); INSERT INTO Citrus_Fruits VALUES ('Lemon'); INSERT INTO Citrus_Fruits VALUES ('Lime'); INSERT INTO Citrus_Fruits VALUES ('Grape Fruit'); /*USING MINUS OPERATOR*/ SELECT f.Fruit_name FROM Fruits f MINUS SELECT c.Fruit_name FROM Citrus_Fruits c;
The above query returns the following table:
Fruit_name |
Apple |
Cherry |
Mango |
Pear |
In this example, the result table contains all rows of the Fruits table that are not part of the Citrus_Fruits table.
Note: The
MINUS
operator is replaced by theEXCEPT
clause in the SQL Server.
MySQL does not supportMINUS
/EXCEPT
. However, we can easily simulate this type of query using either theNOT EXISTS
,NULL
, orNOT IN
clause.
RELATED TAGS
CONTRIBUTOR
View all Courses