How to fetch records using NOT syntax?

Question

I have the next tables: faculty, faculty_subjects and subjects. Every faculty has a list of subjects

db schema

How can I SELECT subjects that are not needed for this faculty ?

Note: if user adds some new subject, then this subject doesn't already have a record in faculty_subjects table, but it should be displayed in SELECT.

So to sum up - from the list of ALL subjects that exists I want to know the ones that doesn't needed for this faculty. How this can be achieved ?

I'm using MySQL database.

PS. if there is a more better title - please edit.

Use "where NOT EXISTS (sub-qeury)".

jarlh
– jarlh

2015-01-23 09:11:15 +00:00
Commented Jan 23, 2015 at 9:11 — jarlh
– jarlh, Commented Jan 23, 2015 at 9:11

GarethD · Accepted Answer · 2015-01-23 09:10:33Z

3

You can LEFT JOIN to the Faculty_Subject, with the particulary Faculty ID you are interested in used in the join predicate, then filter out the rows with a match using the WHERE predicate, so you are left with the subjects you want:

SELECT  s.id, s.Name
FROM    Subject AS s
        LEFT JOIN Faculty_Subject AS fs
            ON fs.Subject_idSubject = s.ID
            AND fs.Faculty_idFaculty = 1
WHERE   fs.id IS NULL;

Note, I would usually advise to use NOT EXISTS syntax:

SELECT  s.id, s.Name
FROM    Subject AS s
WHERE   NOT EXISTS
        (   SELECT  1
            FROM    Faculty_Subject AS fs
            WHERE   fs.Subject_idSubject = s.ID
            AND     fs.Faculty_idFaculty = 1
        );

Which I beleive is more logical to the reader, but in MySQL LEFT JOIN/IS NULL performs better than NOT EXISTS

answered Jan 23, 2015 at 9:10

GarethD

70k11 gold badges90 silver badges130 bronze badges

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

3 Comments

foobar Over a year ago

I'd also suggest the JOIN-version as it's usually always faster than the list iteration.

Thorsten Kettner Over a year ago

@foobar: But GarethD would "usually advise to use NOT EXISTS", as would I. Yes, MySQL was known to have problems to execute this equally fast as the outer join trick. But why start with a trick instead of trying the straight-forward way first, which would be more readable and thus easier to maintain? Use tricks only when you run into performance problems. (And of what I heard MySQL's optimizer has become much better, so this might not even be the case for such a simple query any longer.)

marknorkin Over a year ago

@GarethD yeap, this is really what i needed

SGrebenkin · Accepted Answer · 2015-01-23 09:44:04Z

2

SELECT name FROM Subject WHERE id NOT IN
    (SELECT FS.Subject_idSubject
        FROM Faculty_Subjects AS FS
        INNER JOIN Faculty AS F ON F.id=FS.Faculty_idFaculty
    WHERE F.name='Physics' AND FS.Subject_idSubject IS NOT NULL)

edited Jan 23, 2015 at 9:44

answered Jan 23, 2015 at 9:19

SGrebenkin

5624 silver badges10 bronze badges

4 Comments

jarlh Over a year ago

NOT IN is a bit dangerous, because if FS.Subject_idSubject contains a NULL, no rows at all are returned. My advice is to either make sure no NULL's are returned from the sub-query, or (even better) use NOT EXISTS instead.

SGrebenkin Over a year ago

Agree with that, fixed, +1

Thorsten Kettner Over a year ago

I think you can remove the not null test again. It would be a strange bridge table if Subject_idSubject were nullable. And if it were possible to have null values, then these should have a meaning such as "all subjects", which would have to get special treatment in a query anyway.

Thorsten Kettner Over a year ago

In a case like this ("give me all subjects that are not related to faculty xy") the faculty's ID is usually known by the calling app (for why should the app know the name but not the ID?). This would make the query much simpler, and I'd prefer it over NOT EXISTS for its simplicity.

Collectives™ on Stack Overflow

How to fetch records using NOT syntax?

2 Answers 2

3 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related