Shulou(Shulou.com)06/01 Report--

What is the case of using the minus operator in mysql? This problem may be often seen in our daily study or work. I hope you can gain a lot from this question. The following is the reference content that the editor brings to you, let's take a look at it!

Mysql does not support the MINUS operator, but you can use MySQL JOIN to simulate it in the syntax format "SELECT Field list FROM Table 1 LEFT JOIN Table 2 ON join_predicate WHERE Table 2.id IS NULL;".

Introduction to SQL MINUS operator

MINUS is one of the three operations that include the SQL standard, UNION,INTERSECT and MINUS.

MINUS compares the results of the two queries and returns the different rows in the first query that are not output by the second query.

The MINUS instruction is applied to two SQL statements. It first finds the results produced by the first SQL statement, and then sees if those results are in the results of the second SQL statement. If so, this piece of data will be removed and will not appear in the final result. If the result produced by the second SQL statement does not exist in the result produced by the first SQL statement, the data is discarded.

The syntax of the MINUS operator is explained as follows:

SELECT column_list_1 FROM table_1MINUS SELECT columns_list_2 FROM table_2

The basic rules for queries that use the MINUS operator are as follows:

The quantity and the order of the two columns column_list_1 and column_list_2 must be the same.

The data types of the corresponding columns in the two queries must be compatible.

Suppose we have two tables T1 and T2 with the following structure and data:

CREATE TABLE T1 (id INT PRIMARY KEY); CREATE TABLE T2 (id INT PRIMARY KEY); INSERT INTO T1 VALUES (1), (2), (3); INSERT INTO T2 VALUES (2), (3), (4)

The following query returns different values in the query of the T1 table, which cannot be found in the query results of the table.

SELECT id FROM t1MINUSSELECT id FROM t2

The following is an illustration of the MINUS diagram:

MySQL MINUS operator

Unfortunately, MySQL does not support the MINUS operator. However, you can use MySQL JOIN to simulate it.

To simulate two MINUS queries, use the following syntax:

SELECT column_list FROM table_1 LEFT JOIN table_2 ON join_predicateWHERE table_2.id IS NULL

For example, the following query uses the LEFT JOIN clause to return the same results as the MINUS operator:

SELECT idFROM T1 LEFT JOIN T2 USING (id) WHERE t2.id IS NULL; Thank you for reading! After reading the above, do you have a general idea of the case of using the minus operator in mysql? I hope the content of the article will be helpful to all of you. If you want to know more about the relevant articles, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.