The AND and OR operators are used to filter records based on more than one condition: The AND operator displays a record when all the conditions are matched and TRUE. The OR operator displays a record if any of the conditions separated by OR is TRUE.
AND, OR, and NOT are logical operators in SQL Server. They help to combine the conditions used to filter records. They are most commonly used in conjunction with the WHERE or HAVING clauses.
The SQL AND, OR, and NOT Operators
The WHERE clause can be combined with AND, OR, and NOT operators.
The AND and OR operators are used to filter records based on multiple conditions:
- The AND operator displays a record if all the conditions separated by AND are TRUE.
- The OR operator displays a record if any of the conditions separated by OR is TRUE.
The NOT operator displays a record if the condition(s) is NOT TRUE.
SQL AND Syntax
SELECT <TableColumn1>, <TableColumn2>, … FROM <TableName> WHERE <Condition1 > AND <Condition2> AND <ConditionN>
When multiple conditions are combined using the AND operator, all rows which matched all of the given conditions will be returned.
SQL OR Syntax
SELECT <TableColumn1>, <TableColumn2>, … FROM <TableName> WHERE <Condition1 > OR <Condition2> OR <ConditionN>
SQL OR Syntax
SELECT <TableColumn1>, <TableColumn2>, ...FROM <TableName> WHERE NOT condition;
Employee Demo Table
The table below shows the complete "EmployeeInfo" table from the Employee sample Database:
| FirstName | LastName | Address | Age |
| Sylvia | Neupane | Kathmandu | 10 |
| Rahul | Bhattarai | Pokhara | 22 |
| Ubina | Thapa | Mirmee | 23 |
| Saurab | Rai | Pokhara | 10 |
| Sylvia | Gurung | Butwal | 21 |
SQL AND Example
The following SQL statement selects all fields from "EmployeeInfo" where FirstName "Rahul" AND Address "Pokhara"
SELECT * FROM EmployeeInfo WHERE FirstName='Rahul' AND Address='Pokhara';Output
| FirstName | LastName | Address | Age |
| Rahul | Bhattarai | Pokhara | 22 |
SQL OR Example
The following SQL statement selects all fields from "EmployeeInfo" where FirstName is "Rahul" OR "Ubina":
SELECT * FROM EmployeeInfo WHERE FirstName='Rahul' OR FirstName='Ubina';Output
| FirstName | LastName | Address | Age |
| Rahul | Bhattarai | Pokhara | 22 |
| Ubina | Thapa | Mirmee | 23 |
SQL NOT Example
The following SQL statement selects all fields from "EmployeeInfo" where the Address is NOT "Pokhara":
SELECT * FROM EmployeeInfo WHERE NOT Address='Pokhara';
Output
| FirstName | LastName | Address | Age |
| Sylvia | Neupane | Kathmandu | 10 |
| Ubina | Thapa | Mirmee | 23 |
| Sylvia | Gurung | Butwal | 21 |
Combining AND, OR, and NOT
We can also combine the AND, OR and NOT operators. The following SQL statement selects all fields from "EmployeeInfo" where "Sylvia" AND Address must be "Pokhara" OR "Kathmandu" (use parenthesis to form complex expressions):
SELECT * FROM EmployeeInfo WHERE FirstName='Sylvia' AND (Address='Kathmandu' OR Address='Butwal');Output
| FirstName | LastName | Address | Age |
| Sylvia | Neupane | Kathmandu | 10 |
| Sylvia | Gurung | Butwal | 21 |
The following SQL statement selects all fields from "EmployeeInfo" where FirstName is NOT "Sylvia" and NOT "Pokhara":
SELECT * FROM EmployeeInfo WHERE NOT FirstName='Sylvia' AND NOT Address='Pokhara';Output
| FirstName | LastName | Address | Age |
| Rahul | Bhattarai | Pokhara | 22 |
| Ubina | Thapa | Mirmee | 23 |