Logical Operators are the ones which evaluate to TRUE, FALSE or unknown (NULL) values. These operators are used where ever we have to apply multiple filter conditions to SELECT, DELETE or UPDATE selective records of a table. These MySQL Logical Operators are most commonly used in the WHERE clause discussed in the MySQL SELECT statement.
The following logical operators are available in MySQL. We will use the following data in artists table in examples to explain functioning of MySQL Logical Operators.
Logical AND (AND /&&)
The expressions or operands combined with Logical AND operator will return TRUE if all the operands or expressions are true or non-zero. If one of the operands or expressions is zero or false, and even if the rest of operands or expressions are true, the whole expression combined with Logical AND will evaluate to FALSE.
Example
SELECT * FROM artists WHERE ratings=0 AND artist_name LIKE 'M%';
The SELECT statement will return all those records from artists table where ratings are equal to 0 and artist name begins with ‘M’
Logical OR (OR /||)
The expressions or operands combined with Logical OR operator will return TRUE if any one of the combined operands or expressions are true or non-zero. If all the operands or expressions are zero or false only then the whole expression combined with Logical OR will evaluate to FALSE.
Example
SELECT * FROM artists WHERE artist_name LIKE 'D%' OR artist_name LIKE 'M%';
The SELECT statement will return all those records from artists table artist name begin with ‘M’ or ‘D’
Logical NOT (NOT /!)
The logical NOT operator negates the operand’s TRUTH Value. If the operand is non-zero or True, NOT logical operator will return FALSE. If the operand is zero or False, NOT logical operator will return TRUE.
Example
SELECT * FROM artists WHERE NOT (artist_name LIKE 'D%' OR artist_name LIKE 'M%');
This query will return the records where artist names do not start with ‘D’ or ‘M’. This will negate the result of previous query discussed for OR operator.
Logical XOR (XOR)
The logical XOR operator stands for exclusive OR. It returns true if out of both the two operands evaluate to opposite logical values. If one operand is non-zero and other operand is Zero then Logical XOR returns True. If both the operands of XOR operator evaluate to same logical value, the whole expression returns FALSE. If both operands are true (non-zero) or both are false (zero) then XOR expression returns FALSE.
Example
