Analysis on mysql exists and not exists

This article mainly introduces the relevant information about the detailed explanation of mysql exists and not exists instances. Since the efficiency of not exists is often higher than that of not in, the former is generally used instead of the latter. Friends who need it can refer to it. I hope it can Help everyone.

Mysql exists and not exists examples detailed explanation

tableA

|column1 | column1 |column3 |

tableb

|column1 | column1 |column3 |

To query the data of tableA, the condition is that tableA.column1 is not in tableB.column2 of tableB

That is to say, we need to get the effect similar to the following statement (the effect of not in is not completely the same For not exists, if an empty record appears in the subquery, the entire query statement will not return data)

SELECT 
  a.*
FROM
  tableA a
WHERE 
  a.column1 not in (
    SELECT column2 FROM tableB
  )

You can use the following statement to achieve

SELECT
  a.*
FROM
  tableA a
WHERE
  NOT EXISTS(
    SELECT b.column2 FROM tableB b WHERE a.colunm1=b.column2
  )

The above is only the case of two tables. In fact, It is also relatively easy to use in connection queries of multiple tables. The above writing method is also applicable to exists

Related recommendations:

php file_exists() function has no effect because What?

Recommend 10 articles about exists()

Recommend 10 articles about php file_exists() function

The above is the detailed content of Analysis on mysql exists and not exists. For more information, please follow other related articles on the PHP Chinese website!