The AND and OR operators are used to combine multiple conditions to narrow down the data in an SQL statement. These two operators are called conjugate operators in SQL. AND and OR allow for multiple comparisons with other operators within the same SQL statement.
The AND and OR operators are used to combine multiple conditions to narrow down the data in an SQL statement. These two operators are called conjugate operators in SQL . AND and OR allow for multiple comparisons along with other operators in a single SQL statement.
In this SQL lesson, we will explore the AND/OR and NOT operators - how to use them, as well as specific examples.
ANDThe and operators ORare used to filter records based on more than one condition:
- The AND operator
ANDdisplays a record if all conditions separated by AND are TRUE. - The operator
ORdisplays a record if any of the conditions separated by OR are TRUE.
The operator NOTdisplays a record if the condition(s) are NOT TRUE.
The AND operator in SQL
AND allows the use of multiple conditions in the WHERE clause of an SQL statement. As you saw in the previous lesson, we can only use one condition in WHERE when combined with SELECT . But with AND, you can add other conditions to ensure more accurate data retrieval. The retrieved data must simultaneously satisfy all the conditions in the WHERE clause.
Syntax of AND in WHERE:
SELECT cot1, cot2, cotN FROM TEN_BANG WHERE [DIEU_KIEN1] AND [DIEU_KIEN2].AND [DIEU_KIENN];Examples of AND:
In this example, we will retrieve employee information from the EMPLOYEE table, filtering out employees whose salary is higher than 2000 and who are younger than 29.
This is the EMPLOYEE table:
The command will be as follows:
SQL> SELECT * FROM NHANVIEN WHERE LUONG > 2000 AND TUOI < 29;The result will look like this:
The OR operator in SQL
The OR operator is used to combine multiple conditions in the WHERE clause of an SQL statement. When using OR in the WHERE clause in conjunction with SELECT, the retrieved data only needs to satisfy one of the listed conditions. Do you notice the difference between OR and AND?
Syntax of OR in WHERE:
SELECT cot1, cot2, cotN FROM TEN_BANG WHERE [DIEU_KIEN1] OR [DIEU_KIEN2].OR [DIEU_KIENN]You can combine N conditions using OR, and if just one of these N conditions is true, the data will be retrieved.
Examples of OR:
We will retrieve employee information from the EMPLOYEE table below, provided that the SALARY is greater than 6500 and the age is less than 24.
The command would be:
SQL> SELECT * FROM NHANVIEN WHERE LUONG > 6500 OR age < 24;When executing the above command, any records that meet the conditions of having a salary higher than 6500 or being under 24 years old will be returned in the results table, as shown below:
The NOT operator in SQL
The NOT operator is used to negate the conditions that follow it in the WHERE clause. When using NOT in a SELECT statement combined with a WHERE clause, you will retrieve data that does NOT satisfy the condition.
Syntax of NOT in WHERE:
SELECT cot1, cot2,. cotN FROM TEN_BANG WHERE NOT [DIEU_KIEN]You can combine the NOT operator with AND/OR when necessary.
Examples of NOT:
We will retrieve employee information from the EMPLOYEE table below, provided that the SALARY is not less than 4000 and the age is 24 or greater.
The command would be:
SQL> SELECT * FROM NHANVIEN WHERE NOT LUONG < 4000 AND age >= 24;After executing the command, anyone with a salary higher than 4000 and aged 24 or older will be selected, with the following results:
+----+----------+-----+-----------+---------+ | ID | TEN | TUOI| DIACHI | LUONG | +----+----------+-----+-----------+---------+ | 4 | Mạnh | 29 | Hue | 6500.00 | | 5 | Huy | 28 | Hatinh | 8500.00 | +----+----------+-----+-----------+---------+By now, are you confident in using the AND, OR, and NOT operators in the WHERE clause? It's quite simple, isn't it? In the next lesson, we'll learn about a very important SQL statement: the UPDATE statement.