{"id":5292,"date":"2014-05-23T05:28:51","date_gmt":"2014-05-23T12:28:51","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=5292"},"modified":"2023-10-08T03:18:11","modified_gmt":"2023-10-08T10:18:11","slug":"mysql-and","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-and\/","title":{"rendered":"MySQL AND Operator"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to the MySQL AND<\/code> operator to combine multiple Boolean expressions to filter data.<\/p>\n\n\n\n

Introduction to MySQL AND operator<\/h2>\n\n\n\n

MySQL doesn’t have a built-in Boolean type. Instead, it uses the number zero as FALSE and non-zero values as TRUE.<\/p>\n\n\n\n

The AND<\/code> operator is a logical operator that combines two or more Boolean<\/a> expressions and returns 1, 0, or NULL:<\/p>\n\n\n

A AND B<\/code><\/span><\/pre>\n\n\n
In this expression, A and B are called operands. They can be literal values or expressions.<\/p>\n\n\n\n
The logical AND operator returns 1 if both A and B are non-zero and not NULL. It returns 0 if either operand is zero; otherwise, it returns NULL.<\/p>\n\n\n\n
The logical AND operator returns 1 if both A and B are non-zero and NOT NULL. For example:<\/p>\n\n\n
SELECT<\/span> 1<\/span> AND<\/span> 1<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n
+---------+\n| 1 AND 1 |\n+---------+\n|       1 |\n+---------+\n1 row in set (0.00 sec)<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
The logical AND operator returns 0 if A or B is zero or both A and B are zero:<\/p>\n\n\n
SELECT<\/span> 1<\/span> AND<\/span> 0<\/span>, 0<\/span> AND<\/span> 1<\/span>, 0<\/span> AND<\/span> 0<\/span>, 0<\/span> AND<\/span> NULL<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n
+---------+---------+---------+------------+\n| 1 AND 0 | 0 AND 1 | 0 AND 0 | 0 AND NULL |\n+---------+---------+---------+------------+\n|       0 |       0 |       0 |          0 |\n+---------+---------+---------+------------+\n1 row in set (0.00 sec)<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
The logical AND operator returns NULL if either operand is non-zero or both operands are NULL.<\/p>\n\n\n
SELECT<\/span> 1<\/span> AND<\/span> NULL<\/span>, NULL<\/span> AND<\/span> NULL<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n
+------------+---------------+\n| 1 AND NULL | NULL AND NULL |\n+------------+---------------+\n|       NULL |          NULL |\n+------------+---------------+\n1 row in set (0.00 sec)<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
The following table illustrates the results of the AND<\/code> operator when combining true, false, and null.<\/p>\n\n\n\n
<\/th>TRUE<\/th>FALSE<\/th>NULL<\/th><\/tr>
TRUE<\/strong><\/td>TRUE<\/td>FALSE<\/td>NULL<\/td><\/tr>
FALSE<\/strong><\/td>FALSE<\/td>FALSE<\/td>FALSE<\/td><\/tr>
NULL<\/strong><\/td>NULL<\/td>FALSE<\/td>NULL<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
In practice, you’ll use the AND<\/code> operator in the WHERE<\/a><\/code> clause of the SELECT<\/a><\/code>, UPDATE<\/a><\/code>, DELETE<\/a><\/code> statements to form a condition. Also, you can the AND<\/code> operator in the conditions of the INNER JOIN<\/a><\/code> and LEFT JOIN<\/a><\/code> clauses.<\/p>\n\n\n\n
When evaluating an expression that contains the AND<\/code> operator, MySQL stops evaluating the remaining parts of the expression as soon as it can determine the result. <\/p>\n\n\n\n
This is called short-circuit evaluation. In other words, the AND<\/code> operator is short-circuited. For example:<\/p>\n\n\n
SELECT<\/span> 1<\/span> = 0<\/span> AND<\/span> 1<\/span> \/ 0<\/span> ;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
+-----------------+\n| 1 = 0 AND 1 \/ 0 |\n+-----------------+\n|               0 |\n+-----------------+\n1 row in set (0.01 sec)<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
In this example, MySQL only evaluates the first part 1 = 0<\/code> of the expression 1 = 0 AND 1  \/ 0<\/code>.  <\/p>\n\n\n\n
Since the expression 1 = 0<\/code> returns 0, MySQL can determine the result of the whole expression, which is 0.<\/p>\n\n\n\n
Therefore, MySQL doesn’t need to evaluate the remaining part of the expression, which is 1\/0<\/code>; it would issue a division by zero error.<\/p>\n\n\n\n
MySQL AND operator examples<\/h2>\n\n\n\n
Let’s use the customers<\/code> table in the sample database<\/a> for the demonstration.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
The following statement uses the AND<\/code> operator to find customers who are located in California (CA), USA:<\/p>\n\n\n
SELECT<\/span> \n    customername, \n    country, \n    state\nFROM<\/span>\n    customers\nWHERE<\/span>\n    country = 'USA'<\/span> AND<\/span> \n    state = 'CA'<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
+------------------------------+---------+-------+\n| customername                 | country | state |\n+------------------------------+---------+-------+\n| Mini Gifts Distributors Ltd. | USA     | CA    |\n| Mini Wheels Co.              | USA     | CA    |\n| Technics Stores Inc.         | USA     | CA    |\n| Toys4GrownUps.com            | USA     | CA    |\n| Boards & Toys Co.            | USA     | CA    |\n| Collectable Mini Designs Co. | USA     | CA    |\n| Corporate Gift Ideas Co.     | USA     | CA    |\n| Men 'R' US Retailers, Ltd.   | USA     | CA    |\n| The Sharp Gifts Warehouse    | USA     | CA    |\n| West Coast Collectables Co.  | USA     | CA    |\n| Signal Collectibles Ltd.     | USA     | CA    |\n+------------------------------+---------+-------+\n11 rows in set (0.00 sec)<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
By using the AND<\/code> operator, you can combine more than two Boolean expressions. For example, the following query returns the customers who are located in California, USA, and have a credit limit greater than 100K.<\/p>\n\n\n
SELECT<\/span> \n    customername, \n    country, \n    state, \n    creditlimit\nFROM<\/span>\n    customers\nWHERE<\/span>\n    country = 'USA'<\/span> AND<\/span> \n    state = 'CA'<\/span> AND<\/span> \n    creditlimit > 100000<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
+------------------------------+---------+-------+-------------+\n| customername                 | country | state | creditlimit |\n+------------------------------+---------+-------+-------------+\n| Mini Gifts Distributors Ltd. | USA     | CA    |   210500.00 |\n| Collectable Mini Designs Co. | USA     | CA    |   105000.00 |\n| Corporate Gift Ideas Co.     | USA     | CA    |   105000.00 |\n+------------------------------+---------+-------+-------------+\n3 rows in set (0.00 sec)<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
Summary<\/h2>\n\n\n\n
    \n
  • Use the AND<\/code> operator to combine two Boolean expressions. The AND<\/code> operator returns true when both expressions are true; otherwise, it returns false.<\/li>\n\n\n\n
  • Use the AND<\/code> operator to form conditions in the WHERE<\/code> clause of the SELECT<\/code> statement.<\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful? <\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t
Try It Out<\/a><\/p>\n\n\n
Try It Out<\/a><\/p>\n\n\n
Try It Out<\/a><\/p>\n\n\n