Dismiss Notice
Join Physics Forums Today!
The friendliest, high quality science and math community on the planet! Everyone who loves science is here!

SQL "Where not exists" (nested loops)

  1. May 14, 2014 #1
    I'm having a hard time understanding how nested loops work in SQL.

    Find all the people who likes all the hobbies.

    Code (Text):

    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?
     
  2. jcsd
  3. May 15, 2014 #2

    SixNein

    User Avatar
    Gold Member

    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: May 15, 2014
  4. May 16, 2014 #3

    harborsparrow

    User Avatar
    Gold Member

    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 (...
     
  5. May 17, 2014 #4

    Borg

    User Avatar
    Science Advisor
    Gold Member

    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).
     
  6. Mar 17, 2016 #5
    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: Mar 17, 2016
  7. Mar 18, 2016 #6
    Thank you!

    My previous company had the complete opposite standard:
    select P.NAME
    from PERSON P
    where not exists(...
    That irked me so badly.
     
Know someone interested in this topic? Share this thread via Reddit, Google+, Twitter, or Facebook

Have something to add?
Draft saved Draft deleted



Similar Discussions: SQL "Where not exists" (nested loops)
  1. SQL Help (Replies: 2)

Loading...