Combinations Combining Query Results in SQL

Combinations Combining Query Results in SQL

UNION

Functionality

The UNION operator combines the results of two or more SELECT statements into a single result set, removing duplicate rows. It effectively merges data from multiple queries into a unified set of results.

Syntax 

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

Key Points

• Column Compatibility: Each SELECT statement must have the same number of columns, and the columns must be of compatible data types.
• Sorting: Use ORDER BY at the end of the combined query to sort the final result set.
• Performance: Can be slower if there are many duplicates to remove, as it requires sorting and deduplication.

Example 

SELECT Name, Department
FROM Employees2023
UNION
SELECT Name, Department
FROM Employees2024;

This query combines employee names and departments from both 2023 and 2024, removing any duplicates.

UNION ALL

Functionality

The UNION ALL operator combines the results of two or more SELECT statements into a single result set, including all duplicates. It does not remove duplicate rows.

Syntax 

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

Key Points

• Column Compatibility: Same as UNION, columns must match in number and data type.
• Performance: Generally faster than UNION because it does not perform duplicate elimination.
• Use Case: Useful when you need to preserve duplicate rows or when you know there are no duplicates to remove.

Example 

SELECT Name, Department
FROM Employees2023
UNION ALL
SELECT Name, Department
FROM Employees2024;

This query combines employee names and departments from both 2023 and 2024, including duplicates.

INTERSECT

Functionality

The INTERSECT operator returns only the rows that are present in the result sets of both SELECT queries. It finds the common rows between two or more result sets.

Syntax 

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

Key Points

• Column Compatibility: Columns must be the same in number and compatible in type across all SELECT statements.
• Performance: Can be resource-intensive for large datasets, as it involves comparison operations.
• Use Case: Useful for finding common data between different datasets.

Example 

SELECT Name, Department
FROM Employees2023
INTERSECT
SELECT Name, Department
FROM Employees2024;

This query finds the employees who are present in both 2023 and 2024.

MINUS (or EXCEPT in Some Databases)

Functionality

The MINUS operator (or EXCEPT in some databases like SQL Server) returns the rows that are in the result set of the first SELECT query but not in the result set of the second SELECT query. It effectively subtracts one result set from another.

Syntax 

SELECT column1, column2, ...
FROM table1
MINUS
SELECT column1, column2, ...
FROM table2;

Key Points

• Column Compatibility: Columns must match in number and data type.
• Performance: Can be costly in terms of performance for large datasets due to the need for comparison operations.
• Use Case: Useful for finding records that are present in one dataset but missing in another.

Example 

SELECT Name, Department
FROM Employees2023
MINUS
SELECT Name, Department
FROM Employees2024;

This query identifies employees who were present in 2023 but are not in 2024.

Combining Results with Sorting and Filtering

You can also use these operators in conjunction with ORDER BY and WHERE clauses to further manipulate the result sets:

Sorting

Example with UNION and Sorting: 

SELECT Name, Department
FROM Employees2023
UNION
SELECT Name, Department
FROM Employees2024
ORDER BY Name;

This query combines results from both years and sorts the final list by Name.

Filtering

Example with INTERSECT and Filtering: 

SELECT Name, Department
FROM Employees2023
WHERE Department = 'IT'
INTERSECT
SELECT Name, Department
FROM Employees2024
WHERE Department = 'IT';

This query finds employees in the ‘IT’ department who are present in both years.

Common Pitfalls and Tips

• Column Data Types: Ensure columns are compatible in type and order when using these operators.
• Performance: Be aware of potential performance issues, especially with large datasets. Indexing and query optimization can help.
• Database Compatibility: Note that not all SQL databases support all these operators. For example, MINUS is specific to Oracle, while EXCEPT is used in SQL Server and PostgreSQL.

Practical Examples

Example Combining Multiple Queries with UNION ALL and Sorting: 

SELECT ProductName, Price
FROM Products2023
UNION ALL
SELECT ProductName, Price
FROM Products2024
ORDER BY Price DESC;

This query combines product data from two years and sorts the results by price in descending order.

Example Using INTERSECT to Find Common Customers: 

SELECT CustomerID, CustomerName
FROM Customers2023
INTERSECT
SELECT CustomerID, CustomerName
FROM Customers2024;

This query finds customers who are present in both 2023 and 2024.

Example Using MINUS to Find New Products: 

SELECT ProductID, ProductName
FROM Products2024
MINUS
SELECT ProductID, ProductName
FROM Products2023;