SQL "Where not exists" (nested loops)

[whitehorsey]

I'm having a hard time understanding how nested loops work in SQL.

Find all the people who likes all the hobbies.

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?

 

[SixNein]

I'm having a hard time understanding how nested loops work in SQL.

Find all the people who likes all the hobbies.

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.

 

[harborsparrow]

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 (...

 

[Borg]

I'm having a hard time understanding how nested loops work in SQL.

Find all the people who likes all the hobbies.

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).

 

[brucelee073]

I'm having a hard time understanding how nested loops work in SQL.

Find all the people who likes all the hobbies.

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..

 

[newjerseyrunner]

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.