May 16, 2021 SQL
3. An instance of the AND operator
4. An instance of the OR operator
5. An instance of the NOT operator
The AND&OR operator is used to filter records based on more than one condition, i.e. to combine multiple conditions to reduce the data in sql statements.
Where clauses can be used in conjunction with AND, OR, and NOT operators.
The AND and OR operators are used to filter records based on multiple criteria:
If the condition is not TRUE, the NOT operator displays the record.
SELECT column1, column2, ...
FROM table_name
WHERE condition1 AND condition2 AND condition3 ...;
SELECT column1, column2, ...
FROM table_name
WHERE condition1 OR condition2 OR condition3 ...;
SELECT column1, column2, ...
FROM table_name
WHERE NOT condition;
In this tutorial, we'll use the famous Northwind sample database.
Here's the data from the Customers table:
CustomerID | CustomerName | ContactName | Address | City | PostalCode | Country |
---|---|---|---|---|---|---|
1
|
Alfreds Futterkiste | Maria Anders | Obere Str. 57 | Berlin | 12209 | Germany |
2 | Ana Trujillo Emparedados y helados | Ana Trujillo | Avda. de la Constitución 2222 | México D.F. | 05021 | Mexico |
3 | Antonio Moreno Taquería | Antonio Moreno | Mataderos 2312 | México D.F. | 05023 | Mexico |
4
|
Around the Horn | Thomas Hardy | 120 Hanover Sq. | London | WA1 1DP | UK |
5 | Berglunds snabbköp | Christina Berglund | Berguvsvägen 8 | Luleå | S-958 22 | Sweden |
The following SQL statement selects all customers whose country is "Germany" and whose city is "Berlin" from the "Customers" table:
The following SQL statement selects all the fields of "Customers" for the city of "Berlin" or "München":
The following SQL statement selects all fields in a country that is not "Customers" for "Germany":
SELECT * FROM Customers
WHERE NOT Country='Germany';
You can also combine AND and OR (using parentheses to form complex expressions).
The following SQL statement selects all customers from the "Customers" table of the country "Germany" and the city is "Berlin" or "München":
You can also combine AND, OR and NOT operators.
The following SQL statement selects all fields for a country that is a "customer" of "Germany" and the city must be "Berlin" or "Munich" (a complex expression in parentheses):
Code example:
SELECT * FROM Customers
WHERE Country='Germany' AND (City='Berlin' OR City='München');
The following SQL statement selects all fields from countries that are not "Germany" and are not "USA":
Code example:
SELECT * FROM Customers
WHERE NOT Country='Germany' AND NOT Country='USA';
Now, take a look at how well you know "SQL AND, OR and NOT (with, or not operators)" by using the following topics!
SQL AND, OR operators: In this section of the quiz, you will practice using SQL's AND, OR operators.
Note: The above tests are paid and premium VIP is free of charge
For more questions, please refer to: SQL Quiz Library