SQL query to select all teachers not in different table For homework I had to do the following:


  Give a list of all the teachers that don't have a classroom assigned to them.


It involves the following 2 tables:





Code inside Groups table would be the classroom in this case and TeacherId matches the Id inside Teachers table.

My solution to the problem is the following query:

SELECT Id, FirstName, MiddleName, LastName
FROM Teachers
WHERE Id NOT IN (SELECT TeacherId FROM Groups)


It works perfectly, however, I wonder if there was a better solution using JOINs.

Edit:

I should mention there is a CONSTRAINT on the Groups table:

ALTER TABLE Groups
ADD CONSTRAINT [FK_Groups_Teachers]
FOREIGN KEY (TeacherId)
REFERENCES [Teachers] ([Id])
Your query using NOT IN is good, but you can also use LEFT JOIN and keep only the teachers who don't have Groups:

SELECT t.Id, FirstName, MiddleName, LastName
FROM Teachers t LEFT JOIN Groups g ON t.Id=g.TeacherId
WHERE g.TeacherId IS NULL