MINUS Set Operator

Under construction...

SQL Set Operations

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.

UNION Set Operator

INTERSECT Set Operator

MINUS Set Operator

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;