SQL IN and NOT IN Operators
SQL IN and NOT IN operators used to specify multiple values in a WHERE clause.
SQL IN condition used to allow multiple value in a WHERE clause condition. SQL IN condition you can use when you need to use multiple OR condition.
SQL IN condition allow only specific value in INSERT, UPDATE, DELETE, SELECT statement.
Syntax
WHERE column_name IN (value1, value2, ...);
Example
SQL> SELECT * FROM users_info WHERE no IN (1,5,10);
NO NAME ADDRESS CONTACT_NO
---- -------------------- ------------------------ --------------------
1 Opal Kole 63 street Ct. 000-444-7847
5 Ken Myer 137 Clay Road 000-444-7528
10 Sariya Vargas 145 Taxo court. 000-444-5927
3 rows selected.
SQL NOT IN Condition Statement
SQL NOT IN condition used to exclude the defined multiple value in a WHERE clause condition. SQL NOT IN condition also identify by NOT operator.
Syntax
NOT IN condition use with WHERE clause to exclude defined multiple values from record data.
WHERE column_name NOT IN (value1, value2, ...);
SQL NOT condition used with BETWEEN condition or LIKE condition.
NOT BETWEEN condition;
NOT LIKE condition
SQL WHERE clause with NOT IN Example
SQL> SELECT * FROM users_info WHERE no NOT IN (1,3,5,7,9);
NO NAME ADDRESS CONTACT_NO
---- -------------------- ------------------------ --------------
2 Max Miller 41 NEW ROAD. 000-444-8292
4 Paul Singh 1343 Prospect St 000-444-7585
6 Jack Evans 1365 Grove Way 000-444-8401
8 Gabe Hee 1220 Dallas Drive 000-444-5028
10 Sariya Vargas 145 Taxo court. 000-444-5927
5 rows selected.
SQL BETWEEN with NOT Example
Our users_info table we have to select all rows with add condition exclude no column range from 6 to 10.
SQL> SELECT * FROM users_info WHERE no NOT BETWEEN 6 AND 10;
NO NAME ADDRESS CONTACT_NO BIRTH_DATE
--- --------------------- ------------------------- --------------- -------------
1 Opal Kole 63 street Ct. 000-444-7847 1984-05-07
2 Max Miller 41 NEW ROAD. 000-444-8292 1987-07-07
3 Beccaa Moss 2500 green city. 000-444-7586 1992-01-15
4 Paul Singh 1343 Prospect St 000-444-7585 1998-07-25
5 Ken Myer 137 Clay Road 000-444-7528 2002-04-07
5 rows selected.
SQL LIKE with NOT Example
In our users_info table not select whose name column starting with 'Be' character.
SQL> SELECT * FROM users_info WHERE name NOT LIKE 'Be%';
NO NAME ADDRESS CONTACT_NO
--- --------------------- ------------------------------- ---------------
1 Opal Kole 63 street Ct. 000-444-7847
2 Max Miller 41 NEW ROAD. 000-444-8292
4 Paul Singh 1343 Prospect St 000-444-7585
5 Ken Myer 137 Clay Road 000-444-7528
6 Jack Evans 1365 Grove Way 000-444-8401
7 Reed Koch 1274 West Street 000-444-5228
8 Gabe Hee 1220 Dallas Drive 000-444-5028
10 Sariya Vargas 145 Taxo court. 000-444-5927
8 rows selected.