SQL Statement with EXCEPT

[#1]

Hi

I would like to formulate an SQL Statement like

SELECT x FROM a EXCEPT SELECT x FROM b RIGHT OUTER JOIN table_n ON ... WHERE ...

but can't seem to find the correct syntax. Goal is to first subtract b from a and then outer join it.

I also tried to write it as

SELECT a.x FROM a LEFT OUTER JOIN b ON b.x = a.x WHERE b.x IS NULL RIGHT OUTER JOIN table_n ON ... WHERE ...

with no success.

Any hint is welcome. Regards, Robert

[VolkerBarth · Accepted solution]

The EXCEPT set operator (like UNION and INTERSECT) works on whole query blocks so it has lower precedence than a join operator.

Try to enclose the EXCEPT statement within braces and use it as a derived query and join it then, such as

SELECT [yourSelectListHere] FROM
(SELECT x FROM a
 EXCEPT
 SELECT x FROM b) dt RIGHT OUTER JOIN table_n ON dt.x = table_n... WHERE ...

[huber1 · Accepted solution]

Thanks, Volker, I use it exactly that way but somehow I do not get the number of rows the RIGHT OUTER JOIN table delivers. So I have to continue to analyse it.

[VolkerBarth · Accepted solution]

What does the WHERE clause look like? I'm asking because it is not uncommon that a condition in the WHERE clause relates to the NULL-supplying side (here the left side) and turns an OUTER join into an INNER join - been there, too...

[Breck_Carter · Accepted solution]

> turns an OUTER join into an INNER join

That's NUMBER ONE on the list! 🙂

[huber1 · Accepted solution]

@ Volker: Your suggestion could very well be the reason. The number of records (of the SELECT in the RIGHT OUTER JOIN) returned for the given example is correct when executed separately, which could hint in your direction.

@Breck: Thanks for your list.

The mentioned SQL statement looks like this (HS means Herbst-Semester):

SELECT DISTINCT
    o1.day_number,
    o1.start_time,
    o1.end_time,
    o1.untis_lesson_subject,
    o1.untis_lesson_teacher,
    o1.untis_assigned_room

FROM (
SELECT
    w.day_number,
    lt.start_time,
    lt.end_time,
    tl.untis_lesson_subject,
    tl.untis_lesson_teacher,
    tr.untis_assigned_room
FROM
    timetable_lessons tl
INNER JOIN timetable_positions tp
    ON tp.timetable_lesson_id = tl.id
INNER JOIN timetable_rooms tr
    ON tr.timetable_position_id = tp.id
INNER JOIN timetable_classes tc
    ON tc.timetable_lesson_id = tl.id
    AND tc.class_level_number = 3
    AND tc.class_sign = 'ma'
INNER JOIN profile_positions pp
    ON pp.profile_definition_subject_code = tl.subject_code
    AND pp.person_id = 15789
    AND pp.period_school_year = 2015
    AND pp.period_fraction_name = 'HS'
RIGHT OUTER JOIN lesson_times lt
    ON lt.start_time = tp.untis_assigned_start_time
    AND tp.day = lt.weekday_id
    AND tl.period_school_year = 2015
    AND tl.period_fraction_name = 'HS'
    AND tp.week_number = 51
INNER JOIN weekdays w
    ON lt.weekday_id = w.id

EXCEPT

SELECT
    w.day_number,
    lt.start_time,
    lt.end_time,
    tl.untis_lesson_subject,
    tl.untis_lesson_teacher,
    tr.untis_assigned_room
FROM
    timetable_lessons tl
INNER JOIN timetable_positions tp
    ON tp.timetable_lesson_id = tl.id
INNER JOIN  timetable_rooms tr
    ON tr.timetable_position_id = tp.id
INNER JOIN timetable_classes tc
    ON tl.id = tc.timetable_lesson_id
    AND tc.class_level_number = 3
    AND tc.class_sign = 'ma'
INNER JOIN partial_class_courses pcc
    ON pcc.timetable_lesson_code = tl.untis_lesson_subject
INNER JOIN partial_class_course_members pccm
    ON pccm.partial_class_course_name = pcc.name
    AND pccm.partial_class_course_name IN
        (SELECT
            pccm.partial_class_course_name
        FROM
            partial_class_course_members pccm
        WHERE
            pccm.class_member_class_period_school_year = 2015
            AND pccm.class_member_class_period_fraction_name = 'HS'
            AND pccm.partial_class_course_class_course_class_level_number = 3
            AND pccm.partial_class_course_class_course_class_sign = 'ma'
        EXCEPT
        SELECT
            pccm.partial_class_course_name
        FROM
            partial_class_course_members pccm
        WHERE
            pccm.class_member_person_id = 15789
            AND pccm.class_member_class_period_school_year = 2015
            AND pccm.class_member_class_period_fraction_name = 'HS'
            AND pccm.partial_class_course_class_course_class_level_number = 3
            AND pccm.partial_class_course_class_course_class_sign = 'ma')
    AND pccm.partial_class_course_class_course_class_period_school_year = 2015
    AND pccm.partial_class_course_class_course_class_period_fraction_name = 'HS'
INNER JOIN lesson_times lt
    ON lt.start_time = tp.untis_assigned_start_time
    AND tp.day = lt.weekday_id
    AND tl.period_school_year = 2015
    AND tl.period_fraction_name = 'HS'
    AND tp.week_number = 51
INNER JOIN weekdays w
    ON lt.weekday_id = w.id) o1
RIGHT OUTER JOIN (
SELECT
    lt.start_time
FROM
    timetable_lessons tl
INNER JOIN timetable_positions tp
    ON tp.timetable_lesson_id = tl.id
INNER JOIN timetable_rooms tr
    ON tr.timetable_position_id = tp.id
INNER JOIN timetable_classes tc
    ON tc.timetable_lesson_id = tl.id
    AND tc.class_level_number = 3
    AND tc.class_sign = 'ma'
INNER JOIN profile_positions pp
    ON pp.profile_definition_subject_code = tl.subject_code
    AND pp.person_id = 15789
    AND pp.period_school_year = 2015
    AND pp.period_fraction_name = 'HS'
RIGHT OUTER JOIN lesson_times lt
    ON lt.start_time = tp.untis_assigned_start_time
    AND tp.day = lt.weekday_id
    AND tl.period_school_year = 2015
    AND tl.period_fraction_name = 'HS'
    AND tp.week_number = 51
INNER JOIN weekdays w
    ON lt.weekday_id = w.id) x
    ON x.start_time = o1.start_time 
ORDER BY
    o1.day_number ASC,
    o1.start_time ASC,
    o1.untis_lesson_subject ASC,
    o1.untis_lesson_teacher ASC;

[huber1 · Accepted solution]

Sorry, the formatting of my SQL statement get's lost when I press the comment button. Is there a way the formatting is kept? The formatting is there when I copy/paste it into the comment field.

[huber1 · Accepted solution]

I forgot to mention I have no WHERE clause.

[VolkerBarth · Accepted solution]

Just insert a pre tag before the code. - I tried to but the text seems a few chars too long for one comment, so you might split up the comments and the statement code into two comments...

[huber1 · Accepted solution]

Thanks, Volker, here is the formatted SQL statement

SELECT DISTINCT
    o1.day_number,
    o1.start_time,
    o1.end_time,
    o1.untis_lesson_subject,
    o1.untis_lesson_teacher,
    o1.untis_assigned_room
FROM (
SELECT
    w.day_number,
    lt.start_time,
    lt.end_time,
    tl.untis_lesson_subject,
    tl.untis_lesson_teacher,
    tr.untis_assigned_room
FROM
    timetable_lessons tl
INNER JOIN timetable_positions tp
    ON tp.timetable_lesson_id = tl.id
INNER JOIN timetable_rooms tr
    ON tr.timetable_position_id = tp.id
INNER JOIN timetable_classes tc
    ON tc.timetable_lesson_id = tl.id
    AND tc.class_level_number = 3
    AND tc.class_sign = 'ma'
INNER JOIN profile_positions pp
    ON pp.profile_definition_subject_code = tl.subject_code
    AND pp.person_id = 15789
    AND pp.period_school_year = 2015
    AND pp.period_fraction_name = 'HS'
RIGHT OUTER JOIN lesson_times lt
    ON lt.start_time = tp.untis_assigned_start_time
    AND tp.day = lt.weekday_id
    AND tl.period_school_year = 2015
    AND tl.period_fraction_name = 'HS'
    AND tp.week_number = 51
INNER JOIN weekdays w
    ON lt.weekday_id = w.id
EXCEPT
SELECT
    w.day_number,
    lt.start_time,
    lt.end_time,
    tl.untis_lesson_subject,
    tl.untis_lesson_teacher,
    tr.untis_assigned_room
FROM
    timetable_lessons tl
INNER JOIN timetable_positions tp
    ON tp.timetable_lesson_id = tl.id
INNER JOIN  timetable_rooms tr
    ON tr.timetable_position_id = tp.id
INNER JOIN timetable_classes tc
    ON tl.id = tc.timetable_lesson_id
    AND tc.class_level_number = 3
    AND tc.class_sign = 'ma'
INNER JOIN partial_class_courses pcc
    ON pcc.timetable_lesson_code = tl.untis_lesson_subject
INNER JOIN partial_class_course_members pccm
    ON pccm.partial_class_course_name = pcc.name
    AND pccm.partial_class_course_name IN
        (SELECT
            pccm.partial_class_course_name
        FROM
            partial_class_course_members pccm
        WHERE
            pccm.class_member_class_period_school_year = 2015
            AND pccm.class_member_class_period_fraction_name = 'HS'
            AND pccm.partial_class_course_class_course_class_level_number = 3
            AND pccm.partial_class_course_class_course_class_sign = 'ma'
        EXCEPT
        SELECT
            pccm.partial_class_course_name
        FROM
            partial_class_course_members pccm
        WHERE
            pccm.class_member_person_id = 15789
            AND pccm.class_member_class_period_school_year = 2015
            AND pccm.class_member_class_period_fraction_name = 'HS'
            AND pccm.partial_class_course_class_course_class_level_number = 3
            AND pccm.partial_class_course_class_course_class_sign = 'ma')
    AND pccm.partial_class_course_class_course_class_period_school_year = 2015
    AND pccm.partial_class_course_class_course_class_period_fraction_name = 'HS'
INNER JOIN lesson_times lt
    ON lt.start_time = tp.untis_assigned_start_time
    AND tp.day = lt.weekday_id
    AND tl.period_school_year = 2015
    AND tl.period_fraction_name = 'HS'
    AND tp.week_number = 51
INNER JOIN weekdays w
    ON lt.weekday_id = w.id) o1
RIGHT OUTER JOIN (
SELECT
    lt.start_time
FROM
    timetable_lessons tl
INNER JOIN timetable_positions tp
    ON tp.timetable_lesson_id = tl.id
INNER JOIN timetable_rooms tr
    ON tr.timetable_position_id = tp.id
INNER JOIN timetable_classes tc
    ON tc.timetable_lesson_id = tl.id
    AND tc.class_level_number = 3
    AND tc.class_sign = 'ma'
INNER JOIN profile_positions pp
    ON pp.profile_definition_subject_code = tl.subject_code
    AND pp.person_id = 15789
    AND pp.period_school_year = 2015
    AND pp.period_fraction_name = 'HS'
RIGHT OUTER JOIN lesson_times lt
    ON lt.start_time = tp.untis_assigned_start_time
    AND tp.day = lt.weekday_id
    AND tl.period_school_year = 2015
    AND tl.period_fraction_name = 'HS'
    AND tp.week_number = 51
INNER JOIN weekdays w
    ON lt.weekday_id = w.id) x
    ON x.start_time = o1.start_time 
ORDER BY
    o1.day_number ASC,
    o1.start_time ASC,
    o1.untis_lesson_subject ASC,
    o1.untis_lesson_teacher ASC;

[VolkerBarth · Accepted solution]

Given

• your observation that the right-hand side (alias x) of the (impressingly complex!) outer join does deliver more rows when run alone
• and the simple join condition "ON x.start_time = o1.start_time"

I would try to simplify the select list to just return just these two columns - that way you may be able to check whether all according rows are listed and which ones do have NULL on the left side (alias o1).

And as your real select list only returns DISTINCT rows from the NULL-supplying side o1, that may strip away some rows from the right-hand side, in case they have duplicate values or are all NULL - please note that DISTINCT treats two NULL values as identical.

---

(Note, I do not claim to understand that complex statement, it's just a distant observation...).

[Breck_Carter · Accepted solution]

You can (almost) always use raw HTML tags as well as the funky formatting thingies; e.g., B and /B instead of two asterisks.

In this case, I added PRE tags.

[VolkerBarth · Accepted solution]

Or, to put it another way, I'd suggest to add x.start_time to the final SELECT list to find out which result set rows would have duplicate or all-NULL values in the colums of derived table o1. - That would be the probably missing rows when x.start_time is not part of the final SELECT list.

[huber1 · Accepted solution]

We built up the statement once again from the beginning and it works now as expected. Thanks for all the helpful tips I got from you all - I very much appreciate it!

[VolkerBarth · Accepted solution]

Glad to hear that - feel free to check that answer as accepted, if you like:)