LEFT OUTER JOIN does not work

Question

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!

You're asking only for rows of `Knows` where: a) `guest1` is a certain Person **and** b) `guest2` is that same person. That doesn't sound like what you want does it...? I'll type out a suggested solution shortly — Shai, Mar 03 '15 at 16:11
http://wiki.lessthandot.com/index.php/WHERE_conditions_on_a_LEFT_JOIN — HLGEM, Mar 03 '15 at 16:37

DRapp · Answer 1 · 2015-03-03T16:42:36.517

1

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.

edited Mar 03 '15 at 16:42

answered Mar 03 '15 at 16:12

DRapp

47,638
12
72
142

Your answer seems logical to me, however, it gives me an error in my SQL syntax. I know this is very basic but I can not seem to find the syntax error. – Mar 03 '15 at 16:29
@Ypan, I had Person.* and not just the alias P.* The query could not find the "Person" table reference since it was a "PersonS" table or the alias "P". Also, the other answer you checked as solution would fail as it is never testing for a person in the second position. – DRapp Mar 03 '15 at 16:43

score 0 · Answer 2 · answered Mar 03 '15 at 16:11

0

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.

answered Mar 03 '15 at 16:11

IcyBright

654
4
15

GolezTrol · Answer 3 · 2015-05-15T10:02:20.283

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

Fabien TheSolution · Accepted Answer · 2015-03-03T16:32:09.013

0

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)

edited Mar 03 '15 at 16:32

answered Mar 03 '15 at 16:24

Fabien TheSolution

5,055
1
18
30

I see where you are getting at, but the person that knows nobody can still be known by anyone else. Should I change the K.id IS NULL to either one of them IS NULL? Or is that not the solution? – Mar 03 '15 at 16:31
I edited both queries to reflect this. I just removed any reference to `guest2_id` – Fabien TheSolution Mar 03 '15 at 16:32

LEFT OUTER JOIN does not work

4 Answers4