SQL "Where not exists" (nested loops)

  • Thread starter Thread starter whitehorsey
  • Start date Start date
  • Tags Tags
    Loops Sql
AI Thread Summary
The discussion focuses on understanding the SQL query that finds people who like all hobbies using nested loops with "NOT EXISTS." The query employs two nested subqueries to check if a person has hobbies that do not match all distinct hobbies in the hobby table. The first "NOT EXISTS" identifies hobbies not associated with a person, while the second ensures that only those who have all hobbies are selected. Participants emphasize the importance of evaluating the innermost subquery first and suggest naming conventions for clarity. Overall, the conversation aims to clarify the logic behind the query structure and its execution.
whitehorsey
Messages
188
Reaction score
0
I'm having a hard time understanding how nested loops work in SQL.

Find all the people who likes all the hobbies.

Code:
select p.name 
from person p 
where not exists (select *
			      from (select distinct h.hobby
					    from hobby h) as h1
	              where not exists (select *
									from hobby as h2
									where h2.hobby = h1.hobby and h2.id = p.id))

My logic:
In the first not exists it gets all the columns in hobby. The second not exists it gets all the hobbies but this time checks to see if the hobby are equal and the ids. But how does this all come together?
 
Technology news on Phys.org
whitehorsey said:
I'm having a hard time understanding how nested loops work in SQL.

Find all the people who likes all the hobbies.

Code:
select p.name 
from person p 
where not exists (select *
			      from (select distinct h.hobby
					    from hobby h) as h1
	              where not exists (select *
									from hobby as h2
									where h2.hobby = h1.hobby and h2.id = p.id))

My logic:
In the first not exists it gets all the columns in hobby. The second not exists it gets all the hobbies but this time checks to see if the hobby are equal and the ids. But how does this all come together?

You want to start your evaluation with the inner most subquery and work your way back. Kind of like mathematics where you have a situation like a(b(c*d)).Start with:
(select * from (select distinct h.hobby from hobby h) as h1
where not exists (select * from hobby as h2 where h2.hobby = h1.hobby and h2.id = p.id))

So here you have a derived table before the where not exists and a sub query afterwards.

Look at the sub query first.

exists is true when a subquery returns results. Not exists means its false when it returns results.

Now it's just a matter of walking this backwards till we evaluate the outermost statement.
 
Last edited:
An unsolicited piece of advice: write Sql keywords in caps, variable and table names in lower case. Like this:

SELECT p.name
FROM person p
WHERE NOT EXISTS (...
 
whitehorsey said:
I'm having a hard time understanding how nested loops work in SQL.

Find all the people who likes all the hobbies.

Code:
select p.name 
from person p 
where not exists (
    select * from (
         select distinct h.hobby from hobby h) as h1
	 where not exists (
               select * from hobby as h2 
               where h2.hobby = h1.hobby and h2.id = p.id))

My logic:
In the first not exists it gets all the columns in hobby. The second not exists it gets all the hobbies but this time checks to see if the hobby are equal and the ids. But how does this all come together?

Just to be clear. Are you trying to list the names of people who like every type of hobby in the Hobby table?

What are the column names in those two tables? Your Hobby table has a column named hobby. It's not a good idea to name columns in your table with the same exact name as your table. You should either rename the table HOBBIES or change the name of the column to HOBBY_NAME.

Also, I wouldn't expect the Hobby table IDs have anything to do with the Person table IDs (i.e., h2.id = p.id). Based on the way that you're trying to construct the query, I would expect the Hobby table to have a column named something like PERSON_ID (i.e., h2.person_id = p.id).
 
whitehorsey said:
I'm having a hard time understanding how nested loops work in SQL.

Find all the people who likes all the hobbies.

Code:
select p.name
from person p
where not exists (select *
                  from (select distinct h.hobby
                        from hobby h) as h1
                  where not exists (select *
                                    from hobby as h2
                                    where h2.hobby = h1.hobby and h2.id = p.id))

My logic:
In the first not exists it gets all the columns in hobby. The second not exists it gets all the hobbies but this time checks to see if the hobby are equal and the ids. But how does this all come together?

DO NOT THINK in the normal "NOT IN" way, I consider it as an "attribute" way

Think of it in this way.

1. For each person you try to find, his/her hobbies are listed in the innermost connected table, and this person needs to have another attribute which is he/she don't have all the hobbies in the hobbies table. That is the first NOT EXIST.

select *
from (select distinct h.hobby
from hobby h) as h1
where not exists (select *
from hobby as h2
where h2.hobby = h1.hobby and h2.id = p.id)
means that pick hobbies out from the hobby table that does not belong to this person.

2. But we don't want this person.

3. So we used second NOT exist.

So, in short, Start with the innermost combined table in your mind. and You don't want those person whose hobbies (think as his/her attribute) does not match all the hobbies from the derived hobbies table..
 
Last edited:
harborsparrow said:
An unsolicited piece of advice: write Sql keywords in caps, variable and table names in lower case. Like this:

SELECT p.name
FROM person p
WHERE NOT EXISTS (...
Thank you!

My previous company had the complete opposite standard:
select P.NAME
from PERSON P
where not exists(...
That irked me so badly.
 
Thread 'Is this public key encryption?'
I've tried to intuit public key encryption but never quite managed. But this seems to wrap it up in a bow. This seems to be a very elegant way of transmitting a message publicly that only the sender and receiver can decipher. Is this how PKE works? No, it cant be. In the above case, the requester knows the target's "secret" key - because they have his ID, and therefore knows his birthdate.

Similar threads

Back
Top