SQL для получения кортежей несвязанных записей

Question

Представьте себе такую ​​базу данных:

И ищу всех студентов - наборы учителей, которые не разделяют никаких курсов. Результат должен быть (Student.Name - Teacher.Name) кортеж.

Эта идея близка к решению, но не совсем то, что хотел.

--- The students that go to some courses 
SELECT S.FIRSTNAME, S.LASTNAME
FROM STUDENTS S
JOIN STU_COU SC ON S.STUDENTID = SC.STUDENTS_STUDENTID

UNION ALL

--- teachers not in attended courses 
SELECT T.FIRSTNAME, T.LASTNAME
FROM TEA_COU TC
JOIN TEACHERS T ON T.TEACHERID = TC.TEACHERS_TEACHERID
WHERE TC.COURSES_COURSEID NOT IN (
  SELECT C.COURSEID
  FROM STUDENTS S
  JOIN STU_COU SC ON S.STUDENTID = SC.STUDENTS_STUDENTID
  JOIN COURSES C ON C.COURSEID = SC.COURSES_COURSEID
);

Тестовые данные, такие как:

• TEACHER A преподает курсы CS и MATH;
• СТУДЕНТ А идет на курс CS;
• СТУДЕНТ Б ходит на курсы ЛИТЕРАТУРЫ и СПОРТА;

Результат будет

STUDENT B - TEACHER A

Ищите универсальное решение, поэтому не указана конкретная база данных.

Answer 1

Вы можете начать с перекрестного соединения, а затем удалить любую пару, имеющую отношение:

SELECT     s.firstname, s.lastname, t.firstname, t.lastname
FROM       students s
CROSS JOIN teachers t
WHERE      NOT EXISTS (SELECT *
                       FROM   stu_cou sc
                       JOIN   tea_cou tc ON sc.courses_courseid = 
                                            tc.courses_courseid
                       WHERE  sc.students_studentid = s.studentid AND
                              tc.teachers_teacherid = t.teacherid)

Answer 2

В Oracle вы можете использовать minusили SQL Server или PostgreSQL except оператор set: (функционально эквивалентный)

select s.firstname as stud_fname,
       s.lastname  as stud_lname,
       t.firstname as teac_fname,
       t.lastname  as teac_lname
  from students s
 cross join teachers t
minus
select s.firstname,
       s.lastname,
       t.firstname,
       t.lastname
  from students s
  join stu_cou sc
    on s.studentid = sc.students_studentid
  join courses c
    on sc.courses_courseid
  join tea_cou tc
    on c.courseid = tc.courses_courseid
  join teachers t
    on tc.teachers_teacherid = t.teacherid

Answer 3

Это похоже на решение @Mureinik, но позволяет избежать CROSS JOIN:

SELECT     s.firstname, s.lastname, t.firstname, t.lastname
FROM       students s
JOIN teachers t ON NOT EXISTS (
    SELECT *
    FROM   stu_cou
    JOIN   tea_cou tc ON sc.courses_courseid = tc.courses_courseid
    WHERE  sc.students_studentid = s.studentid
    AND tc.teachers_teacherid = t.teacherid
    );

Answer 4

Вот решение, очень похожее на @Brian DeMilia, которое работает для MySQL:

SELECT
    s.firstname ,
    s.lastname ,
    t.firstname ,
    t.lastname  
from
    students s   
cross join
    teachers t  
where
    (
        s.firstname ,  s.lastname ,  t.firstname ,  t.lastname 
    ) NOT IN  (
        select
            s.firstname,
            s.lastname ,
            t.firstname,
            t.lastname  
        from
            students s  
        inner join stu_cou sc  
                on s.studentid = sc.students_studentid  
        inner join courses c  
                on sc.courses_courseid = c.courseid  
        inner join tea_cou tc  
                on c.courseid = tc.courses_courseid  
        inner join teachers t  
                on tc.teachers_teacherid = t.teacherid 
        );