SQL Operators – AND, OR, NOT

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.

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.

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.

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.

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.

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.

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.

• PHP MySQL CRUD Tutorial For Beginners

Also, don’t forget to Subscribe to WTMatter!