Set operators in SQL

The set operators are used to combine the results of two or more compound queries into a single result. Queries containing set operators are compound queries.

The different types of operators are:

  • UNION
  • UNION ALL
  • INTERSECT
  • MINUS

The source tables used in this article are table A1 and table A2.

Table A1 :

SELECT * FROM A1;

set-operator-table-first-sql

Table A2 :

SELECT * FROM A2;

set-operator-table-second-sql

Guidelines for the set operator:
  • The number of columns that are selected must be the same in both the tables. For example, if the first query is having two columns, then the second query must have two columns. If not, the error occurs "query block has an incorrect number of result columns"
  • The datatypes of the columns which are selected must be the same. Otherwise, we may get "ORA-01790: expression must have the same datatype as the corresponding expression"
  • The name of the columns need not be identical.
  • During the duplicate data checking, the NULL values are not ignored.
  • The output is sorted into ascending order of the columns.

UNION operator in SQL

When two or more queries are joined using the UNION operator, the result is combined from all the compounded queries and removes all duplicates.

In the UNION operator, the two tables are used. In this operator, the data of the first table are compared to the data of the second table. If the operator finds any row having the same value in both the tables, the UNION operator removes the repeated or duplicate data from the second table.

unionioperator-output-sql

Example for the UNION operator :

SELECT * FROM A1
UNION
SELECT * FROM A2;

This query gives the output by combining both the tables and removes the repeated data.
union-operator-sql

UNION ALL operator in SQL

When two or more queries are joined using the UNION ALL operator, the result is combined from all the tables including all duplicates.

In the UNION ALL operators, the two tables A1 and A2 are used. In this operator, all the data are given as output including the repeated data or duplicate data.
union-all-operator-output-sql

Example for the UNION ALL operator :

SELECT * FROM A1
UNION ALL
SELECT * FROM A2;

This query gives the output by combining both the tables and includes repeated data.
union-all-operatortable-sql

INTERSECT operator in SQL

In the INTERSECT operator, the two tables A1 and A2 are compared and the common data of the two tables are given as the output.

intersect-operator-output-sql

Example for the INTERSECT operator :

SELECT * FROM A1
INTERSECT
SELECT * FROM A2;

intersect-operator-sql

MINUS operator in SQL

The MINUS operator compares the two tables. The first table data which are not identical to the second table data are given as output from the first table.
minus-operator-output-sql

Example for the MINUS operator :

SELECT * FROM A1
MINUS
SELECT * FROM A2;

minus-operator-sql

0 results
Comment / Suggestion Section
Point our Mistakes and Post Your Suggestions