SQL IN statement can be used with where clause to list a set of matching records
of a table. We can use SQL in query with NOT combination also to filter
out some records. This is our table
id
name
class
mark
1
John Deo
Four
75
2
Max Ruin
Three
85
3
Arnold
Three
55
4
Krish Star
Four
60
5
John Mike
Four
60
6
Alex John
Four
55
7
My John Rob
Fifth
78
We will apply in command to display records of class Four and Fifth only.
SELECT * FROM `student` WHERE class IN ('Four','fifth')
id
name
class
mark
1
John Deo
Four
75
4
Krish Star
Four
60
5
John Mike
Four
60
6
Alex John
Four
55
7
My John Rob
Fifth
78
Here above the sql in query returns the records of class Four and Fifth
only. We can apply NOT IN sql query also like below to remove some records
in display.
SELECT * FROM `student` WHERE class not IN ('Four','fifth')
id
name
class
mark
2
Max Ruin
Three
85
3
Arnold
Three
55
We can link or collect data related to two different tables by using IN command. For example we have another sports table where we have the list of students joined in sports. We need not keep all the columns lime name , class etc. We can only keep the id field. So our student can be collected from main table. Here is the query which will display only students joined in sports
Select * from student where id in ( select * from student_sports)