Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

How to convert a FULL Join to a subquery in SQL?

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.

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

>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?

Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading