SQL AND OR NOT Clause

Read this article to understand the Where clause along with the AND, OR and NOT examples. We have shared few examples and we have shared basic as well as advance level common interview questions also.

In this post, I will provide you a short overview of Where clause with all AND, OR and NOT options. In addition, we have covered some questions which we receive from the audience.

First, let’s talk about the theory of Where clause and how to use AND, OR and NOT.

Definition

The Where Keyword is used to filter the data. Where clause can be used with Select, Update, Delete command. When we have to filter the data based on more conditions then we need logical operators like AND, OR and NOT.

Reasons to Use

As it’s name suggest we can use the Where clause to filter the data. Below are the some key areas where we need to use Where clause with AND, OR and NOT.

Retrieve all employees who work in a specific department and have a certain job title.
Show the products which has price range between 100 to 1000.
Retrieve all customers who are from a specific country OR have a certain age.
Retrieve all customers who do not have a certain membership level.

AND Clause

The AND clause is used to combine multiple conditions in a SQL query, and all conditions must be true for a row to be returned.

Syntax

SELECT column1, column2, columnN
FROM table_name
WHERE [condition] AND [condition2] AND [condition3 ]...;

The condition can be a combination of one or more column values compared to a specific value, a range of values, or other expressions using comparison operators such as = , < , > , <= , >= , <> (not equal), LIKE , IN , BETWEEN , IS NULL , IS NOT NULL .

Example

Example 1:

Lets see the graphical representation of the Where clause with AND. We have “Customers” table, having 5 records with few columns. Print some records which belongs to “USA” country and having age is greater than 25.

customer_id	first_name	last_name	age	country
1	John	Doe	31	USA
2	Robert	Luna	22	USA
3	David	Robinson	32	UK
4	John	Sinha	22	UK
5	James	Doe	19	INDIA

SELECT * FROM Customers
WHERE country = “USA” AND age > 25;

customer_id	first_name	last_name	age	country
1	John	Doe	31	USA

I hope above example clears the concept of Where clause with AND clause.

OR Clause

The OR clause is used to combine multiple conditions in a SQL query, and at least one condition must be true for a row to be returned.

Syntax

SELECT column1, column2, columnN
FROM table_name
WHERE [condition] OR [condition2] OR [condition3 ]...;

Example

Example 2:

Lets see the graphical representation of the Where clause with OR. We have “Customers” table, having 5 records with few columns. Print some records which belongs to either “USA” country OR having age is greater than 25.

customer_id	first_name	last_name	age	country
1	John	Doe	31	USA
2	Robert	Luna	22	USA
3	David	Robinson	32	UK
4	John	Sinha	22	UK
5	James	Doe	19	INDIA

SELECT * FROM Customers
WHERE country = “USA” OR age > 25;

customer_id	first_name	last_name	age	country
1	John	Doe	31	USA
2	Robert	Luna	22	USA
3	David	Robinson	32	UK

Why three records returned instead of one?

First two records satisfy the first condition that belongs to USA. The third records satisfy the second condition where age is greater than 25. Record number 4 and 5 skipped because they are not following any condition.

Or clause return the rows when either condition satisfies. I hope above example clears the concept of Where clause with OR clause.

NOT Clause

The NOT clause is used to negate a condition in a SQL query and it will return all the rows which are not matching the condition.

Syntax

SELECT column1, column2, columnN
FROM table_name
WHERE NOT condition;

Example

Example 3:

Lets see the graphical representation of the Where clause with NOT. We have “Customers” table, having 5 records with few columns. Print some records which not belongs to “USA”.

customer_id	first_name	last_name	age	country
1	John	Doe	31	USA
2	Robert	Luna	22	USA
3	David	Robinson	32	UK
4	John	Sinha	22	UK
5	James	Doe	19	INDIA

SELECT * FROM Customers
WHERE NOT country = “USA”;

customer_id	first_name	last_name	age	country
3	David	Robinson	32	UK
4	John	Sinha	22	UK
5	James	Doe	19	INDIA

Why three records returned instead of two?

First two records satisfy the condition that belongs to USA. NOT operator will negate the condition and skip those records only and print remaining not matched records. Lets see some complex examples with the combination of AND and OR with NOT.

Example 4:

Retrieve some records which NOT belongs to “USA” OR NOT belongs to UK. Show only the records whose age is NOT greater than 25.

Below are the important conditions which we can understood on problem statement.

NOT country = USA
NOT country = UK
NOT age > 25

Lets combine all the statement with the help of AND and OR clause.

customer_id	first_name	last_name	age	country
1	John	Doe	31	USA
2	Robert	Luna	22	USA
3	David	Robinson	32	UK
4	John	Sinha	22	UK
5	James	Doe	19	INDIA

SELECT * FROM Customers
WHERE NOT (country = ‘USA’ OR country = ‘UK’ OR age > 25) ;

SELECT * FROM Customers
WHERE NOT (country = ‘USA’ OR country = ‘UK’ ) AND age < 25 ;

customer_id	first_name	last_name	age	country
5	James	Doe	19	INDIA

First four records satisfy the condition that belongs to USA or UK. NOT operator will negate the condition and skip those records. For last record, age condition is satisfied and retrieved that records only. Both syntax we can use for mentioned problem. I hope above example clears the concept of Where clause with NOT clause.

Linking

Where clause is used in Select , Update, Delete statement. Lets go through this post for more details about the where clause. We can use where clause with AND, OR and NOT operators. We can use Where clause before Order By clause. Advance level Where having the Like , Between, IN keywords also. You can refer all the interview questions. Please refer all the links for understanding the Where with AND, OR and NOT in depth.