Loading

MySQL Logical Operators

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);

Share with:


Leave a Reply

Connect with:

z35W7z4v9z8w