# How to use SQL `NOT`

SQL `NOT` is a logical operator that facilitates **efficient and precise filtering of search results by means of exclusion**. You can use the `NOT` operator to omit data that doesn’t fulfill a condition, limiting your results to relevant information.

## What is the SQL `NOT` operator?

Along with [SQL’s OR](https://www.ionos.com/digitalguide/server/configuration/sql-or/) and `AND`, `NOT` is one of the logical [operators in SQL](https://www.ionos.com/digitalguide/server/configuration/sql-operators/) that can be used to formulate conditions and criteria for data queries. While the operators `AND` and `OR` return the items from datasets that fulfill all or one of the conditions, **`NOT` is based on exclusion**. It omits from your search results all the information that *does not* fulfill a certain condition.

SQL `NOT` is often used with [SELECT](https://www.ionos.com/digitalguide/server/configuration/sql-select/), [WHERE](https://www.ionos.com/digitalguide/server/configuration/sql-where/) and SQL `HAVING`. You can **combine `NOT` with `AND` and `OR`** to make your search even more precise. That way you’ll filter out irrelevant results.

### What is the difference between SQL `NOT`, `AND` and `OR`

The main differences between SQL’s `NOT`, `AND` and `OR` can be summed up as follows:

<table>
  <thead>
    <tr>
      <th>SQL `NOT`</th>
      <th>SQL `AND`</th>
      <th>SQL `OR`</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>Only returns items that do not fulfill the indicated condition</td>
      <td>Returns items that fulfill all of the indicated conditions</td>
      <td>Returns items that fulfill at least one of the indicated conditions</td>
    </tr>
    <tr>
      <td>Negates a condition that isn’t relevant for the search</td>
      <td>Combines conditions in order to narrow the search to items that match multiple search criteria</td>
      <td>Narrows the search results to items that fulfill at least one of several criteria</td>
    </tr>
  </tbody>
</table>

Tip You want to dive deeper into [SQL](https://www.ionos.com/digitalguide/server/know-how/what-is-sql/) but are still a beginner? Then take a look at our [introduction to SQL with examples](https://www.ionos.com/digitalguide/server/configuration/sql-introduction-with-examples/).

## What is the syntax of SQL’s `NOT`?

The basic syntax of SQL’s `NOT` looks as follows:

```sql
SELECT  *
FROM table
WHERE  NOT  (searchcriteria)
```

The following parameters can be used with `NOT`:

- `SELECT`: Specifies which columns should be included in your search. You can use an asterisk `*` to search the entire table. You can also specify individual columns with their primary and foreign keys.
- `FROM`: Specifies the table that you want to search in.
- `WHERE`: Specifies the conditions you want to use to narrow your search. This is where you can use `NOT`.
- `NOT`: Combined with `WHERE` to specify which search results should not be included.

If you want to make your search results even more precise, you can also use `AND` and `OR`.

### SQL `NOT` combined with `AND`

You can combine `NOT` with `AND` to exclude results that fulfill two or more conditions.

```sql
SELECT  *
FROM  table
WHERE  NOT  (condition1  AND  condition2)
```

### SQL `NOT` combined with `OR`

You can combine `NOT` with `OR` to exclude results that fulfill **at least one condition**.

```sql
SELECT  *
FROM  table
WHERE  NOT  (condition1  OR  condition2)
```

## What is SQL `NOT` used for?

There are **countless uses** for SQL `NOT` in queries and searches. They include:

- Excluding data relating to customers, orders or products based on region, year, category, price or another characteristic
- Ignoring data that contains invalid or missing values
- Filtering finance data that doesn’t fulfill certain transaction criteria
- Excluding data for employees that aren’t in a certain department
- Filtering out applicants that don’t have certain qualifications

### Examples of SQL `NOT`

Searches with SQL `NOT` can vary widely depending on the use case. Deciding factors are how you define the condition with the `WHERE` clause, the `NOT` operator and other optional operators. Below we’ll show you **three examples**.

### Excluding employees based on region

Let’s say you’re working in a table called “Employees” and only want to see the employees that **don’t live in the US**. To do this, use the “Country” column, a `WHERE` clause, a `NOT` operator and the comparison operator `=`.

```sql
SELECT  *
FROM  Customers
WHERE  NOT  (Country  =  'USA')
```

Or if you wanted to see which **products are out of stock**, that would look similar:

```sql
SELECT  *
FROM  Products
WHERE  NOT  (Inventory  >  0)
```

### Filtering customers

In this example, we’ll search for a group of customers that **don’t fulfill certain criteria**. Let’s say you don’t want to see customers who are younger than 30 and live in New York. That would look as follows:

```sql
SELECT  *
FROM  Customers
WHERE  NOT  (Age  <  30  AND  City  =  'New York')
```

### Filtering out under-qualified applicants

Let’s say you want to exclude all applicants who don’t have a either bachelor’s degree or at least three years of working experience. That search would look as follows:

```sql
SELECT  *
FROM  Applicants
WHERE  NOT  (Degree  =  ´Bachelor´  OR  Experience  <  3)
```

## What are some alternatives to SQL `NOT`?

SQL `NOT` is the only operator that uses negation to filter search criteria. But since SQL `NOT` is a versatile SQL operator, it can be combined with various [SQL commands](https://www.ionos.com/digitalguide/server/configuration/sql-commands/) to carry out **precise queries**. Those commands include [SQL JOIN](https://www.ionos.com/digitalguide/server/configuration/sql-join/) and SQL `HAVING`-aggregate functions like [SQL SUM](https://www.ionos.com/digitalguide/server/configuration/sql-sum/) and `AVG`. It can also be combined with comparison operators like `=`, `>` and [SQL LIKE](https://www.ionos.com/digitalguide/server/configuration/sql-like/), and the logical operators `AND` and `OR`.


This is a markdown version of: [https://www.ionos.com/digitalguide/server/configuration/sql-not/](https://www.ionos.com/digitalguide/server/configuration/sql-not/) for AI/LLM consumption.