Example sharing about exists and not exists in MySQL-Mysql Tutorial-php.cn

Example sharing about exists and not exists in MySQL

黄舟

Release： 2017-07-30 13:38:35

Original

1801 people have browsed it

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

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 tableA.column1 Not in tableB.column2 of tableB

That is to say, you will get an effect similar to the following statement (the effect of not in is not completely equivalent to 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
  )

Copy after login

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
  )

Copy after login

The above is only the case of two tables. In fact, it can be achieved with multiple tables It is also easier to use in table connection queries. The above writing method is also applicable to exists

The above is the detailed content of Example sharing about exists and not exists in MySQL. For more information, please follow other related articles on the PHP Chinese website!