Usage of exists,not exists in SQL

Both exists and in are representations of existence, and exists emphasizes whether to return a result set, the difference between exists and in is that the in-boot clause can only return one field.

Not exists and not are mutually exclusive conditions for exists and in.

Assuming that tables A and B, the data record of a is not present in B, and the key in output a (the output cannot contain duplicate keys), SQL is as follows:

SELECT DISTINCT

a.ID

, A.name

From

Table A

where exists

(SELECT

1

From

B

where

a.x=b.x

and A.Y=B.Y

);

Execute the above SQL to output the data record in B in A;

If you want to output a data record that is not in B, as long as you change the exists to not exists, you can change the not exists condition to the negation of the associated condition of table A and B below, namely:

a.x<>b.x

OR A.Y<>B.Y

When the condition is changed to the above conditions, the output record will be repeated output several, although the above has distinct operation, but affect the efficiency.

Usage of exists,not exists in SQL