Using the MINUS operator in SQL

Usage of MINUS in SQL and specific code examples

In SQL, MINUS is an operation used to perform a difference set operation between two result sets symbol. It is used to delete the same rows from the first result set as in the second result set. The result set returned by the MINUS operator will contain rows that exist only in the first result set.

The following is a specific code example to demonstrate the usage of MINUS:

Suppose there are two tables - "table1" and "table2", their structures are as follows:

Table Name: table1
Fields: ID (integer), Name (string), Age (integer)

Table name: table2
Fields: ID (integer), Name (string), Age (Integer)

Now we need to find the rows where the values ​​of the "ID" and "Age" fields in table1 are not the corresponding field values ​​in table2. We can use the MINUS operator to achieve this goal.

Code example 1:

SELECT ID, Age
FROM table1
MINUS
SELECT ID, Age
FROM table2;

In the above code, we first select the two fields "ID" and "Age" from table1, and then use the MINUS operator to subtract the same fields in table2 field. The final returned result set will contain those rows that only exist in table1.

Code example 2:

SELECT ID, Name, Age
FROM table1
MINUS
(
    SELECT ID, Name, Age
    FROM table2
    WHERE Age > 30
);

In the above example, we select the three fields "ID", "Name" and "Age" from table1, and then use the MINUS operator to subtract Rows in table2 that meet the specified condition (Age > 30). The returned result set will contain rows that exist in table1 and do not meet the specified conditions.

It should be noted that the MINUS operator will only delete duplicate rows, not duplicate columns. Therefore, when using the MINUS operator, the number of columns and data types of the result sets must be the same.

Summary:
The MINUS operator is a commonly used SQL operator, which is used to obtain the difference between two result sets. With the MINUS operator, we can easily find out those rows that only exist in one table and not in another table. When using the MINUS operator, we need to ensure that the number of columns and data types to be compared are the same to ensure the correctness of the operation.

Hope the above code example can help you better understand the usage of MINUS in SQL. If you have any questions, please feel free to ask us.

The above is the detailed content of Using the MINUS operator in SQL. For more information, please follow other related articles on the PHP Chinese website!