Relational Set Operators

<aside> 👉 Run the SQL file

InvoicesDBINIT.sql

</aside>

Relational Set Operators use relational algebra to manipulate contents in a database. The major relational set operators are union, intersection, and set difference.

1) Union

The UNION operator combines the resulting rows from two or more queries without including duplicate rows.

→ Each SELECT statement within UNION must have the same number of columns

→ The columns must also have similar data types

→ The columns in each SELECT statement must also be in the same order

Syntax:

SELECT *select_expr,...* FROM *tbl_name* [WHERE *where_condition*] 
UNION [ALL]
SELECT *select_expr,...* FROM *tbl_name* [WHERE *where_condition*] [ORDER BY *order_expr,...*]; 	****

• Try the following query to compile a comprehensive list of contacts with phone numbers from customers and vendors: ****

SELECT lastName, areaCode, phone FROM Customer UNION SELECT contactPerson, areaCode, Phone FROM Vendor;

• UNION ALL combines the results of two queries without removing duplicates if any. Try the following query**,** where some names may be printed more than once:

SELECT lastName FROM Customer
UNION ALL
SELECT contactPerson FROM Vendor;

• Compare to the same query without ALL.

2) Intersection

The INTERSECT operator returns the common rows two query result sets.

→ There must be the same number of expressions in both SELECT statements.

→ The corresponding expressions must have the same data type in the SELECT statements