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 and AND, NOT is one of the logical operators in SQL 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, 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:

SQL NOT SQL AND SQL OR
Only returns items that do not fulfill the indicated condition Returns items that fulfill all of the indicated conditions Returns items that fulfill at least one of the indicated conditions
Negates a condition that isn’t relevant for the search Combines conditions in order to narrow the search to items that match multiple search criteria Narrows the search results to items that fulfill at least one of several criteria
Tip

You want to dive deeper into SQL but are still a beginner? Then take a look at our introduction to SQL with examples.

What is the syntax of SQL’s NOT?

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

SELECT  *
FROM table
WHERE  NOT  (searchcriteria)
sql

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.

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

SQL NOT combined with OR

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

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

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
Tip

You’re looking for efficient data management with a secure, scalable database management system? SQL Server Hosting from IONOS provides servers and hosting suited to your individual needs.

VPS Hosting
Fully virtualised servers with root access
  • Unlimited traffic and up to 1 Gbit/s bandwidth
  • Fast SSD NVMe storage
  • Free Plesk Web Host Edition

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 UK. To do this, use the ‘Country’ column, a WHERE clause, a NOT operator and the comparison operator =.

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

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

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

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 Cardiff. That would look as follows:

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

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:

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

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 to carry out precise queries. Those commands include SQL JOIN and SQL HAVING-aggregate functions like SQL SUM and AVG. It can also be combined with comparison operators like =, > and SQL LIKE, and the logical operators AND and OR.

Was this article helpful?