The usage of EXISTS in MySQL

What is the difference between EXISTS and in usage in MySQL?

Assume that there are two tables in the database, table A and table B, respectively

CREATE Table A

(

a_id int,

A_name varchar (20)

)

CREATE TABLE B

(

b_id int,

B_name varchar (20)

)

So

Select *  from where inch (Select from B)

The meaning of this SQL statement is obviously to select the data that satisfies all the columns in a where condition. And where condition is a_name if all b_name one of them.

That is, assuming that b_name in B has {' John ', ' Peter ', ' Baron '}, and that the a_name in a piece of data in a is exactly one of them, then this line of data is selected.

Instead, use exists:

Select *  from where exists (selectfromb where b.b_name=a.a_name)

The result of the execution is the same as the result returned using the in.

So why is this, the column returned in the subquery is b_id, and there is no name.

The reason is that the EXISTS clause returns no result set from the database, but a Boolean value that returns TRUE if the clause queries the data, and false instead.

So the column selected in the clause is not important at all, and what is important is where the condition is. If True is returned, it is equivalent to directly executing the part of the clause where the

A_name and B_name are compared, and if they are equal, the data is returned. So the result of the execution is consistent with the result of the previous return using in.

Interestingly, the MySQL internal optimizer translates the first statement using in into the second one using the EXISTS statement. The result of execution is of course the same.

The usage of EXISTS in MySQL