Find the SQL query?? Find the name of students whose are not enrolled in any class

Answer 1

In attempting to answer this question without any information on the database schema, I'm going to make an assumption that there are two tables that would be used to retrieve this data. I'm also going to make the assumption that this query will include some sort of filter such as Semester (i.e. Students not enrolled in a class for Spring 2021). The tables including fields required to write the query for this question are:

Student

Id

Student_Name

Course

Id

Student_Id (this is the Id field from the Students Table)

Semester

Using this database schema example, the query could be written two different ways:

Option #1

SELECT Student_Name

FROM Student

WHERE Id NOT IN (SELECT Student_Id FROM Course WHERE Semester = 'Spring 2021')

Option #2

SELECT Student_Name

FROM Student S LEFT OUTER JOIN

Course C ON S.Id = C.Student_Id

WHERE Semester = 'Spring 2021'

AND C.Student_Id IS NULL

Although both queries would generate the same resultset, Option #2 is the better choice for performance reasons. Using the NOT IN operator is not ideal for scanning large tables.