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.

1. SQL AND Operator

SQL AND operator are used when we want to combine multiple conditions as part of the WHERE clause. The result set will be filtered based on the 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.

1.1) SQL single AND operator example

SQL AND operator syntax is:

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.

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.

1.2) SQL multiple AND operator example

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 the percentage is more than 80.

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.

2. 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.

2.1) SQL single OR operator example

Syntax:

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:

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.

2.2) SQL multiple OR operator example

Syntax:

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:

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.

3. 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.

3.1) SQL NOT operator example

Syntax:

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:

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.

4. SQL Logical Operators Combination 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.

4.1) SQL AND OR Example

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

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

Output:

4.2) SQL AND NOT Example

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

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

Output:

4.3) SQL OR NOT Example

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

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.