Problem
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:
SELECT *
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.
SELECT s.*
FROM SUPPLIERS s
WHERE EXISTS (SELECT 1/0
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
INTERSECT
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:
SELECT
student_grade.student_id
FROM
student_grade
WHERE
student_grade.grade = 10 AND
student_grade.class_name = 'Math'
ORDER BY
student_grade.student_id
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:
SELECT
id, first_name, last_name
FROM
student
WHERE EXISTS (
SELECT 1
FROM
student_grade
WHERE
student_grade.student_id = student.id AND
student_grade.grade = 10 AND
student_grade.class_name = 'Math'
)
ORDER BY id
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 https://stackoverflow.com/questions/5846882/how-do-sql-exists-statements-work