Set Operations
Set operations in SQL, such as UNION, UNION ALL, INTERSECT, and EXCEPT, are used to combine or compare the result sets of two or more queries. These operations are valuable when working with multiple SELECT statements to retrieve data in different ways.
1. UNION:
The UNION operator combines the result sets of two or more SELECT statements into a single result set, removing duplicate rows. It is often used when you want a distinct list of results from multiple tables or queries.
Syntax:
SELECT column1, column2, ...
FROM table1
UNION
SELECT column1, column2, ...
FROM table2;
Example:
This query selects the union of the name columns from two different tables: Teachers and Students, returning a list of unique names.
SELECT name
FROM Teachers
UNION
SELECT name
FROM Students;
2. UNION ALL:
The UNION ALL operator also combines the result sets of two or more SELECT statements into a single result set but retains all rows, including duplicates. This is useful when you need to preserve all occurrences of data, not just distinct values.
Syntax:
SELECT column1, column2, ...
FROM table1
UNION ALL
SELECT column1, column2, ...
FROM table2;
Example:
SELECT name
FROM Teachers
UNION ALL
SELECT name
FROM Students;
This query returns all names from both Teachers and Students, including duplicates.
3. INTERSECT:
The INTERSECT operator returns the intersection of two result sets, meaning it includes only the rows that appear in both result sets. It is used when you want to find common data between tables or queries.
Syntax:
SELECT column1, column2, ...
FROM table1
INTERSECT
SELECT column1, column2, ...
FROM table2;
Example:
SELECT name
FROM Teachers
INTERSECT
SELECT name
FROM Students;
This query returns the names that are present in both the Teachers and Students tables.
4. EXCEPT:
The EXCEPT operator returns the set difference of two result sets, meaning it includes only the rows that appear in the first result set but not in the second. It is useful for finding data that exists in one table or query but not in another.
Syntax:
SELECT column1, column2, ...
FROM table1
EXCEPT
SELECT column1, column2, ...
FROM table2;
Example:
SELECT name
FROM Teachers
EXCEPT
SELECT name
FROM Students;
This query returns the names that are present in the Teachers table but not in the Students table.
