How does Exists exactly works when using it like a join?-CodePudding

I feel clumsy because I have been looking for information on how exists exactly works and I continue without undetsanding it.

Imagine I have the following query:

SELECT *
  FROM suppliers
 WHERE EXISTS (select *
                 from orders
                where suppliers.supplier_id = orders.supplier_id);

What I know from exists is that if its argument query is true, the outer query will be executed. So imagine that the inner query is true, then the output would be all the table as the outer query is:

    SELECT *
      FROM suppliers

But actually it is not, I think I'm missing something when exists is used to make inner join like in this example but I don't understand...

I have taken this example of this post and I have read all the answers and I didn't understand it really well.. : How do SQL EXISTS statements work?

I really appreciate all the possible help. Thanks in advance

CodePudding user response：

It's essentially implementing the following pseudo-code loop:

for row in suppliers
    current_supplier_id = row.supplier_id
    perform subquery: 
        SELECT * 
        FROM orders 
        WHERE orders.supplier_id = current_supplier_id
    if the subquery returns any results, add row to the result set

Now you should be able to see how the inner query is changing each time. It's just looking for one particular supplier_id, not any of them.