MySQL Tutorial/Logic Operator/AND
Logical AND(AND, &&)
Evaluates to 1 if all operands are non-zero and not NULL.
Evaluates to 0 if one or more operands are 0.
Otherwise NULL is returned.
<source lang="sql">
mysql> mysql> SELECT 1 && 1; +--------+ | 1 && 1 | +--------+ | 1 | +--------+ 1 row in set (0.00 sec) mysql> SELECT 1 && 0; +--------+ | 1 && 0 | +--------+ | 0 | +--------+ 1 row in set (0.00 sec) mysql></source>
Using AND to link two conditions
<source lang="sql">
mysql> mysql> mysql> CREATE TABLE Books(
-> BookID SMALLINT NOT NULL PRIMARY KEY, -> BookName VARCHAR(40) NOT NULL, -> Category VARCHAR(15), -> InStock SMALLINT NOT NULL, -> OnOrder SMALLINT NOT NULL -> );
Query OK, 0 rows affected (0.03 sec) mysql> mysql> mysql> INSERT INTO Books VALUES (101, "Java", "Nonfiction", 12, 13),
-> (102, "MySQL", "Fiction", 17, 20), -> (103, "Oracle", "Nonfiction", 23, 33), -> (104, "VB.net", "Nonfiction", 32, 12), -> (105, "www.sqle.ru", "Fiction", 6, 35), -> (106, "Perl", "Fiction", 28, 14), -> (107, "Php", NULL, 46, 3);
Query OK, 7 rows affected (0.00 sec) Records: 7 Duplicates: 0 Warnings: 0 mysql> mysql> mysql> select * from Books; +--------+----------------+------------+---------+---------+ | BookID | BookName | Category | InStock | OnOrder | +--------+----------------+------------+---------+---------+ | 101 | Java | Nonfiction | 12 | 13 | | 102 | MySQL | Fiction | 17 | 20 | | 103 | Oracle | Nonfiction | 23 | 33 | | 104 | VB.net | Nonfiction | 32 | 12 | | 105 | www.sqle.ru | Fiction | 6 | 35 | | 106 | Perl | Fiction | 28 | 14 | | 107 | Php | NULL | 46 | 3 | +--------+----------------+------------+---------+---------+ 7 rows in set (0.00 sec) mysql> mysql> SELECT BookName, Category, InStock, OnOrder
-> FROM Books -> WHERE Category="Fiction" AND (InStock+OnOrder)>40 -> ORDER BY BookName;
+----------------+----------+---------+---------+ | BookName | Category | InStock | OnOrder | +----------------+----------+---------+---------+ | Perl | Fiction | 28 | 14 | | www.sqle.ru | Fiction | 6 | 35 | +----------------+----------+---------+---------+ 2 rows in set (0.00 sec) mysql> mysql> drop table Books; Query OK, 0 rows affected (0.00 sec) mysql></source>
Using Logical AND Operator with NULL value
<source lang="sql">
mysql> mysql> SELECT 1 && NULL; +-----------+ | 1 && NULL | +-----------+ | NULL | +-----------+ 1 row in set (0.00 sec) mysql> SELECT 0 && NULL; +-----------+ | 0 && NULL | +-----------+ | 0 | +-----------+ 1 row in set (0.00 sec) mysql> SELECT NULL && 0; +-----------+ | NULL && 0 | +-----------+ | 0 | +-----------+ 1 row in set (0.00 sec) mysql></source>