In MySQL, there exists three logical operators namely AND, OR and NOT. In this post we are going to learn how AND operator works in MySQL.
The AND operator is a logical operator that combines two or more Boolean expressions and returns true only if both expressions evaluate to true. The AND operator returns false if one of the two expressions evaluate to false.
In simpler terms MySQL AND operator compares two expressions and returns true if both of the expressions are TRUE otherwise it returns FALSE. It is represented with “&” symbol.
Let us discuss about AND operator in simple cases and later on will discuss under different cases.
1. AND operator returns 1 when all the operands are non-zero and not NULL.
We can check it from the below example:
2. AND operator returns 1 or more operands are 0 , otherwise it returns NULL.
3. What happens when the inputs are 0 and NULL!!
AND operator returns 0 when one of the operands is 0 and the other is NULL.
4.AND operator returns NULL when all the operands are NULL.
In MySQL, What if there is a situation to evaluate an expression (which includes with AND operator). In such case, it evaluates the remaining parts of the expression until it can determine the result. This function is called short-circuit evaluation.
In MySQL, Notice that 0 is FALSE and other than 0 are TRUE.
From the above example, we consider 1=0 as the first part of expression ‘1=0 AND 1+0’ . Since 1=0 evaluates to FALSE and so that MySQL can conclude as the final result of whole expression as FALSE. With this there is no need to evaluate the remaining part of the expression i.e., 1+0.
Till now we have seen how AND operator in MySQL works on simple statements. Let us discuss how AND operator in MySQL works with databases…
The AND operator is often used in the WHERE clause of the SELECT, UPDATE, DELETE statement to form Boolean expressions.
The syntax of AND operator is as follows:
|SELECT column1, column2, … column n FROM table_name WHERE condition1 AND condition2 AND condition3 …condition n;|
To demonstrate this with example, I am choosing my sample database as follows.
In the above example we are combining only two expressions. But we can also combine more than two expressions. For example,
If the given condition fails it returns nothing. We can observe it from the below example:
(From the exact above two examples please notice the conditions where rank < 10 and rank>10.)