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 (–)
The UNION operator

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;
The INTERSECT operator

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 INTERSECT operator is not supported by MySQL. However, we can easily simulate this type of query using either the IN clause or the EXISTS clause.

The MINUS operator

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 MINUS operator is replaced by the EXCEPT clause in the SQL Server.

MySQL does not support MINUS / EXCEPT. However, we can easily simulate this type of query using either the NOT EXISTS, NULL, or NOT IN clause.

Free Resources

Copyright ©2026 Educative, Inc. All rights reserved