SQL Server - Combine two select queries

Question

I have two tables, Semester1 and Semester2. Semester1:

StudentId	SubjectId
abc	sub1
def	sub1
ghi	sub1

Semester2:

StudentId	SubjectId
abc	changedSub1
def	sub1
ghi	changedSub2
newStudent1	sub2
newStudent2	sub3

I am trying to write a single Select statement such that it selects rows from Semester2 that have:

1. New StudentIds - i.e., StudentIds in Semester2 that are not in Semester1. So the result from this requirement should be Semester2's newStudent1 and newStudent2 rows.

AND

2. Changed SubjectIds - i.e., SubjectId are different for the same StudentId between Semester1 and Semester2. So the result from this requirement should be Semester2's changedSub1 and changedSub2 rows.

I have been able to write two separate queries to select the 2 requirements separately:

-- Part 1
SELECT * FROM Semester2
    WHERE StudentId NOT IN ( SELECT StudentId from Semester1 );

-- Part 2
SELECT Semester2.StudentId, Semester2.SubjectId 
FROM   Semester2
JOIN Semester1
  ON (Semester1.StudentId = Semester2.StudentId)
  WHERE Semester1.SubjectId <> Semester2.SubjectId;

How can I combine the two queries? Or if there is a better/easier/clearer way to write both requirements as a single query (without combining my above queries), how do I do that?

Answer 1

It looks like a single query with an outer join should suffice

select s2.*
from semester2 s2
left join semester1 s1 on s1.studentId = s2.studentId
where s1.studentId is null or s2.SubjectId != s1.SubjectId;

Answer 2

You could also do it in a single query using a join if UNION doesn't count for "single query":

SELECT s2.* 
  FROM Semester2 s2
       LEFT OUTER JOIN Semester1 s1
         ON s2.StudentId = s1.StudentId
         AND s2.SubjectId = s1.SubjectId
 WHERE s1.StudentId IS NULL;

The WHERE clause will make it so only results where there isn't a perfect match in Semester1 appear.

Answer 3

You might just need to extend your "Part 1" query a little. Right now it excludes all students from semester 1, but you only want to exclude students from semester 1 that do not have changed subjects in semester 2.

Something like this:

SELECT * FROM Semester2
WHERE StudentId NOT IN (-- Student ids with same subject from semester 1
                        SELECT StudentId FROM Semester1
                        WHERE Semester1.SubjectId = Semester2.SubjectId);

But I haven't tested it. Please let me know if I made some terrible mistake.

Answer 4

One option that requires no UNION (which requires scanning the table twice) and no OR condition (which can be slow) and no LEFT JOIN (which confuses the optimizer into thinking there will be multiple joined rows)

SELECT s2.*
FROM semester2 s
WHERE NOT EXISTS (SELECT 1
    FROM semester1 s1
    WHERE s1.studentId = s2.studentId
      AND s2.SubjectId = s1.SubjectId
);

Answer 5

Here's the simplest thing I can think of.

select Semester2.*
    from Semester2
        left outer join Semester1
            on Semester1.StudentId = Semester2.StudentId
    where NULLIF(Semester2.SubjectId,Semester1.SubjectId) is NOT NULL

NULLIF will return NULL if the two things are equal (same student had the same subject both semesters). Otherwise it returns Semester2.SubjectId. This excludes exactly what you want to exclude - students from Semester1 who didn't have a different subject in Semester2.