SQL Basics | DML: Data Manipulation Language | INTERSECT
INTERSECT is similar to UNION and is used to process the results of two SQL statements. The difference is that UNION is basically like OR: if a value exists in the first statement or the second statement, it is selected and output. INTERSECT is closer to AND: a value is selected only when it exists in both the first and second statements. UNION is a union, while INTERSECT is an intersection.
INTERSECT syntax
The INTERSECT command is as follows.
[SQL statement 1]
INTERSECT
[SQL statement 2];
INTERSECT example
Suppose you have the following two tables.
store_information table
| store_name | sales | txn_date |
|---|---|---|
| Los Angeles | 1500 | Jan-05-2018 |
| San Diego | 250 | Jan-07-2018 |
| Los Angeles | 300 | Jan-08-2018 |
| Boston | 700 | Jan-08-2018 |
internet_sales table
| txn_date | sales |
|---|---|
| Jan-07-2018 | 250 |
| Jan-10-2018 | 535 |
| Jan-11-2018 | 320 |
| Jan-12-2018 | 750 |
To check which dates have both store sales and internet sales, use the following SQL statement.
SELECT txn_date FROM store_information
INTERSECT
SELECT txn_date FROM internet_sales;
The SQL execution result is as follows.
| txn_date |
|---|
| Jan-07-2018 |
Note that the INTERSECT command displays each distinct value only once.