SQL AND, OR, NOT – SQL Logical Operators

SQL provides logical operators which helps in filtering the result set based on some condition. SQL logical operators that we will be discussing are AND, OR and NOT. These three are the most commonly used logical operators in SQL queries.

SQL AND

SQL AND operator is used when we want to combine multiple conditions as part of the WHERE clause. The result set will be filtered based on satisfaction of both the condition. So, if both the conditions are true then only the result will be filtered. To combine multiple conditions, we can use more than one AND as part of the WHERE clause.

SQL single AND operator example

SQL AND operator syntax is:

Copy
SELECT column(s) FROM table_name WHERE condition1 AND condition2;

As mentioned in the syntax above for combining two conditions we can use one AND operator.

We will now try to understand one AND operator through some example.

Let’s consider the following Student table for example purpose.

Scenario: Get the percentage of students whose age is more than 12 years and gender is female.

Copy
SELECT StudentPercent FROM Student WHERE StudentAge>12 AND StudentGender = "F";

Output:

In the example above, we have used one AND operator to combine two conditions, StudentAge is greater than 12 and StudentGender is equal to “F”.

Let’s try to see an example for multiple AND operator.

SQL multiple AND operator example

Copy
SELECT column(s) FROM table_name WHERE condition1 AND condition2 AND condition3…AND conditionN;

As mentioned in the syntax above for combining more than two conditions we can use more than one AND operator.

We will now try to understand more than one AND operator through some example. Let’s consider the earlier defined Student table for example purpose.

Scenario: Get the percentage of students whose age is more than 12 years and gender are female and percentage is more than 80.

Copy
SELECT StudentPercent FROM Student WHERE StudentAge>12 AND StudentGender = "F" AND StudentPercent>80;

Output:

In the example above, we have used one AND operator to combine three conditions, StudentAge is greater than 12, StudentGender is equal to “F” and StudentPercent is greater than 80.

SQL OR Operator

OR operator is used when we want to combine multiple conditions as part of the WHERE clause. The result set will be filtered based on satisfaction of at least one of the conditions. So, if at least one of the conditions are true than only the result will be filtered. To combine multiple conditions, we can use more than one OR as part of the WHERE clause.

SQL single OR operator example

Syntax:

Copy
SELECT column(s) FROM table_name WHERE condition1 OR condition2;

As mentioned in the syntax above for combining two conditions we can use one OR operator.
We will now try to understand one OR operator through some example. Let’s reuse the earlier defined Student table for example purpose.

Scenario: Get the percentage of students whose age is more than 12 years or gender are female.

Query:

Copy
SELECT StudentPercent FROM Student WHERE StudentAge>12 OR StudentGender = "F";

Output:

In the example above, we have used one OR operator to combine two conditions, StudentAge is greater than 12 and StudentGender is equal to “F”.

Let’s try to understand for multiple OR operator.

SQL multiple OR operator example

Syntax:

Copy
SELECT column(s) FROM table_name WHERE condition1 OR condition2 OR condition3 ... OR conditionN;

As mentioned in the syntax above for combining more than two conditions we can use more than one OR operator.

We will now try to understand more than one OR operator through some example.

Scenario: Get the percentage of students whose age is more than 12 years or gender is female or percentage is more than 80.

Query:

Copy
SELECT StudentPercent FROM Student WHERE StudentAge>12 OR StudentGender = "F" OR StudentPercent>80;

In the example above, we have used one OR operator to combine three conditions, StudentAge is greater than 12, StudentGender is equal to “F” and StudentPercent is greater than 80.

SQL NOT Operator

SQL NOT operator is used when we want to filter result set when the condition is not satisfied in the WHERE clause.

Let’s try to understand NOT operator in detail with some examples.

SQL NOT operator example

Syntax:

Copy
SELECT column(s) FROM table_name WHERE NOT condition;

As mentioned in the syntax above we use NOT operator along with WHERE clause. We will now try to understand NOT operator through some example.

Scenario: Get the percentage of students whose gender is not female.

Query:

Copy
SELECT StudentPercent FROM Student WHERE NOT StudentGender = "F";

Output:

In the example above, we have used NOT operator to identify if the gender of the student is not female.

SQL Logical Operators Example

We have tried to understand the three logical operators individually. But, these three operators can also be used in combination. Let’s try to understand different combinations.

We will consider the below mentioned Employee table for example purpose for all the combinations.

SQL AND OR Example

Scenario: Get the name of male employees whose state is London or Texas.
Query:

Copy
SELECT EmpName FROM Employee WHERE EmpGender = "M" AND (EmpState = "London" OR EmpState = "Texas");

Output:

SQL AND NOT Example

Scenario: Get the name of the employee whose age is above 30 and gender is not male.
Query:

Copy
SELECT EmpName FROM Employee WHERE EmpAge > 30 AND NOT EmpGender = "M"; 

Output:

SQL OR NOT Example

Scenario: Get the name of the employee whose age is above 30 or gender is not male.
Query:

Copy
SELECT EmpName FROM Employee WHERE EmpAge > 30 OR NOT EmpGender = "M"; 

Output:

That’s all for SQL logical operators, I hope you got good enough ideas through examples of SQL AND, OR and NOT operators.

About Pankaj

I love Open Source technologies and writing about my experience about them is my passion. You can connect with me directly on Facebook, Twitter, and YouTube.