How Exist works?

Question

MySQL

Already Bosko rides from Exists. There is a request like this:

SELECT *
FROM authors
WHERE EXISTS (SELECT * FROM m2m_books_authors 
                  WHERE authors.a_id=m2m_books_authors.a_id)

It is clear that it will display everything from the authors table where authors.a_id=m2m_books_authors.a_id.
I'm interested in how this will all work. I imagine this:
We take a record from authors in exists and see if there is a record in it with the same id as id authors, if there is, we return true and voila, the record is placed in the main selection.
If I'm wrong, please describe in detail the process of interaction between a query and a subquery in Exists.

Answer 1

In general, I found a detailed explanation here https://stackoverflow.com/questions/5846882/how-do...
If in Russian: for a string from an external request, it is checked whether there is a string in the subquery that matches the condition (in the subquery). When a suitable row is found, the where exists condition will be satisfied for this row and the row will be included in the main selection.
The subquery is executed for each row of the outer query table.

Answer 2

I think it depends on what indexes and what kind of statistics there are on the tables. The task is similar to join algorithms.
For example, if there are indexes by fields, it is easier to bypass all records in the table in which there are fewer of them in ascending index, while moving along the index in a larger table and selecting records from there (for more details, see merge join).
Which particular algorithm is selected will be shown by the EXPLAIN command or the Visual Explain tool in MYSQL Workbench

Answer 3

Most likely, the scheduler will figure it out and build an inner join, but it is possible that he does not have enough intelligence for this and the request will be stupid ...
Therefore, it will be much more honest to write inner join