SQL | Intersect & Except clause

Both INTERSECT and EXCEPT are set operators used in SQL to combine or compare the result sets of two or more queries. These operators treat each result set as a set of rows, hence the "set operators" term.

INTERSECT:
This operator returns only the rows that are common between the two result sets.
For example, consider two tables: table_A and table_B, both having a single column named number. If you want to find numbers that are present in both tables, you can use:
```
SELECT number FROM table_A
INTERSECT
SELECT number FROM table_B;
```
Only the rows (numbers) that are common in both table_A and table_B will be returned.
EXCEPT (known as MINUS in some databases like Oracle):
This operator returns the rows from the first result set that aren��t present in the second result set.
Using the same example with table_A and table_B, if you want to find numbers that are in table_A but not in table_B, you can use:
```
SELECT number FROM table_A
EXCEPT
SELECT number FROM table_B;
```
Only the rows (numbers) that are present in table_A and not in table_B will be returned.

Few things to note:

The number and order of columns in all participating SELECT statements must be the same.
The data types of the corresponding columns in the SELECT statements should be compatible.
Duplicate rows are automatically eliminated (like using a DISTINCT on a result set).
Both operators are guided by the order of the SELECT statements. That is, with EXCEPT, rows from the first query are returned that aren't in the result set of the second query.

When using these operators, ensure that the SELECT statements involved are structured correctly to avoid unexpected results.

Using INTERSECT in SQL with examples:
- Description: The INTERSECT operator in SQL is used to retrieve the common rows between two SELECT statements. It returns distinct rows that are present in both result sets.
- Example Code:
```
SELECT column1, column2 FROM table1
INTERSECT
SELECT column1, column2 FROM table2;
```
Comparing INTERSECT and EXCEPT in SQL queries:
- Description: INTERSECT and EXCEPT are both set operators in SQL. While INTERSECT returns common rows, EXCEPT returns distinct rows from the first result set that are not present in the second result set.
- Example Code:
```
-- INTERSECT Example
SELECT column1, column2 FROM table1
INTERSECT
SELECT column1, column2 FROM table2;

-- EXCEPT Example
SELECT column1, column2 FROM table1
EXCEPT
SELECT column1, column2 FROM table2;
```
How to use INTERSECT and EXCEPT in SQL:
- Description: To use INTERSECT and EXCEPT, you need to structure two SELECT statements with matching columns. INTERSECT retrieves common rows, while EXCEPT retrieves rows unique to the first result set.
- Example Code:
```
-- INTERSECT Example
SELECT column1, column2 FROM table1
INTERSECT
SELECT column1, column2 FROM table2;

-- EXCEPT Example
SELECT column1, column2 FROM table1
EXCEPT
SELECT column1, column2 FROM table2;
```