Adventures in Machine Learning

Mastering SQL Set Operators: A Guide for Database Management

SQL

INTERSECT and

MINUS operatorsSQL (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 Operatorsto 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.

Popular Posts