SQL AND , OR and NOT Operators
AND Syntax & OR :
An SQL request may be restricted using the WHERE condition. THE AND and GOLD logic operators can be used within the WHERE command to combine conditions.
AND and OR operator use syntax :
Operators are added in the WHERE condition. They can be combined to infinity to filter the data as desired.
The AND operator ensures that the condition1 AND condition2 are true
SELECT nom_colonnes FROM nom_table WHERE condition1 AND condition2
Example :
SELECT *
FROM Article
WHERE Price>100 AND date>'2019';The OR operator verifies that the condition1 OR condition2 is true
SELECT nom_colonnes FROM nom_table WHERE condition1 OR condition2
Example :
SELECT *
FROM Article
WHERE Price>100 OR date>'2019'; Exercise :
In order to highlight the use of AND and OR commands, and quickly understand how these are working, we will use the customer table which contains the following columns and data for our various examples. The latter simulates the table that contains customers of any company.
| id | last Name | First Name | City | command | |
|---|---|---|---|---|---|
| 1 | Niss | jozira | Niss@example.com | Casablanca | NULL |
| 2 | bean | attar | attar@example.com | New York | 99 |
| 3 | jean | anicko | anickoy@example.com | Paris | 12 |
| 4 | ayoub | mariano | marianoh@example.com | Tokyo | NULL |
| 5 | anny | martines | martines@example.com | Berlin | 2 |
In SQL, the logical operator AND allows the display of a query to be conditioned according to several conditions. It is used as an ET. The LOGICAL and operator cannot be used without the WHERE command. Without adding to wait, a concrete example. We want to look in the table for customers who have ordered and who are in the city of Paris.
- SQL Command
SELECT * FROM clients WHERE ville = 'Paris' AND command IS NOT NULL;- SQL Result
| 3 | jean | anicko | anickoy@example.com | Paris | 12 |
- SQL Command
SELECT * FROM clients WHERE First_Name = 'jozira' AND command = 'NULL';- SQL Result
| 1 | Niss | jozira | Niss@example.com | Casablanca | NULL |
OR LOGIC OPERATOR :
In SQL, the LOGIC OPERATOR OR allows the display of a query to be conditioned according to several conditions. It is used as a OR. The LOGIC OPERATOR CANNOT be used without the WHERE command. Without adding to wait, a concrete example. We want to look in the table for customers who have not ordered or who live in the city of Berlin.
- SQL Command
SELECT * FROM clients WHERE ville = 'tokyo' OR commandes IS NULL;
- SQL Result
| 1 | Niss | jozira | Niss@example.com | Casablanca | NULL |
| 2 | bean | attar | attar@example.com | New York | 99 |
Important :
The WHERE clause can be combined with AND, OR, and NOT operators.
The AND and OR operators are used to filter records based on more than one condition:
- 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.
COMBINE AND & GOLD :
In SQL, it is promoted to actually consider building queries buildings that combine logical operators AND and GOLD. When we take over the customers table, let's say that we want to get the customers of Paris who ordered, and the customers of Lyon who ordered in addition to once.
Exercise : SELECT * FROM clients WHERE (ville = 'tokyo' AND command > 1) OR (ville = 'Paris' AND command IS NOT NULL);
Result :
| 3 | jean | anicko | anickoy@example.com | Paris | 12 |
0 Comments