SQL exclude rows without subquery

Question

I have these tables:

PERSON               COURSE               PERSON_COURSE
+----+----------+    +----+----------+    +------+------+
| ID | Name     |    | ID | Name     |    | P_ID | C_ID |       
+----+----------+    +----+----------+    +------+------+
| P1 | Person 1 |    | C1 | Course 1 |    | P1   | C1   |
| P2 | Person 2 |    | C2 | Course 2 |    | P1   | C2   |
| P3 | Person 3 |    | C3 | Course 3 |    | P3   | C2   |
+----+----------+    +----+----------+    | P3   | C3   |
                                          +------+------+

and I want to select all persons which does not attend course C1.

So I'm using:

select p.id
from person p
where p.id not in (
    select pc.p_id 
    from person_course pc
    where pc.c_id != 'C1')

Now, I'm wondering if it's possible to obtain the same result without using a subquery.

Joachim Isaksson · Accepted Answer · 2016-02-28 10:26:50Z

3

One option is a left join, trying to match people with the course and including only people where there is no match;

SELECT p.* 
FROM person p
LEFT JOIN person_course pc
  ON p.id = pc.p_id
 AND pc.c_id = 'C1'
WHERE pc.c_id IS NULL;

An SQLfiddle to test with.

edited Feb 28, 2016 at 10:26

answered Feb 28, 2016 at 10:25

Joachim Isaksson

182k28 gold badges297 silver badges307 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Lukasz Szozda · Accepted Answer · 2016-02-28 10:21:45Z

1

I want to select all persons which does not attend course C1.

You could use aggregation:

SELECT P.ID, P.NAME
FROM PERSON P
LEFT JOIN PERSON_COURSE PC
  ON P.ID = PC.P_ID
GROUP BY P.ID, P.NAME
HAVING SUM(CASE WHEN PC.C_ID = 'C1' THEN 1 ELSE 0 END) = 0;

LiveDemo

Output:

╔════╦══════════╗
║ ID ║   Name   ║
╠════╬══════════╣
║ P2 ║ Person 2 ║
║ P3 ║ Person 3 ║
╚════╩══════════╝

edited Feb 28, 2016 at 10:21

answered Feb 28, 2016 at 10:16

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Comments

Gordon Linoff · Accepted Answer · 2016-02-28 12:27:50Z

1

I just want to point out that you can do this using a subquery, but it is not the one in the question. Instead:

select p.id
from person p
where p.id not in (select pc.p_id 
                   from person_course pc
                   where pc.c_id = 'C1'
---------------------------------^
                  );

(Although I prefer NOT EXISTS for this logic, I am keeping this as similar to your logic as possible.)

Be careful with double negatives.

answered Feb 28, 2016 at 12:27

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

SQL exclude rows without subquery

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related