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.