Guide to MySQL AND Operator

In this tutorial, we'll explore the use of the MySQL AND operator. This operator is used to combine multiple Boolean expressions, a vital tool in filtering data in SQL queries.

Introduction to MySQL AND Operator

MySQL uses numeric values to represent Boolean conditions, where zero represents FALSE and any non-zero value represents TRUE.

The AND operator combines two or more Boolean expressions, and the result is determined as follows:

• True (1): If both operands are non-zero and not NULL.
• False (0): If either operand is zero.
• NULL: If either operand is NULL, and the other is non-zero or both are NULL.

Basic Usage of AND Operator

Example 1: Basic AND Operations

Consider basic AND operations with numeric values:

SELECT 2 AND 3, 0 AND 5, 0 AND 0, 0 AND NULL;

Output Example:

+-------+-------+-------+----------+
| 2 AND 3 | 0 AND 5 | 0 AND 0 | 0 AND NULL |
+-------+-------+-------+----------+
|     1 |     0 |     0 |        0 |
+-------+-------+-------+----------+

Example 2: AND with NULL

When using AND with NULL:

SELECT 2 AND NULL, NULL AND NULL;

Output Example:

+----------+---------------+
| 2 AND NULL | NULL AND NULL |
+----------+---------------+
|     NULL |          NULL |
+----------+---------------+

Practical Use of AND Operator in Queries

Example 3: Filtering Data

Let's consider a table students with columns gradeLevel, age, and city.

To find students in 10th grade who are 15 years old:

SELECT name, gradeLevel, age
FROM students
WHERE gradeLevel = 10 AND age = 15;

Output Example:

+--------------+------------+-----+
| name         | gradeLevel | age |
+--------------+------------+-----+
| Alice Smith  |         10 |  15 |
| Bob Johnson  |         10 |  15 |
+--------------+------------+-----+

Example 4: Combining Multiple Conditions

To find students in 10th grade, aged 15, living in 'Springfield':

SELECT name, gradeLevel, age, city
FROM students
WHERE gradeLevel = 10 AND age = 15 AND city = 'Springfield';

Output Example:

+--------------+------------+-----+-------------+
| name         | gradeLevel | age | city        |
+--------------+------------+-----+-------------+
| Alice Smith  |         10 |  15 | Springfield |
+--------------+------------+-----+-------------+

Summary

• The AND operator is used to combine multiple Boolean conditions in SQL queries.
• It returns TRUE if all conditions are TRUE, and FALSE otherwise.
• It's commonly used in the WHERE clause to filter data based on multiple criteria.