Coder Perfect

What are SQL EXISTS statements and how do they work?


I’m learning SQL, and I’m having trouble understanding EXISTS statements. I stumbled saw this comment regarding “exists” and I’m not sure what to make of it:

What I’m not sure about is how the outer query determines which row the subquery is inspecting. Consider the following scenario:

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

If the ids from the supplier and orders tables match, the subquery returns true, and all columns from the matching row in the suppliers’ table are outputted. What I don’t get is how the subquery indicates which specific row (let’s say row 25 with supplier id 25) should be reported when only a true or false is returned.

There appears to be no connection between the outer query and the subquery in my opinion.

Asked by Dan

Solution #1

Consider the following scenario:

Check if there is a record in the Order table that fulfills the criterion Suppliers.supplier id (this comes from Outer query current ‘row’) = Orders.supplier id for ‘each’ row from Suppliers. Stop when you locate the first matching row – the WHERE EXISTS condition has been met.

The fact that Supplier id is sent from the outer query to the subquery for each row evaluated is the magic link between the outer query and the subquery.

To put it another way, the subquery is run for each table row returned by the outer query.

It’s not as if the subquery runs over the entire table, extracting the ‘true/false’ condition, and then attempting to match this ‘true/false’ condition with the outer query.

Answered by sojin

Solution #2

What is the purpose of the WHERE clause in the EXISTS example, in your opinion? How do you come to that conclusion when the SUPPLIERS reference isn’t in the FROM or JOIN clauses within the EXISTS clause?

Because EXISTS evaluates for TRUE/FALSE and exits as TRUE on the first match of the criterion, it can be faster than IN. Also keep in mind that in an EXISTS, the SELECT clause is omitted – i.e.

                 FROM ORDERS o
                WHERE o.supplier_id = s.supplier_id)

…should result in a division by zero mistake, but it will not. The most crucial part of an EXISTS clause is the WHERE clause.

Also keep in mind that a JOIN isn’t a direct substitute for EXISTS because there will be duplicate parent records if the parent has more than one child record.

Answered by OMG Ponies

Solution #3

You can get the same results by using JOIN, EXISTS, IN, or INTERSECT:

SELECT s.supplier_id
FROM suppliers s
INNER JOIN (SELECT DISTINCT o.supplier_id FROM orders o) o
    ON o.supplier_id = s.supplier_id

SELECT s.supplier_id
FROM suppliers s
WHERE EXISTS (SELECT * FROM orders o WHERE o.supplier_id = s.supplier_id)

SELECT s.supplier_id 
FROM suppliers s 
WHERE s.supplier_id IN (SELECT o.supplier_id FROM orders o)

SELECT s.supplier_id
FROM suppliers s
SELECT o.supplier_id
FROM orders o

Answered by Anthony Faull

Solution #4

If your where clause looked anything like this:

WHERE id in (25,26,27) -- and so on

It’s easy to see why some rows are returned and others aren’t.

When the where clause is written as follows:

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

Have an existing entry with the same id in the orders table.

Answered by Menahem

Solution #5

Assume we have a one-to-many table link between the following two tables in our database.

Because it has a student id Foreign Key column that references the id Primary Key column in the student table, the student grade table is the child table.

The following two records are found in the student table:

| id | first_name | last_name | admission_score |
| 1  | Alice      | Smith     | 8.95            |
| 2  | Bob        | Johnson   | 8.75            |

In addition, the student grade table contains the following information on the students’ grades:

| id | class_name | grade | student_id |
| 1  | Math       | 10    | 1          |
| 2  | Math       | 9.5   | 1          |
| 3  | Math       | 9.75  | 1          |
| 4  | Science    | 9.5   | 1          |
| 5  | Science    | 9     | 1          |
| 6  | Science    | 9.25  | 1          |
| 7  | Math       | 8.5   | 2          |
| 8  | Math       | 9.5   | 2          |
| 9  | Math       | 9     | 2          |
| 10 | Science    | 10    | 2          |
| 11 | Science    | 9.4   | 2          |

Let’s assume we’re looking for all pupils that scored a 10 in Math class.

If we only want to look for the student identifier, we may use a query like this:

    student_grade.grade = 10 AND
    student_grade.class_name = 'Math'

However, because the application wants to display a student’s full name, not just the identifier, we’ll need data from the student database as well.

We can use the EXISTS SQL operator to filter student records with a 10 grade in Math, as seen below:

    id, first_name, last_name
    SELECT 1
        student_grade.student_id = AND
        student_grade.grade = 10 AND
        student_grade.class_name = 'Math'

We can see that only the Alice row is picked when we perform the query above:

| id | first_name | last_name |
| 1  | Alice      | Smith     |

The outer query picks the columns from the student row that we want to send back to the client. The WHERE clause, on the other hand, employs the EXISTS operator in conjunction with an inner subquery.

If the subquery produces at least one record, the EXISTS operation returns true; otherwise, it returns false. The database engine is not required to execute the subquery in its entirety. The EXISTS operator returns true if a single record is matched, and the associated other query row is selected.

Because the student id column of the student grade table is matched against the id column of the outer student table, the inner subquery is correlated.

Answered by Vlad Mihalcea

Post is based on