Boolean operators AND and OR in SQL

The AND and OR operators are used to combine multiple conditions to narrow the data in SQL statements. These two operators are called conjugate operators in SQL. AND and OR allow multiple comparisons with other operators in the same SQL statement.

In this SQL article, we will go to learn the math from AND and the OR operator, how to use and specific examples of them.

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 if you have AND you can add other conditions to make sure you get more accurate data. The retrieved data must satisfy all conditions contained in the WHERE clause at the same time.

Syntax of AND in WHERE:

 SELECT cot1, cot2, cotN 
 FROM TEN_BANG 
 WHERE [DIEU_KIEN1] AND [DIEU_KIEN2].AND [DIEU_KIENN]; 

Example of AND:

In this example we will take ID, TEN and LUONG from the NHANVIEN table, filtering employees with higher wages than 2000 and age less than 29.

This is NHANVIEN table:

Boolean operators AND and OR in SQL Picture 1

The statement will look like this:

 SQL> SELECT ID, TEN, LUONG FROM NHANVIEN WHERE LUONG > 2000 AND TUOI < 29; 

The result will look like this:

 Boolean operators AND and OR in SQL Picture 2 

OR operator in SQL

The OR operator is used to combine multiple conditions in the WHERE clause of the SQL statement. When using OR in WHERE in conjunction with SELECT, the retrieved data only needs to satisfy one of the listed conditions. Did you notice the difference between OR and AND?

Syntax of OR:

 SELECT cot1, cot2, cotN 
 FROM TEN_BANG 
 WHERE [DIEU_KIEN1] OR [DIEU_KIEN2].OR [DIEU_KIENN] 

You can combine N conditions using OR, and only one of these N conditions is correct, the data will be retrieved.

Example of OR:

We will take the ID, TEN and LUONG from the NHANVIEN table below, provided that LUONG is over 6500 and less than 24 years old.

Boolean operators AND and OR in SQL Picture 3

The statement will be:

 SQL> SELECT ID, TEN, LUONG FROM NHANVIEN WHERE LUONG > 6500 OR age < 24; 

When executing the above command, any record that satisfies the wage conditions higher than 6500 or less than 24 years will be returned in the results table, as shown below:

Boolean operators AND and OR in SQL Picture 4

Now, are you confident to use the AND and OR operators in the WHERE clause? It's quite simple, isn't it?

In the next article, we will learn about a very important command in SQL, which is the UPDATE command.

Next lesson: UPDATE command in SQL

Previous article: WHERE clause in SQL