This is a detailed guide on AND, OR, NOT SQL operators. Learn how to make use of AND, OR, NOT operators with SQL Where Clause.
Table of Contents
Use of SQL AND, OR, NOT Operators
In our last article on the SQL WHERE clause, we saw how we can fetch records according to a condition. In this article, we’ll find out how we can fetch records that satisfy more than one condition. To establish a relationship between different conditions, we make use of these AND, OR, NOT operators.
- All the conditions have to be TRUE if written in AND relationship for the overall WHERE clause to be TRUE.
- One of the conditions has to be TRUE if written in OR relationship for the overall WHERE clause to be TRUE.
- NOT is a special operator and used in WHERE clause when to fetch only those records which do not satisfy the condition. It fetched the records when the condition is NOT TRUE or FALSE.
Syntax
The syntax for SQL AND, OR, NOT operator is written below.
AND Syntax
SELECT col_1, col_2, …
FROM table_name
WHERE condition_1 AND condition_2 AND condition_3 …;
OR Syntax
SELECT col_1, col_2, …
FROM table_name
WHERE condition_1 OR condition_2 OR condition_3 …;
NOT Syntax
SELECT col_1, col_2, …
FROM table_name
WHERE NOT condition;
Merged Syntax
Any of the AND, OR, NOT operators can be used in a merged way, even all together.
SELECT col_1, col_2, …
FROM table_name
WHERE condition_1 AND (condition_2 OR condition_3) AND NOT condition_4;
Note. The above syntax is just a combination example. There are infinite possibilities for such combinations of multiple conditions.
SQL AND, OR, NOT Operator Examples
Individual examples are given below for each of the AND, OR, NOT operators used along with WHERE clause in SQL Select Statement. We’ll use the database table named as employees for demo purposes. The screenshot of the complete table data is given below as well.
AND Example
The following query having two conditions in the WHERE clause in AND relationship will select all the table rows WHERE description
is Hindi Teacher and dob
is 2001-04-05.
1 |
SELECT * FROM employees WHERE description = 'Hindi Teacher' AND dob = '2001-04-05' |
There are exactly two rows in the entire database table that satisfy both the conditions of the WHERE clause.
OR Example
The following query with two conditions in the WHERE clause of SQL select statement separated with OR operator will fetch all the table rows where either the description
is Hindi Teacher or Punjabi Teacher.
1 |
SELECT * FROM employees WHERE description = 'Hindi Teacher' OR description = 'Punjabi Teacher' |
So, there are four rows in our database table that satisfy one of the conditions in the WHERE clause.
NOT Example
Not is different from AND and OR. Instead of relating more than one condition, it simply fetches all the table rows WHERE the condition is NOT TRUE.
The following query fetches all the table records WHERE the first_name
column does not have value Manmeet.
1 |
SELECT * FROM employees WHERE NOT first_name = 'Manmeet' |
Combination Example
As mentioned earlier as well that you can combine any of these operators to form complex logic for the WHERE clause, here’s two of such examples given.
The following SQL query will fetch all the database table records WHERE the last_name
is Kaur and the description
is either Hindi Teacher or Punjabi Teacher.
1 2 |
SELECT * FROM employees WHERE last_name = 'Kaur' AND (description = 'Hindi Teacher' OR description = 'Punjabi Teacher'); |
The query given below is another example of the combination of the AND, OR, NOT operator in the SQL Where clause. It fetches all the records WHERE last_name
is Kaur but the type
is not 1.
1 |
SELECT * FROM employees WHERE last_name = 'Kaur' AND NOT type = 1 |
In the screenshot of the result, you can clearly see that it completely satisfies the above combination of WHERE statement logics. The type
column in the result does not contain the value 1 and both of the result set rows have the value Kaur for the column last_name
.
I hope you find this guide useful. If so, do share it with others who are willing to learn SQL. If you have any questions related to this article, feel free to ask them in the comments section.
Helpful Associated Guide.
Also, don’t forget to Subscribe to WTMatter!