Sql query to find records not containing value – code example

SQL provides NOT IN (...) keyword to fetch records where column does not contain some values. In this article we will see few examples of different use cases.

First, let’s consider a table with 3 columns. We are taking 3 columns because we want to hold primary key, numeric column and string column.

ID	name	age
1	Scooby	14
2	Popeye	56
3	Mickey	6
4	Tom	8
5	Jerry	5

1. Not containing numeric values

If you have a collection of numbers and you want to select the records in which a column has no matching value from the collection, then use this query –

SELECT * FROM people WHERE age NOT IN (5, 56, 14, 15, 16)

/*
Output 👇 
 ____________________________________
|____ID_____|____name____|____age____|
|____3______|___Mickey___|_____6_____|
|____4______|____Tom_____|_____8_____|
*/

2. Not containing text values

Matching the text values is a bit complex because here we need to consider the text case and spellings. So, Mickey is not same as mickey. Also, you need to put the values under quotes "".

SELECT * FROM people WHERE name NOT IN ("Tom", "jerry", "Mickey", "Goofy")

/*
Output 👇 
 ____________________________________
|____ID_____|____name____|____age____|
|____1______|___Scooby___|____14_____|
|____2______|___Popeye___|____56_____|
|____5______|___Jerry____|_____5_____|
*/

3. Using NOT LIKE %...%

If you want to match all the strings containing a set of characters, like ey matches Popeye and Mickey, then you need to use LIKE "%ey%".

SELECT * FROM people WHERE name NOT LIKE "%ey%"

/*
Output 👇 
 ____________________________________
|____ID_____|____name____|____age____|
|____1______|___Scooby___|____14_____|
|____4______|____Tom_____|_____8_____|
|____5______|___Jerry____|_____5_____|
*/

If you got multiple values to check, then you need to create the query by concatenating AND NOT LIKE "%...%". I will show you a Php example of how to do it –

<?php
    $to_match = array("ey", "err", "oo", "mickey");
    $query = "SELECT * FROM people WHERE name LIKE '%" . implode("%' AND name LIKE '%", $to_match). "%'";
    echo $query;
?>  

/*
  Output 👇 

  SELECT * FROM people WHERE name LIKE '%ey%' AND name LIKE '%err%' AND name LIKE '%oo%' AND name LIKE '%mickey%'
*/

4. Using subquery

You can also use the subquery to match the column and select those records where column values are not matching the values returned by subquery.

For this, we need one more table. Let’s say we have a minor table. It holds ages which are considered minor, i.e. less than 16.

In real scenario, you don’t need table to hold minor ages because you can simply check using condition WHERE age > 16. But we are only considering this table to showcase subquery.

ID	age
1	4
2	5
3	6
4	8
5	12
6	14

SELECT * FROM people WHERE age NOT IN (
    SELECT age FROM minor
)

/*
Output 👇 
 ____________________________________
|____ID_____|____name____|____age____|
|____2______|___Popeye___|____56_____|

*/