MySQL AND Operator With Examples

Sometimes you may need to work with Boolean type of columns and use AND/OR operators to combine their values. In MySQL, Boolean values are stored as 0 for false and 1 for true. There is no explicit Boolean field. But MySQL AND Operator is logical operator that allows you to easily combine two or more Boolean values or columns and returns 1, 0, Null. In this article, we will learn about MySQL AND operator with examples.

MySQL AND Operator With Examples

Here is the basic syntax of AND operator in MySQL.

A AND B

In the above expression, operands A and B can be literal values of expressions. The AND operator returns 1 if both A and B are not 0. It returns 0 if either of them is 0. Otherwise, it returns NULL.

Here is an example to use logical AND operator with literal values.

mysql> SELECT 1 AND 1;
+---------+
| 1 AND 1 |
+---------+
|       1 |
+---------+

Similarly, you can use AND operator with multiple combinations of literal values, as shown in the following example.

mysql> SELECT 1 AND 0, 0 AND 1, 0 AND 0, 0 AND NULL;
+---------+---------+---------+------------+
| 1 AND 0 | 0 AND 1 | 0 AND 0 | 0 AND NULL |
+---------+---------+---------+------------+
|       0 |       0 |       0 |          0 |
+---------+---------+---------+------------+

Please note, AND operator will return 0 if either of the operands is 0 as shown above.

On the other hand, it will return null if either of the operands is NULL and the other operand is non-zero.

mysql> SELECT 1 AND NULL, NULL AND NULL;
+------------+---------------+
| 1 AND NULL | NULL AND NULL |
+------------+---------------+
|       NULL |          NULL |
+------------+---------------+

Here is a handy table to give you an idea of the results of combining true, false, and null values.

	TRUE	FALSE	NULL
TRUE	TRUE	FALSE	NULL
FALSE	FALSE	FALSE	FALSE
NULL	NULL	FALSE	NULL

In the above examples, we have used AND operator in SELECT statement’s columns. You can also use AND operator in WHERE clause of SELECT, UPDATE, DELETE statements. Similarly, you can also use AND operator in INNER JOIN and LEFT JOINs.

Please note, MySQL evaluates AND expressions from left to right until the result it not determined. if your MySQL query has a long logical expression containing multiple AND operators, then MySQL stops evaluating the expression the moment it arrives at the result. Here is an example.

SELECT 1 = 0 AND 1 = 1 ;
+-----------------+
| 1 = 0 AND 1 = 1 |
+-----------------+
|               0 |
+-----------------+

In the above expression, MySQL evaluates only 1=0 but not 1=1. This is because, since 1=0 evaluates to 0, MySQL can safely determine that the result of entire AND expression will be zero. So MySQL doesn’t need to evaluate the remaining part of the expression.

MySQL AND Operator Examples

Now let us look at couple of AND operator examples. Let us say you have customers(id, name, country, state, sale) table that contains information about customers and amount of sale obtained from them. Here is a simple SELECT statement to select rows where country=’US’ and state=’IL’.

mysql> select *
       from customers
       where country='US' and state='IL';

You can also combine numeric columns with text-based columns using AND operator. Here is an example to select rows from customers table where country=’US’ and sale>1000.

mysql> select * 
       from customers
       where country='US' and sale>1000;

In this article, we have learnt what is MySQL AND operator and how to use it.

MySQL AND Operator With Examples

MySQL AND Operator With Examples

MySQL AND Operator Examples

Related posts:

Leave a Reply Cancel reply