I have a SQL query as follows:
SELECT DISTINCT FirstName, LastName
FROM Students FULL JOIN
AssignmentSubmissions
ON Students.Id = AssignmentSubmissions.StudentId
WHERE AssignmentSubmissions.StudentId IS NULL;
I a trying to figure out a way to form an equivalent subquery. But since the comparison operator is NULL here for AssignmentSubmissions.StudentId I am not able to come up with a solution.
>Solution :
a) Not sure why you’ve got a FULL JOIN here rather than LEFT OUTER JOIN – it appears that a LEFT OUTER JOIN is more appropriate.
SELECT DISTINCT FirstName, LastName
FROM Students
LEFT OUTER JOIN AssignmentSubmissions
ON Students.Id = AssignmentSubmissions.StudentId
WHERE AssignmentSubmissions.StudentId IS NULL;
b) If you don’t want to use a JOIN, you can use a WHERE clause e.g.,
SELECT DISTINCT FirstName, LastName
FROM Students
WHERE Students.Id NOT IN (SELECT StudentId FROM AssignmentSubmissions);
However, this doesn’t really save you anything (it may even have the same execution plan) – why do it?