SQL WHERE AND, OR, NOT Operators and How to Use or in SQL

Use or in SQLIn this article, you will learn SQL WHERE AND, NOT, OR in SQL Operator, and How to use or in SQL with an example, let,s discussed in detail. therefore, you will learn AND condition, OR condition, NOT condition with WHERE Clause in SQL Server.

Introduction:

The WHERE clause can be combined with AND, NOT, and OR in SQL operators.
The AND and OR in SQL operators are used to filter records, Therefore filters records on the basis of more than one condition:
The AND the operator in SQL displays a record if all the conditions separated by AND are TRUE.
The OR in SQL operator displays a record if any of the conditions separated by OR is TRUE.
NOT the operator in SQL Server displays a record if the condition(s) are NOT TRUE.

.
Note: The SQL AND condition and OR condition can be combined to test for multiple conditions in an INSERT, UPDATE, DELETE, and SELECT statement.
The SQL OR and AND conditions, its allow you to test the multiple conditions.
Don’t neglect the order of operation parentheses!
These two AND and OR operators are called conjunctive operators

General Syntax:

A WHERE clause with AND:

The WHERE clause with OR:

WHERE clause with NOT:

Sample Demo Table: EmployeeDetails

The given below Demo table below shows the complete "EmployeeDetails" table sample database:

EmpId EmpName EmpFather Education City Country Pincode
101 James Hudson Liam Isaiah Graduate degree Barbados Bridgetown 226015
102 William Josiah Noah Charles Master Degree Belgium Brussels 236014
103 Oliver Christian Benjamin Hunter Bachelors Costa Rica San Jose 226010
104 Lucas Eli Elijah Connor B.ED Croatia Zagreb 226012
105 Logan Aaron Mason Ezra Master Degree Cyprus Nicosia 226014
106 Ethan Adrian Alexander Landon Management Dominica Roseau 226016
107 Michael Nolan Jacob Jonathan Bachelors France Paris 226020
108 Henry Easton Daniel Jeremiah High School Gabon Libreville 225678
109 Sebastian Colton Jackson Elias Graduate Degree Georgia Tbilisi 227834
110 John Greyson Owen Dominic Management Greece Athens 236056
111 Luke Austin Jack Adam InterMediate Guyana Georgetown 236011
112 Grayson Jordan Dylan Santiago Graduate Degree Haiti Port au Prince 236013
113 Julian Evan Gabriel Roman Master Degree Honduras Tegucigalpa 236017
114 Anthony Xavier Mateo Ezekiel Graduate degree Hungary Budapest 236014
115 Lincoln Jace Jaxon Jose Management Iceland Jakarta 442310
116 Christopher Leonardo Joshua Jameson Management Iraq Baghdad 442313
117 Theodore Axel Andrew Bryson Bachelors Italy Rome 442315
118 Ryan parker Caleb Everett InterMediate Kiribati Tarawa Atoll 442317
119 Nathan Miles Asher Kayden Graduate Degree Kyrgyzstan Bishkek 446778
120 Leo Jason Thomas sawyer Management Liberia Monrovia 459867

AND Example

There are the given below following SQL statement selects all fields from "EmployeeDetails" the table where the country is “San Jose” AND the city is “Costa Rica”:


AND Example output

OR Example

Furthermore, there are the given below following SQL statement selects all fields from "EmployeeDetails" the table where the city is “France” OR “Iceland”:


OR in SQL output
Therefore, the given below following SQL statement selects all fields from "EmployeeDetails" the table where the country is “Bridgetown” OR “Georgetown”:


OR condition Output

NOT Example

Furthermore, There are the given below following SQL statement selects all fields from "EmployeeDetails" the table where the country is NOT “Germany”:


NOT Operator Output
As a result, you can see in the output Result screenshot, there are the Nineteen (19) records show, as you see the above given EmployeeDetails table, there are Twenty(20) records.

Sample Demo Table: Suppliers

SupplierId SupplierName City State Pincode
201 James Hudson Barbados Washington 226015
202 William Josiah Redwood City California 236014
203 Oliver Christian Mountain View California 226010
204 Elijah Connor Croatia Zagreb 226012
205 Logan Aaron Racine Wisconsin 226014

Example: Using Combine AND, NOT and OR in SQL Operator

therefore, in this example, you will see how you can use combine the AND, OR and NOT operators.
There are the given below the following SQL statement selects all fields from "Suppliers" where the State is “Germany” AND city must be “Mountain View” OR “Redwood City” (therefore, use the parenthesis to form complex expressions):


OR in SQL output
Furthermore, There are the given below following SQL statement selects all fields from "Suppliers" where the state is NOT “Zagreb” and NOT “Wisconsin”:


Combine operator OR in SQL

Example: Using AND and OR Conditions with the SELECT Statement

Therefore, let’s look at another example, in the given below statement on how to use the AND condition and OR condition together in a SELECT statement.

As a result, there will be two(2) records selected. furthermore, these are the results that you should see in the screenshot:

AND and OR Condition
Explanation: therefore, in this example would return all Suppliers that are in the State of California but do not have a SupplierId equal to 203. furthermore, the query will also return all Suppliers whose SupplierId is equal to 205. therefore, the parentheses determine the order that the AND condition and OR condition are evaluated.

Example: Using the AND and OR Conditions with the UPDATE Statement

Next, therefore, let’s see the example of how to use the AND and OR conditions in an UPDATE statement. furthermore, In this example, we used the Suppliers table with the following data:

As per the UPDATE result, there will be two(2) records updated. furthermore, Select the data from the Suppliers table again:


AND OR in SQL UPDATE Condition
SQL Query Explanation: As per the result you can see that this example would update all State values in the Suppliers table to ‘Zagreb’ where the SupplierId is equal to 203 as well as those records where the SupplierId is greater than 203 and therefore, the SupplierName is not equal to ‘Elijah Connor’. Furthermore, As you can see, the State value in the 3rd row and 5th row has been updated. furthermore, you can see given below screenshot output.

Example: Using the AND and OR Conditions with the DELETE Statement

Furthermore, let’s see the example of how to combine the AND and OR conditions to delete records using the DELETE statement.
therefore, in this example, we have used a table called Suppliers with the following data:

As per the result, there will be two(2) records deleted. furthermore, Select the data from the Suppliers table again:

Furthermore, you can see the results as per SQL query:

AND OR in SQL DELETE Condition
Code Explanation: As per the result, This example would delete all records from the Suppliers table where the SupplierId is equal to 205. furthermore, It would also delete all records from the Suppliers table where the Supplier Id is less than 202 and the SupplierName is not equal to ‘William Josiah’.

Conclusion:

In this tutorial, you have learned how to use the AND, NOT, OR in SQL Operator and also learned AND condition, OR condition, NOT condition with WHERE Clause in SQL Server with multiple combined conditions. therefore, I hope you will enjoy this article!

About Author

admin