CODEWITHSUNDEEP
sqlleft-joinnotin
user4628475
user4628475

Reputation:

LEFT OUTER JOIN does not work

Good Morning,

I am on a SQL learning tour and trying to create a small database with a few queries to gain experience. Two databases where used, Person {id, name, age} and Knows {id, guest1_id → Persons, guest2_id → Persons}

The query should result in a list of names of people that do not know anyone from the database, but can be known by others. Below is the code that I have got so far, but it does not seem to acquire anything.

What is the problem here?

SELECT distinct K.id
FROM Persons P 
LEFT JOIN Knows K
ON K.guest1_id = P.id 
AND K.guest2_id = P.id
WHERE K.id NOT IN (
    SELECT id
    FROM Knows )

Thank you!

Upvotes: 0

Views: 200

Answers (4)

GolezTrol
GolezTrol

Reputation: 116110

The condition in your where clause is a bit silly:

...  K.id NOT IN (SELECT id
                 FROM Knows)

K is an alias of Knows, so basically this says: "Only select rows that don't exist".

Moreover, this condition doesn't work with NULL values. I think what you tried to do is actually this:

SELECT P.id
FROM Persons P 
  WHERE NOT EXISTS(
     SELECT * FROM Knows K WHERE 
        K.guest1_id = P.id OR
        K.guest2_id = P.id)

This query says: "Select all persons that are not referred to in the guest1 field and/or the guest2 field of 'Knows'.

You could also write this using a LEFT JOIN, and K.id IS NULL in the where clause. This is closer to your attempt, but symantically less accurate to the question, and therefore not preferred in my opinion.

SELECT distinct K.id
FROM Persons P 
LEFT JOIN Knows K ON K.guest1_id = P.id OR K.guest2_id = P.id
WHERE K.id IS NULL

Upvotes: 0

DRapp
DRapp

Reputation: 48139

Your question doesn't really make sense, nor does the look of the query. But, If you are looking for all people who don't know anyone, then that in summary means the person is in neither the guest1 or guest2 ID column within the Knows table.

If that is the case, you can do a double-left-join to the knows table and just get those that don't fit in either side

SELECT 
      P.*
   from 
      Persons P 
      LEFT JOIN Knows K1
         on P.id = K1.guest1
      LEFT JOIN Knows K2
         on P.id = K2.guest2
   where
          K1.guest1 IS NULL
      AND K2.guest2 IS NULL

So if your table of 

Persons
ID  Name
1   A
2   B
3   C
4   D

and Knows table
ID  Guest1   Guest2
1   1        3
2   1        4
3   3        4

Then person 2 is the only person that does not know any other person, thus their ID is not in either Guest1 OR Guest2 columns of the Knows table.

Upvotes: 1

Fabien TheSolution
Fabien TheSolution

Reputation: 5050

Try this :

SELECT P.*
FROM Persons P 
LEFT JOIN Knows K ON K.guest1_id = P.id
WHERE K.id IS NULL

This will give you Persons that know nobody.

You can also try this :

SELECT *
FROM Persons
WHERE NOT EXISTS(SELECT 1 FROM Knows WHERE guest1_id = P.id)

Upvotes: 0

IcyBright
IcyBright

Reputation: 664

WHERE K.id NOT IN (SELECT id FROM Knows)

This where clause essentially excluded all data entries, K.id must be in ids of Knows.

Upvotes: 0

Related Questions