SQL Course - Sub Queries Lesson- Challenge 6

Hi All,

Course: Analyse Data with SQL (Pro Path)
Lesson: Sub-Queries, Challenge 6
Topic: Exists & Not Exists

Q1 - I’m a little bit failing to understand as to what EXISTS & NOT EXISTS does.

In my mind (for an easier understanding) - I’m thinking SQL is checking each record - and comparing it against the criteria in the subquery. if it returns true, then it gets displayed on the result screen - and if it returns false, then not.

I guess this also brings up the topic of “order of execution” - In this, the outerquery and subquery are consequential i.e one (subquery) after another (outerquery)

Would this be a correct understanding?

Question 2:

Would there be a good regular example of when we would use NOT EXISTS?

Question 3:

The answer to the challenge is following:

FROM band_students
WHERE EXISTS (
   SELECT grade
   FROM drama_students);
type or paste code here

In Simple english, is it first checking what grades are there in the band_students table, and then checking against the subquery? Which is nothing but checking if the grade from band_students exists in the drama_students table.

If it returns true, then it gets displayed on the result screen - otherwise not.

Is this a correct understanding?

The order of writing a SQL query is: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT

But don’t confuse that with the logical order of operations/order of execution…or, what happens ‘behind the scenes’, so to speak. Or, in simple terms how the “computer” executes the query (how DBs optimize query clauses). It’s not mentioned in the CC SQL lessons b/c it can be confusing to anyone learning SQL. You can google that if you want. I don’t want to confuse here. :slight_smile:

Concerning (nested) subqueries, the inner query runs first and then the outer query runs. They are two separate queries. Your inner query is selecting the grades in which the drama students are currently enrolled. The EXISTS is basically saying, ‘if this is True, then compare it to the outer query.’

OR, as it states in the lesson:
“… However, when the inner query is included using an EXISTS or NOT EXISTS clause, we are only checking for the presence of rows meeting the specified criteria, so the inner query only returns a true or false.”

Your final query results are the grade levels representative for both drama and band students.

Other resources about subqueries that might be helpful:

https://www.geeksforgeeks.org/sql-subquery/
https://www.geeksforgeeks.org/sql-correlated-subqueries/

https://www.sqltutorial.org/sql-subquery/

4 Likes

Thanks for the answer.

Those links towards the end for super helpful.

1 Like