Set Operations

SET OPERATIONS IN SQL 

In SQL, set operations allow you to combine the results of two or more queries. There are three primary set operations: UNION, INTERSECT, and EXCEPT (or MINUS, depending on the database system). These operations are typically performed on the result sets of two or more SELECT statements.

1. UNION:

The UNION operator is used to combine the results of two or more SELECT statements, removing duplicate rows from the final result set.

SELECT column1, column2
FROM table1
UNION
SELECT column1, column2
FROM table2;

Example:

SELECT product_name FROM category1
UNION
SELECT product_name FROM category2;

2. INTERSECT:

The INTERSECT operator returns the common rows that appear in the result sets of two SELECT statements.

SELECT column1, column2
FROM table1
INTERSECT
SELECT column1, column2
FROM table2;

Example:

SELECT employee_id FROM department1
INTERSECT
SELECT employee_id FROM department2;

3. EXCEPT (or MINUS):

The EXCEPT (or MINUS in some database systems) operator returns the distinct rows present in the result set of the first SELECT statement but not in the result set of the second SELECT statement.

SELECT column1, column2
FROM table1
EXCEPT
SELECT column1, column2
FROM table2;

Example:

SELECT product_name FROM category1
EXCEPT
SELECT product_name FROM category2;

It's important to note that for these set operations to work, the SELECT statements involved must have the same number of columns, and the corresponding columns must have compatible data types.

UNION ALL, INTERSECT ALL, EXCEPT ALL:

If you want to include duplicate rows in the results, you can use UNION ALL, INTERSECT ALL, and EXCEPT ALL. These variations include all rows, even if they are duplicates.

SELECT column1, column2
FROM table1
UNION ALL
SELECT column1, column2
FROM table2;

EXAMPLE 

LET US CONSIDER TWOP TABLES 

COURSE_2024

 

COURSE_2025

 

 

1. UNION COMMAND 

 

The UNION operator is used to combine the results of two or more SELECT statements, removing duplicate rows from the final result set.

 

SELECT CourseName FROM Course_2024

UNION

SELECT CourseName FROM Course_2025;

 

 

 

 

 

 

 

 

2. UNION ALL 

 

The UNION operator is used to combine the results of two or more SELECT statements, including duplicate rows from the final result set.

SELECT CourseName FROM Course_2024

UNION ALL

SELECT CourseName FROM Course_2025;

 

 

 

 

 

 

 

 

 

 

3. INTERSECT COMMAND

 

The INTERSECT operator returns the common rows that appear in the result sets of two SELECT statements.

 

SELECT CourseName FROM Course_2024

INTERSECT

SELECT CourseName FROM Course_2025;

 

 

 

 

4. EXCEPT/ MINUS

 

The EXCEPT (or MINUS in some database systems) operator returns the distinct rows present in the result set of the first SELECT statement but not in the result set of the second SELECT statement.

 

SELECT CourseName FROM Course_2024

EXCEPT

SELECT CourseName FROM Course_2025;