SQL AND and OR Operators

An SQL query can be limited using the WHERE condition. The logical operators AND and OR can be used within the WHERE statement to combine conditions.
 

Syntax for using the AND and OR operators

The operators are to be added in the WHERE condition. They can be combined endlessly to filter the data as desired.

The AND operator ensures that condition1 AND condition2 are true.

SELECT column_name
FROM table_name
WHERE condition1 AND condition2

The OR operator checks that condition1 OR condition2 is true:

SELECT column_name FROM table_name
 WHERE condition1 OR condition2

These operators can be combined and mixed. The example below filters the results of the table “table_name” if condition1 AND condition2 OR condition3 is true:

SELECT column_name FROM table_name
 WHERE condition1 AND (condition2 OR condition3)

 

 

Data example:

To illustrate the next commands, we will consider the following “product” table:

+------+----------+------------+-------+---------+
|  id  |   name   | category   | stock |  price  |
+------+----------+------------+-------+---------+
|  101 | RAM      | computer   |  6    | 850     |
|  102 | Keyboard | computer   |  37   | 40      |
|  103 | Mouse    | computer   |  17   | 30      |
|  104 | Pencil   | fourniture |  148  | 2       |
|  105 | Gum      | fourniture |  250  | 1       |
+------+----------+------------+-------+---------+

 

AND operator:

The AND operator allows joining several conditions in a query. In the following example, we filter only the computer products that are almost out of stock (less than 20 products available):

SELECT * FROM product
WHERE category = 'computer' AND stock < 20

This query returns the following results:

+------+----------+------------+-------+---------+
|  id  |   name   | category   | stock |  price  |
+------+----------+------------+-------+---------+
|  101 | RAM      | computer   |  6    | 850     |
|  103 | Mouse    | computer   |  17   | 30      |
+------+----------+------------+-------+---------+

 

 

OR operator:

To filter the data to have only “Mouse” or “Keyboard” products it is necessary to run the following query:

SELECT * FROM product
WHERE name = 'mouse' OR name = 'Keyboard'

This query returns the following results:

+------+----------+------------+-------+---------+
|  id  |   name   | category   | stock |  price  |
+------+----------+------------+-------+---------+
|  102 | Keyboard | computer   |  37   | 40      |
|  103 | Mouse    | computer   |  17   | 30      |
+------+----------+------------+-------+---------+

 

 

Combine AND and OR operator:

Don’t forget that the operators can be combined to perform powerful searches. It is possible to filter computer products with a stock lower than 20 and furniture products with a stock lower than 200 with the following query:

SELECT * FROM product
WHERE ( category = 'computer' AND stock < 20 )
OR ( category = 'furniture' AND stock < 200 )

This query returns the following results:

+------+----------+------------+-------+---------+
|  id  |   name   | category   | stock |  price  |
+------+----------+------------+-------+---------+
|  101 | RAM      | computer   |  6    | 850     |
|  103 | Mouse    | computer   |  17   | 30      |
|  104 | Pencil   | fourniture |  148  | 2       |
+------+----------+------------+-------+---------+

MCQPractice competitive and technical Multiple Choice Questions and Answers (MCQs) with simple and logical explanations to prepare for tests and interviews.Read More