All logical operators evaluate true, false, and unknown(NULL). Mysql gives 1 for true 0 for false and NULL for unknown. MySQL evaluates any nonzero, non-NULL value to TRUE.
Logical And(AND, &&):- If all condition evaluates true then they return true otherwise it always returns false. For Example:-
SELECT NULL AND NULL;
->1
SELECT 0 AND 1;
->0
SELECT * FROM employee WHERE department = “account” AND salary = “15000”;
If department and salary are both will found then the query will return employee detail otherwise if any one condition will fail then the query will return no value.
Logical Or(OR, ||):- If either any one condition will true then the query will return true otherwise it return false,
For Example:-
SELECT 0 AND 0;
->0
SELECT 0 AND 1;
->1
SELECT * FROM employee WHERE department = “account” OR salary = “15000”;
If the department or Salary any one condition will true then the query will return details of the employee.
Negates Value(NOT, !):- It returns only those values if the condition will not true.
For Example:-
SELECT NOT NULL;
-> NULL
SELECT NOT 0;
-> 1
SELECT * FROM employee WHERE name NOT LIKE ‘d%’;
It will display all employees whose name will not start with “d”
Logical XOR(XOR):- Returns NULL if either condition is NULL. For a non-NULL condition, evaluates to 1 if an odd number of conditions is nonzero, otherwise, 0 is returned.
For Example:-
SELECT 1 XOR 0;
-> 1
SELECT 1 XOR 1;
-> 0
ALL:- If all the conditions will be true then it will return true.
For Example:-
SELECT * FROM students
WHERE rollnumber > ALL (SELECT rollnumber FROM students WHERE rollnumber > 5);
It will display details of all students whose roll number will be greater than 5.
ANY:- If any of the subqueries will be true then it will return true.
For Example:-
SELECT * FROM students
WHERE rollnumber > ANY (SELECT rollnumber FROM students WHERE rollnumber > 5);
BETWEEN:- If the given range of value will be found then it will return the value.
For Example:-
SELECT * FROM students where rollnumber BETWEEN 10 AND 20;
It will return all details from 11 to 19
EXISTS:- If the subquery returns one or more results then it will return true.
For Example:-
SELECT * FROM students
WHERE rollnumber > EXISTS (SELECT rollnumber FROM students WHERE rollnumber > 5);
IN:-It is a shorthand of multiple OR conditions. If the anyone list of expression will found then it will return the value.
For Example:-
SELECT * FROM students WHERE rollnumber IN(10,23, 24, 28, 50);
LIKE:- If the pattern will match then it will return the value.
For Example:-
SELECT * FROM employee WHERE name LIKE ‘d%’;
SOME:- If any subquery meets the condition then it will return the value. The SOME and ANY comparison conditions are similar to each other and are completely interchangeable.
For Example:-
SELECT * FROM students
WHERE rollnumber > SOME (SELECT rollnumber FROM students WHERE rollnumber > 5);