SQL Tutorial

✨ Set Operations in SQL

In SQL, Set Operations allow us to combine the results of two or more queries. The most common set operations are:

• UNION: Combines the result sets of two queries, removing duplicates.
• UNION ALL: Combines the result sets of two queries, including duplicates.
• INTERSECT: Returns only the common rows between two queries.
• EXCEPT: Returns rows from the first query that do not exist in the second query.

1️⃣ UNION - Combine Results

The UNION operation combines the results of two queries but removes any duplicate rows from the final result.

SELECT employee_name FROM employees WHERE department = 'Sales'
UNION
SELECT employee_name FROM employees WHERE department = 'Marketing';

This query combines the employees from the Sales and Marketing departments without showing duplicates.

2️⃣ UNION ALL - Combine Results with Duplicates

The UNION ALL operation is similar to UNION, but it does not remove duplicates, so all results are returned, including repeated rows.

SELECT employee_name FROM employees WHERE department = 'Sales'
UNION ALL
SELECT employee_name FROM employees WHERE department = 'Marketing';

This query returns all employee names from both departments, even if some employees appear in both departments.

3️⃣ INTERSECT - Common Results

The INTERSECT operation returns the common rows from two queries. It essentially finds the intersection of two result sets.

SELECT employee_name FROM employees WHERE department = 'Sales'
INTERSECT
SELECT employee_name FROM employees WHERE department = 'Marketing';

This query returns only the employees who appear in both the Sales and Marketing departments.

4️⃣ EXCEPT - Subtract Results

The EXCEPT operation returns the rows from the first query that are not in the second query. It’s like subtracting the second result set from the first.

SELECT employee_name FROM employees WHERE department = 'Sales'
EXCEPT
SELECT employee_name FROM employees WHERE department = 'Marketing';

This query returns employees who are in the Sales department but not in the Marketing department.

🔧 Key Considerations

• Column Data Type Compatibility: All queries involved in a set operation must return the same number of columns with compatible data types.
• Order of Results: Set operations do not guarantee any specific order unless you explicitly use an ORDER BY clause.
• Performance: Using UNION ALL may be faster than UNION because it does not need to check for duplicates.