“NOT IN” Operator explained in SQL in Hindi with example – SQL Interview Questions

I’m Anup and I asked this condition-based question in an interview for a SQL developer position at NexGen Innovators. If you are interested in joining our family you can apply from NexGen Careers.

SQL Question

There are two tables

Table 1. – Student master (id, first_name, last_name, and enroll) – for keeping details of students.

Table 2. – Student courses (id, stu_id, course) – stu_id is a foreign key of the student master ID.

Table Schema

+----------------+-------------+------+-----+---------+----------------+
| Field          | Type        | Null | Key | Default | Extra          |
+----------------+-------------+------+-----+---------+----------------+
| id             | int(11)     | NO   | PRI | NULL    | auto_increment |
| stu_id         | int(11)     | YES  |     | NULL    |                |
| course         | varchar(50) | YES  |     | NULL    |                |
| enroll_no_date | date        | NO   |     | NULL    |                |
+----------------+-------------+------+-----+---------+----------------+

But sometimes the student takes admitted to the course and sometimes not.

Now the admin wants a list of those students to result in having zero courses assigned.

Result columns:

1. Enroll
2. First Name
3. Last name

SQL – Answer

Conditions we identified – Select students’ details for those having zero courses in the course table.

Example Data in student master table (students_mst)

+----+------------+-----------+----------+
| id | first_name | last_name | enrollno |
+----+------------+-----------+----------+
|  1 | Rahul      | Sharma    | NEX001   |
|  2 | Mukesh     | Kumar     | NEX002   |
|  3 | Pooja      | Singh     | NEX003   |
+----+------------+-----------+----------+

Example Data in student courses table (stu_courses)

+----+--------+--------+----------------+
| id | stu_id | course | enroll_no_date |
+----+--------+--------+----------------+
|  1 |      1 | C++    | 2020-02-05     |
|  2 |      2 | PHP    | 2020-02-07     |
|  3 |      2 | C++    | 2020-02-15     |
+----+--------+--------+----------------+

Desired Result SQL

student_ids in course tables are 1,2 and we need those not 1,2.

We can get 1,2 by query “select stu_id from stu_courses“

Then we can use it as a subQuery in the main Query – select enroll,first_name, last_name from students_mst where id not in (subQuery)

select enrollno,first_name, last_name from students_mst where id not in (select stu_id from stu_courses);

Result

+----------+------------+-----------+
| enrollno | first_name | last_name |
+----------+------------+-----------+
| NEX003   | Pooja      | Singh     |
+----------+------------+-----------+

For better understanding you can also get the same example in the video: