Intersect and Except Operator in SQL

Intersect and Except Operator in SQL

In SQL, the INTERSECT and EXCEPT operators are used to perform set operations between two queries. These operators are typically used in conjunction with the SELECT statement to retrieve data from tables based on certain conditions. Let's take a closer look at each operator:

INTERSECT Operator:

• Think of it like finding common elements.
• If you have two sets (like lists of things), INTERSECT helps you find the items that are present in both sets.
• In SQL, this means you get rows that match between two queries.
• INTERSECT is used to retrieve common rows that exist in two SELECT statements.
• It returns only the rows that are common to both result sets of the SELECT statements.

Let's consider a scenario where we have two tables, "employees" and "managers," both containing information about employees, and we want to find the common employees who are both employees and managers. The common employees are those who exist in both tables.

-- Create sample tables CREATE TABLE employees ( employee_id INT PRIMARY KEY, employee_name VARCHAR(50) ); CREATE TABLE managers ( manager_id INT PRIMARY KEY, manager_name VARCHAR(50) ); -- Insert sample data INSERT INTO employees (employee_id, employee_name) VALUES (1, 'John Doe'), (2, 'Jane Smith'), (3, 'Bob Jones'); INSERT INTO managers (manager_id, manager_name) VALUES (2, 'Jane Smith'), (3, 'Bob Jones'), (4, 'Alice Johnson'); -- Find common employees who are both employees and managers SELECT employee_id, employee_name FROM employees INTERSECT SELECT manager_id, manager_name FROM managers;

In this example:

• The employees table represents regular employees.
• The managers table represents employees who are also managers.

The INTERSECT operator is used to find employees who exist in both tables. The result would be the common employees who are both regular employees and managers. In this case, the result would include Jane Smith and Bob Jones because they are present in both tables.

In SQL, when using the INTERSECT operator, the columns selected in both queries must have the same names and data types.

EXCEPT Operator:

• Think of it like finding the unique items.
• If you have two sets, EXCEPT helps you find the items that are in the first set but not in the second set.
• In SQL, this means you get rows from the first query that are not present in the second query.
• EXCEPT is used to retrieve rows from the result set of the first SELECT statement that do not appear in the result set of the second SELECT statement.
• It returns the distinct rows from the first SELECT statement that are not present in the second SELECT statement.

Let's consider a scenario where we have two tables, "developers" and "managers," both containing information about employees, and we want to find the developers who are not managers. The EXCEPT operator can be used to achieve this.

-- Create sample tables CREATE TABLE developers ( employee_id INT PRIMARY KEY, employee_name VARCHAR(50) ); CREATE TABLE managers ( employee_id INT PRIMARY KEY, manager_name VARCHAR(50) ); -- Insert sample data INSERT INTO developers (employee_id, employee_name) VALUES (1, 'John Doe'), (2, 'Jane Smith'), (3, 'Bob Jones'); INSERT INTO managers (employee_id, manager_name) VALUES (2, 'Jane Smith'), (3, 'Bob Jones'), (4, 'Alice Johnson'); -- Find developers who are not managers SELECT employee_id, employee_name FROM developers EXCEPT SELECT employee_id, manager_name FROM managers;

In this example:

• The developers table represents employees who are developers.
• The managers table represents employees who are managers.

The EXCEPT operator is used to find employees who are in the developers table but not in the managers table. The result would be the developers who are not also managers. In this case, the result would include John Doe, as he is a developer but not a manager.

• The INTERSECT operator finds common elements, while the EXCEPT operator finds unique elements in the first set.
• You can use these operators to compare and analyze data in different ways, just like comparing sets of items.

NOTE:-

• JOIN is used to combine rows based on related columns.
• INTERSECT is used to retrieve common rows between two result sets.
• EXCEPT is used to retrieve distinct rows from the first result set that are not present in the second result set.