El operador AND en MySQL se utiliza para combinar múltiples condiciones en una consulta SELECT, UPDATE o DELETE. En una cláusula WHERE, el operador AND devuelve únicamente los registros que cumplen con todas las condiciones especificadas.
El operador AND en MySQL devuelve 1 si ambos operandos son diferentes de cero y no NULL, y devuelve 0 en caso contrario. También devuelve NULL si alguno de los operandos es NULL.
SELECT 1 AND 1; -- Devuelve 1, porque ambos operandos (1 y 1) son diferentes de cero y NO NULOS
SELECT 1 AND 0; -- Devuelve 0, porque uno de los operandos (0) es cero
SELECT 0 AND 1; -- Devuelve 0, porque uno de los operandos (0) es cero
SELECT 0 AND 0; -- Devuelve 0, porque ambos operandos son cero
SELECT NULL AND 1; -- Devuelve NULL, porque uno de los operandos (NULL) es NULL
SELECT NULL AND NULL; -- Devuelve NULL, porque ambos operandos son NULLUtilizaremos la tabla de customers de la base de datos de muestra para la demostración.
Seleccionar los clientes que tienen un límite de crédito mayor que $50,000 y cuyo país es 'USA'
SELECT * FROM customers
WHERE creditLimit > 50000 AND country = 'USA';+----------------+------------------------------+-----------------+------------------+------------+---------------------------+--------------+---------------+-------+------------+---------+------------------------+-------------+
| customerNumber | customerName | contactLastName | contactFirstName | phone | addressLine1 | addressLine2 | city | state | postalCode | country | salesRepEmployeeNumber | creditLimit |
+----------------+------------------------------+-----------------+------------------+------------+---------------------------+--------------+---------------+-------+------------+---------+------------------------+-------------+
| 112 | Signal Gift Stores | King | Jean | 7025551838 | 8489 Strong St. | NULL | Las Vegas | NV | 83030 | USA | 1166 | 71800.00 |
| 124 | Mini Gifts Distributors Ltd. | Nelson | Susan | 4155551450 | 5677 Strong St. | NULL | San Rafael | CA | 97562 | USA | 1165 | 210500.00 |
| 129 | Mini Wheels Co. | Murphy | Julie | 6505555787 | 5557 North Pendale Street | NULL | San Francisco | CA | 94217 | USA | 1165 | 64600.00 |
| 131 | Land of Toys Inc. | Lee | Kwai | 2125557818 | 897 Long Airport Avenue | NULL | NYC | NY | 10022 | USA | 1323 | 114900.00 |
| 151 | Muscle Machine Inc | Young | Jeff | 2125557413 | 4092 Furth Circle | Suite 400 | NYC | NY | 10022 | USA | 1286 | 138500.00 |
| 157 | Diecast Classics Inc. | Leong | Kelvin | 2155551555 | 7586 Pompton St. | NULL | Allentown | PA | 70267 | USA | 1216 | 100600.00 |
| 161 | Technics Stores Inc. | Hashimoto | Juri | 6505556809 | 9408 Furth Circle | NULL | Burlingame | CA | 94217 | USA | 1165 | 84600.00 |
| 175 | Gift Depot Inc. | King | Julie | 2035552570 | 25593 South Bay Ln. | NULL | Bridgewater | CT | 97562 | USA | 1323 | 84300.00 |
| 181 | Vitachrome Inc. | Frick | Michael | 2125551500 | 2678 Kingston Rd. | Suite 101 | NYC | NY | 10022 | USA | 1286 | 76400.00 |
| 204 | Online Mini Collectables | Barajas | Miguel | 6175557555 | 7635 Spinnaker Dr. | NULL | Brickhaven | MA | 58339 | USA | 1188 | 68700.00 |
| 205 | Toys4GrownUps.com | Young | Julie | 6265557265 | 78934 Hillside Dr. | NULL | Pasadena | CA | 90003 | USA | 1166 | 90700.00 |
| 239 | Collectable Mini Designs Co. | Thompson | Valarie | 7605558146 | 361 Furth Circle | NULL | San Diego | CA | 91217 | USA | 1166 | 105000.00 |
| 286 | Marta's Replicas Co. | Hernandez | Marta | 6175558555 | 39323 Spinnaker Dr. | NULL | Cambridge | MA | 51247 | USA | 1216 | 123700.00 |
| 319 | Mini Classics | Frick | Steve | 9145554562 | 3758 North Pendale Street | NULL | White Plains | NY | 24067 | USA | 1323 | 102700.00 |
| 320 | Mini Creations Ltd. | Huang | Wing | 5085559555 | 4575 Hillside Dr. | NULL | New Bedford | MA | 50553 | USA | 1188 | 94500.00 |
| 321 | Corporate Gift Ideas Co. | Brown | Julie | 6505551386 | 7734 Strong St. | NULL | San Francisco | CA | 94217 | USA | 1165 | 105000.00 |
| 339 | Classic Gift Ideas, Inc | Cervantes | Francisca | 2155554695 | 782 First Street | NULL | Philadelphia | PA | 71270 | USA | 1188 | 81100.00 |
| 347 | Men 'R' US Retailers, Ltd. | Chandler | Brian | 2155554369 | 6047 Douglas Av. | NULL | Los Angeles | CA | 91003 | USA | 1166 | 57700.00 |
| 363 | Online Diecast Creations Co. | Young | Dorothy | 6035558647 | 2304 Long Airport Avenue | NULL | Nashua | NH | 62005 | USA | 1216 | 114200.00 |
| 379 | Collectables For Less Inc. | Nelson | Allen | 6175558555 | 7825 Douglas Av. | NULL | Brickhaven | MA | 58339 | USA | 1188 | 70700.00 |
| 424 | Classic Legends Inc. | Hernandez | Maria | 2125558493 | 5905 Pompton St. | Suite 750 | NYC | NY | 10022 | USA | 1286 | 67500.00 |
| 450 | The Sharp Gifts Warehouse | Frick | Sue | 4085553659 | 3086 Ingle Ln. | NULL | San Jose | CA | 94217 | USA | 1165 | 77600.00 |
| 455 | Super Scale Inc. | Murphy | Leslie | 2035559545 | 567 North Pendale Street | NULL | New Haven | CT | 97823 | USA | 1286 | 95400.00 |
| 462 | FunGiftIdeas.com | Benitez | Violeta | 5085552555 | 1785 First Street | NULL | New Bedford | MA | 50553 | USA | 1216 | 85800.00 |
| 475 | West Coast Collectables Co. | Thompson | Steve | 3105553722 | 3675 Furth Circle | NULL | Burbank | CA | 94019 | USA | 1166 | 55400.00 |
| 486 | Motor Mint Distributors Inc. | Salazar | Rosa | 2155559857 | 11328 Douglas Av. | NULL | Philadelphia | PA | 71270 | USA | 1323 | 72600.00 |
| 487 | Signal Collectibles Ltd. | Taylor | Sue | 4155554312 | 2793 Furth Circle | NULL | Brisbane | CA | 94217 | USA | 1165 | 60300.00 |
| 495 | Diecast Collectables | Franco | Valarie | 6175552555 | 6251 Ingle Ln. | NULL | Boston | MA | 51003 | USA | 1188 | 85100.00 |
+----------------+------------------------------+-----------------+------------------+------------+---------------------------+--------------+---------------+-------+------------+---------+------------------------+-------------+
28 rows in set (0.01 sec)Seleccionar los clientes que tienen un límite de crédito mayor que $50,000 y cuyo código postal es '10022'
SELECT * FROM customers
WHERE creditLimit > 50000 AND postalCode = '10022';+----------------+----------------------+-----------------+------------------+------------+-------------------------+--------------+------+-------+------------+---------+------------------------+-------------+
| customerNumber | customerName | contactLastName | contactFirstName | phone | addressLine1 | addressLine2 | city | state | postalCode | country | salesRepEmployeeNumber | creditLimit |
+----------------+----------------------+-----------------+------------------+------------+-------------------------+--------------+------+-------+------------+---------+------------------------+-------------+
| 131 | Land of Toys Inc. | Lee | Kwai | 2125557818 | 897 Long Airport Avenue | NULL | NYC | NY | 10022 | USA | 1323 | 114900.00 |
| 151 | Muscle Machine Inc | Young | Jeff | 2125557413 | 4092 Furth Circle | Suite 400 | NYC | NY | 10022 | USA | 1286 | 138500.00 |
| 181 | Vitachrome Inc. | Frick | Michael | 2125551500 | 2678 Kingston Rd. | Suite 101 | NYC | NY | 10022 | USA | 1286 | 76400.00 |
| 424 | Classic Legends Inc. | Hernandez | Maria | 2125558493 | 5905 Pompton St. | Suite 750 | NYC | NY | 10022 | USA | 1286 | 67500.00 |
+----------------+----------------------+-----------------+------------------+------------+-------------------------+--------------+------+-------+------------+---------+------------------------+-------------+
4 rows in set (0.00 sec)Seleccionar los clientes que tienen un número de empleado de representante de ventas igual a 1188 y un límite de crédito mayor que $50,000
SELECT * FROM customers
WHERE salesRepEmployeeNumber = 1188 AND creditLimit > 50000;+----------------+----------------------------+-----------------+------------------+------------+--------------------+--------------+--------------+-------+------------+---------+------------------------+-------------+
| customerNumber | customerName | contactLastName | contactFirstName | phone | addressLine1 | addressLine2 | city | state | postalCode | country | salesRepEmployeeNumber | creditLimit |
+----------------+----------------------------+-----------------+------------------+------------+--------------------+--------------+--------------+-------+------------+---------+------------------------+-------------+
| 204 | Online Mini Collectables | Barajas | Miguel | 6175557555 | 7635 Spinnaker Dr. | NULL | Brickhaven | MA | 58339 | USA | 1188 | 68700.00 |
| 320 | Mini Creations Ltd. | Huang | Wing | 5085559555 | 4575 Hillside Dr. | NULL | New Bedford | MA | 50553 | USA | 1188 | 94500.00 |
| 339 | Classic Gift Ideas, Inc | Cervantes | Francisca | 2155554695 | 782 First Street | NULL | Philadelphia | PA | 71270 | USA | 1188 | 81100.00 |
| 379 | Collectables For Less Inc. | Nelson | Allen | 6175558555 | 7825 Douglas Av. | NULL | Brickhaven | MA | 58339 | USA | 1188 | 70700.00 |
| 495 | Diecast Collectables | Franco | Valarie | 6175552555 | 6251 Ingle Ln. | NULL | Boston | MA | 51003 | USA | 1188 | 85100.00 |
+----------------+----------------------------+-----------------+------------------+------------+--------------------+--------------+--------------+-------+------------+---------+------------------------+-------------+
5 rows in set (0.00 sec)Seleccionar los clientes que tienen un límite de crédito mayor que $50,000 y un código postal igual a '44000'
SELECT customerNumber, customerName, contactLastName,creditLimit,postalCode
FROM customers
WHERE creditLimit > 50000 AND postalCode = '44000';+----------------+-------------------+-----------------+-------------+------------+
| customerNumber | customerName | contactLastName | creditLimit | postalCode |
+----------------+-------------------+-----------------+-------------+------------+
| 119 | La Rochelle Gifts | Labrune | 118200.00 | 44000 |
+----------------+-------------------+-----------------+-------------+------------+
1 row in set (0.00 sec)
