find and solve || findandsolve.com
Please wait.....

SQL AND, OR and NOT Operators with Examples

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:

FirstNameLastNameAddressAge
SylviaNeupaneKathmandu10
RahulBhattaraiPokhara22
UbinaThapaMirmee23
SaurabRaiPokhara10
SylviaGurungButwal21


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

FirstNameLastNameAddressAge
RahulBhattaraiPokhara22


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

FirstNameLastNameAddressAge
RahulBhattaraiPokhara22
UbinaThapaMirmee23


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

FirstNameLastNameAddressAge
SylviaNeupaneKathmandu10
UbinaThapaMirmee23
SylviaGurungButwal21


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

FirstNameLastNameAddressAge
SylviaNeupaneKathmandu10
SylviaGurungButwal21


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

FirstNameLastNameAddressAge
RahulBhattaraiPokhara22
UbinaThapaMirmee23