How to use set operators in SQL
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:
- UNION (∪)
- INTERSECT (∩)
- MINUS (–)
Union
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;
Intersect
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
INTERSECToperator is not supported by MySQL. However, we can easily simulate this type of query using either theINclause or theEXISTSclause.
Minus
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
MINUSoperator is replaced by theEXCEPTclause in the SQL Server.
MySQL does not supportMINUS/EXCEPT. However, we can easily simulate this type of query using either theNOT EXISTS,NULL, orNOT INclause.
RELATED TAGS
CONTRIBUTOR
View all Courses
