Development, Web Development

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

Written by Anup Kumar
Posted on - 2 min read

Hi guys,

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 enrol) – 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 student takes admission with the course and sometimes not.

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

Result columns:

  1. Enrol
  2. First Name
  3. Last name

SQL – Answer 

Conditions we identified – Select students details 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 are not 1,2.

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

Then we can use it as subQuery in main Query – select enrollno,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: