Set operators

Introduction to Oracle SQL

Sara Billen

Curriculum Manager

What are set operators?

$$

Set operators take the output of two or more SELECT queries and combine them into one result.

$$

  • Join clauses combines tables
    • Column-oriented
  • Set operators combines queries
    • Row-oriented
Introduction to Oracle SQL

Types of set operators

Union

Union operator

All rows with no duplicates

Union All

Union all operator

All rows with duplicates

Intersect

Intersect operator

Rows outputted by both queries

Minus

Minus operator

Distinct rows in 1st query that are not in the 2nd

Introduction to Oracle SQL

Union

All rows with no duplicates

What are all the cities associated with our clients? 

SELECT City FROM Customer
UNION
SELECT BillingCity FROM Invoice

| City           |
|----------------|
| Lyon           |
| Fort Worth     |
| Vienne         |
| Brussels       |
| Orlando        |
| Copenhagen     |
| Oslo           |
| Rio de Janeiro |
| Boston         |
| ...            |
Introduction to Oracle SQL

Union all

All rows with duplicates

What are all the cities associated with our clients and with what frequency? 

SELECT City from Customer
UNION ALL
SELECT BillingCity from Invoice

| City          |
|---------------|
| Oslo          |
| Prague        |
| Prague        |
| Vienee        |
| Brussels      |
| Copenhagen    |
| Mountain View |
| Mountain View |
| Mountain View |
| ...           |
Introduction to Oracle SQL

Intersect

Rows outputted by both queries

Which tracks by Miles Davis are in a playlist?

(SELECT TrackId from PlaylistTrack)
INTERSECT
(SELECT TrackId from Track
WHERE Composer = 'Miles Davis')

| TrackId |
|---------|
| 612     |
| 600     |
| 614     |
| 604     |
| 605     |
| 598     |
| 617     |
Introduction to Oracle SQL

Minus

Distinct rows in 1st query that aren't in the 2nd query

Who are artists that don't compose music?

(SELECT Name FROM Artist)
MINUS
(SELECT Composer FROM Track)
ORDER BY 1 DESC

| Name                     |
|--------------------------|
| Zeca Pagodinho           |
| Youssou N'Dour           |
| Yo-Yo Ma                 |
| Yehudi Menuhin           |
| Xis                      |
| Wilhelm Kempff           |
| Whitesnake               |
| Vinícius E Qurteto Em Cy |
| Vinícius E Odette Lara   |
| ...                      |                                                                       |
Introduction to Oracle SQL

Let's practice!

Introduction to Oracle SQL

Preparing Video For Download...