SQL INTERSECT and MINUS operators
SQL (Structured Query Language) is a powerful tool used by developers to interact with relational databases. Its ability to extract data using various operators has made it an essential tool in database management.
This article will focus on SQL INTERSECT and MINUS operators, explaining their definitions, differences, syntax, and rules, and providing practical examples to aid understanding.
Definition and Explanation of INTERSECT and MINUS operators
INTERSECT and MINUS are set operators used in SQL to extract data from two or more tables. These operators can retrieve data from tables that have the same structure and columns.
The INTERSECT operator, as the name implies, returns rows that exist in both tables. As such, it looks for similarities between the tables and returns them as a result set.
For instance, if you are looking for customers who have bought both a laptop and a desktop, you can use the INTERSECT operator to find the customers who exist in both tables. On the other hand, the MINUS operator retrieves rows that are present in the first table but are not present in the second table. It looks for differences between the two tables and returns the non-matching row(s).
For instance, if you have two tables, one containing sales made by salespersons in the East region, and another by those in the West region, you can use the MINUS operator to retrieve the sales that were made by the East region and not in the West region.
Difference between the INTERSECT and MINUS operators
The major difference between the INTERSECT and MINUS operators is the results they return. The INTERSECT operator returns only the matching rows, while the MINUS operator returns only the non-matching ones. Another difference is that INTERSECT requires the tables to have the same columns and data types, while MINUS does not require the columns to be the same.
Syntax and Rules for INTERSECT and MINUS
The syntax for using INTERSECT and MINUS operators is as follows:
SELECT column_list FROM table1
INTERSECT/MINUS
SELECT column_list FROM table2;
The column_list is the name of the columns you want to extract from each table. The table1 and table2 are the two tables that you want to use INTERSECT/MINUS on. It is essential to note that the two tables must have the same number of columns, and the column data types must be compatible.
Also, the ORDER BY clause can be added to sort the results in ascending or descending order.
Practical Examples of INTERSECT and MINUS
Example 1: INTERSECT
Suppose you have two tables, one with a list of customers who have bought a laptop, and the other with customers who have bought a desktop. You can use the INTERSECT operator to obtain the customers who have made purchases in both categories. Table Laptops:
ID Customer
1 John
2 Jane
3 Alex
Table Desktops:
ID Customer
1 John
2 Jane
4 Mark
Query:
SELECT Customer FROM Laptops
INTERSECT
SELECT Customer FROM Desktops;
Result:
Customer
John
Jane
Example 2: MINUS
Suppose you have two tables, one with a list of sales made in the East region, and the other with sales made in the West region. You can use the MINUS operator to obtain the sales made in the East region, but not in the West region. Table East Region:
ID Salesperson Amount
1 John 2000
2 Jane 1500
3 Alex 1200
Table West Region:
ID Salesperson Amount
1 Mark 2500
2 Jane 1700
3 Alex 1400
Query:
SELECT Salesperson, Amount FROM East Region
MINUS
SELECT Salesperson, Amount FROM West Region;
Result:
Salesperson Amount
John 2000
Conclusion
In conclusion, SQL INTERSECT and MINUS operators are powerful tools that developers can use to extract data from two or more tables. To use these operators, the tables must have the same number of columns, and their data types must be compatible.
The INTERSECT operator returns only the matching rows, while the MINUS operator returns only the non-matching rows. By using the examples provided above, you can better understand how to use these operators in a practical setting.
SQL MINUS
The SQL MINUS operator retrieves rows from the first table that are not present or matched in the second table. It is also known as a set difference operation since it subtracts one set from another.
Thus, the resulting rows are those that exist only in the first table. The MINUS operation requires the tables to have the same number of fields and similar data types. If there is a mismatch in the number of fields or data types, the database returns an error.
In some SQL dialects, the EXCEPT operator is used instead of MINUS.
Example with Diagram
Consider two tables, Sales and Returns, that need to be compared to identify orders that were not returned:
Table: Sales
| Order_ID | Date | Total |
|----------|------------|-------|
| 1001 | 01/01/2021 | 200 |
| 1002 | 01/02/2021 | 300 |
| 1003 | 01/03/2021 | 450 |
Table: Returns
| Order_ID | Date | Total |
|----------|------------|-------|
| 1002 | 01/03/2021 | 150 |
| 1003 | 01/04/2021 | 225 |
| 1004 | 01/05/2021 | 100 |
Query:
SELECT Order_ID, Date, Total FROM Sales
MINUS
SELECT Order_ID, Date, Total FROM Returns;
Result:
| Order_ID | Date | Total |
|----------|-------------|-------|
| 1001 | 01/01/2021 | 200 |
Practical Examples
Scenario 1: One table is the subset of the other table’s data
Suppose you have two tables, Sales and Order_details, with the following data:
Table: Sales
| Order_ID | Date | Total |
|----------|------------|-------|
| 1001 | 01/01/2021 | 200 |
| 1002 | 01/02/2021 | 300 |
Table: Order_details
| Order_ID | Product | Quantity |
|----------|-----------|----------|
| 1001 | Pencil | 10 |
| 1001 | Pen | 5 |
| 1002 | Notebook | 2 |
Query:
SELECT Order_ID, Date, Total FROM Sales
MINUS
SELECT Order_ID, ' ', 0 FROM Order_details;
Result:
| Order_ID | Date | Total |
|----------|-------------|-------|
| 1002 | 01/02/2021 | 300 |
Explanation:
The query subtracts the Order_ID and Sale rows in Sales that have exact matches in the Order_details table. Since the Order_ID 1001 exists in the Order_details table, it is not part of the result.
Scenario 2: Both tables have the same data
Consider two tables, Orders and Order_copy, with the same data:
Table: Orders
| Order_ID | Customer | Amount |
|----------|-----------|--------|
| 1001 | John | 200 |
| 1002 | Jane | 300 |
Table: Order_copy
| Order_ID | Customer | Amount |
|----------|-----------|--------|
| 1001 | John | 200 |
| 1002 | Jane | 300 |
Query:
SELECT Order_ID, Customer, Amount FROM Orders
MINUS
SELECT Order_ID, Customer, Amount FROM Order_copy;
Result:
(empty result set)
Explanation:
Since both tables have the same data, the database returns no rows. There are no differences to subtract using the MINUS operation. Scenario 3: One table in your query contains no data
Consider two tables, Sales and Returns, where Sales table has no data:
Table: Sales
(empty table)
Table: Returns
| Order_ID | Date | Total |
|----------|------------|-------|
| 1002 | 01/03/2021 | 150 |
| 1003 | 01/04/2021 | 225 |
| 1004 | 01/05/2021 | 100 |
Query:
SELECT Order_ID, Date, Total FROM Sales
MINUS
SELECT Order_ID, Date, Total FROM Returns;
Result:
(empty result set)
Explanation:
Since the Sales table is empty, there are no rows to subtract from the Returns table.
INTERSECT and MINUS Operation results in each scenario
In Scenario 1, the INTERSECT operation will return the matching values in both tables:
Query:
SELECT Order_ID, Date, Total FROM Sales
INTERSECT
SELECT Order_ID, ' ', 0 FROM Order_details;
Result:
| Order_ID | Date | Total |
|----------|-------------|-------|
| 1001 | 01/01/2021 | 200 |
In Scenario 2, the INTERSECT operation will return all rows since both tables have the same data:
Query:
SELECT Order_ID, Customer, Amount FROM Orders
INTERSECT
SELECT Order_ID, Customer, Amount FROM Order_copy;
Result:
| Order_ID | Customer | Amount |
|----------|-----------|--------|
| 1001 | John | 200 |
| 1002 | Jane | 300 |
In Scenario 3, the INTERSECT operation will return an empty result set:
Query:
SELECT Order_ID, Date, Total FROM Sales
INTERSECT
SELECT Order_ID, Date, Total FROM Returns;
Result:
(empty result set)
Conclusion
SQL MINUS operator is essential while extracting data from two or more tables. It returns rows that are present in the first table but are not present in the second table.
To use the MINUS operator, both tables must have the same number of fields and similar data types. If there is a mismatch in the number of fields or data types, the database returns an error.
Finally, the MINUS operation is best used to compare databases and identify areas of difference.
SQL Set Operators
SQL set operators are powerful tools that enable developers to carry out more complex queries and filters on their relational databases. Set operators comprise of UNION, UNION ALL, INTERSECT, and MINUS and can be used to join datasets, filter data by combining two or more tables based on selected conditions.
Below are the two most common set operators – UNION and UNION ALL, along with how they can be used to refine results.
UNION and UNION ALL
UNION and UNION ALL are commonly used to combine the result sets of two SELECT statements into a single result set. The resulting data sets must have the same number of columns and each column must have compatible data types.
However, UNION eliminates duplicate values, while UNION ALL does not. Duplicates between the two result sets are removed, and only distinct values in returns.
Conversely, UNION ALL returns all values, including duplicates. Consider the following two tables that have the same schema and data:
Table 1: Customers
| Customer_ID | First_Name | Last_Name | Occupation |
|-------------|-------------|-----------|------------|
| 001 | John | Doe | Accountant |
| 002 | Jane | Smith | Doctor |
| 003 | Mark | Wallace | Lawyer |
Table 2: Clients
| Customer_ID | First_Name | Last_Name | Occupation |
|-------------|-------------|-----------|------------|
| 004 | Eric | Franson | Accountant |
| 002 | Jane | Smith | Doctor |
| 005 | Mariya | Ivanovna | Engineer |
Query with UNION operator:
SELECT Customer_ID, First_Name, Last_Name, Occupation FROM Customers
UNION
SELECT Customer_ID, First_Name, Last_Name, Occupation FROM Clients;
Result:
| Customer_ID | First_Name | Last_Name | Occupation |
|-------------|-------------|-----------|------------|
| 001 | John | Doe | Accountant |
| 002 | Jane | Smith | Doctor |
| 003 | Mark | Wallace | Lawyer |
| 004 | Eric | Franson | Accountant |
| 005 | Mariya | Ivanovna | Engineer |
Query with UNION ALL operator:
SELECT Customer_ID, First_Name, Last_Name, Occupation FROM Customers
UNION ALL
SELECT Customer_ID, First_Name, Last_Name, Occupation FROM Clients;
Result:
| Customer_ID | First_Name | Last_Name | Occupation |
|-------------|-------------|-----------|------------|
| 001 | John | Doe | Accountant |
| 002 | Jane | Smith | Doctor |
| 003 | Mark | Wallace | Lawyer |
| 004 | Eric | Franson | Accountant |
| 002 | Jane | Smith | Doctor |
| 005 | Mariya | Ivanovna | Engineer |
Refining Results with Set Operators
Set operators can be used to refine result sets, by combining or filtering data from two or more tables based on certain conditions. For instance, consider the following two tables:
Table 1: Sales
| ID | Product | Quantity |
|-----|---------|----------|
| 101 | Laptop | 3 |
| 102 | T-shirt | 5 |
| 103 | Camera | 2 |
Table 2: Returns
| ID | Product | Quantity |
|-----|---------|----------|
| 101 | Laptop | 1 |
| 102 | T-shirt | 2 |
You can use the UNION operator to retrieve all products sold and returned:
Query:
SELECT Product, SUM(Quantity) AS Total FROM Sales
GROUP BY Product
UNION
SELECT Product, SUM(Quantity) AS Total FROM Returns
GROUP BY Product;
Result:
| Product | Total |
|---------|-------|
| Camera | 2 |
| Laptop | 4 |
| T-shirt | 7 |
Similarly, you can use the UNION ALL operator to retrieve all products sold and returned, even if some quantities are equal to zero:
Query:
SELECT Product, SUM(Quantity) AS Total FROM Sales
GROUP BY Product
UNION ALL
SELECT Product, SUM(Quantity) AS Total FROM Returns
GROUP BY Product;
Result:
| Product | Total |
|---------|-------|
| Camera | 2 |
| Laptop | 3 |
| T-shirt | 5 |
| Laptop | 1 |
| T-shirt | 2 |
The above queries demonstrate how set operators can be used to refine the results based on certain conditions. In the first query, the UNION operator removes the duplicates, while the second query, which uses UNION ALL, returns all the results, including duplicates.
Conclusion
SQL set operators are essential tools in database management. They are used to combine, filter, or refine dataset extracting results from two or more tables based on certain conditions.
The UNION and UNION ALL operators are widely used to join datasets, filtering out duplicates by combining two or more tables based on a condition. The ability to refine the results based on certain conditions makes set operators a powerful tool to have in your SQL toolkit.
In conclusion, SQL Set Operators are essential tools for developers to carry out complex queries and filters on their relational databases. These operators include the UNION, UNION ALL, INTERSECT, and MINUS operators, which can be used to join datasets, filter data, and refine dataset values in tables. The UNION and UNION ALL operators are widely used and are important in SQL, allowing for the customization of result sets depending on specific use cases. The ability to refine data based on certain conditions makes SQL Set Operators a powerful tool that every developer should familiarize themselves with.