SQL Set Operations / SQL Set Operators / UNION, INTERSECT, and EXCEPT or MINUS Set Operators / Set Operators in DBMSs Oracle, MySQL, and DB2 / Set Operators with examples
SQL Set Operations
In SQL we have a set of
operations called Set Operations. Those are UNION, INTERSECT, and MINUS. They
are exactly like the relational-algebra set operations U, ∩,
and -.
We can write a SQL set operation
query using the following general syntax;
(Expression 1) θ (Expression 2),
Where, Expression 1 and 2 can be
any valid SQL query, and θ any one of the set operators U, ∩,
and -.
For example, expression 1 and
expression 2 can be something like,
SELECT * FROM student;
SELECT name, regno FROM student;
SELECT name, regno FROM student
WHERE name like ‘%s’;
In simple words, these
expressions are any of the valid SQL queries. Only requirement is that the
SELECT clause of both the expressions must be same. Read the next paragraph to
know about the basis requirements to perform Set operations.
Example:
Let us use the following relation
schemas for discussing the examples;
UG_Student (Regno:varchar,
Name:varchar, Phone:number)
PG_Student (Regno:varchar,
Name:varchar, Phone:number)
To retrieve both UG and PG
students’ Regno and Phone details, the query will look like Query 1 given below;
(SELECT Regno, Phone
FROM UG_Student) U (SELECT Regno, Phone FROM UG_Student)
----- Query 1
To retrieve the same phone
numbers held by both UG and PG students, the query will look like Query 2 given
below;
(SELECT Phone FROM
UG_Student) ∩ (SELECT Phone FROM PG_Student)
----- Query 2
Set Operation Properties:
To perform set operations, the queries have to satisfy the following conditions;
Condition 1: The Expression1 and Expression2
must specify the same number of attributes in the SELECT clause.
For example, see Query 1. In Query
1, the SELECT clause of expression 1 holds two attributes, Regno and Phone. In expression
2, the SELECT clause have two attributes.
Condition 2: The domain of the
attributes specified in the SELECT clause of both expressions should be in the same
order and the domains of the attributes also must be same.
For example, see Query 1. In
Query 1, the SELECT clause of Expression 1 has two attributes in the order
Regno and Phone. The SELECT clause of Expression 2 too has the same set of
attributes in the same order, i.e, Regno and Phone. And the domain of Regno is
varchar and Phone is numbers which are same in Expression 1 and 2.
Click below for Set operations.
Important Points to Remember
- Number of columns must be same in the expressions
- The domains of attributes and the order in which they represented in all the expressions should be same
- We can use many operators in one query. For example, we can write a query as follows;
- SELECT * FROM a UNION SELECT * FROM b UNION SELECT * FROM c;
- SELECT * FROM a UNION SELECT * FROM b INTERSECT SELECT * FROM c;