What is the difference between Logical and Comparison operators in MySQL?

Question

Studying MySQL I came to some confusion about operators categorization in MySQL.

NOT is a Logical operator (Details)

while NOT LIKE, LIKE, IS NOT, IS NULL are Comparison operators. (Details)

I'm unable to grasp the real difference.

Answer 1

A logical operator's operands are booleans; whereas comparison operators may have operands of any type.

The comparison operators test the relationship between their operands according to an ordering over the operands' type set, and return a boolean result: 1 < 2, 'hello' > 'aardvark', CURRENT_DATE = '2013-12-30', 'peanut' LIKE 'pea%', 'walnut' NOT LIKE 'pea%', '' IS NOT NULL, etc.

Booleans, on the other hand, don't have an "ordering" by which such relationships can be established*—it's pretty meaningless, for example, to say that FALSE < TRUE. Instead, we think about them in terms of their "truth" and the operators which act upon them in terms of their logical validity: TRUE AND TRUE, FALSE XOR TRUE, NOT FALSE, etc.

Of course, there are many situations where the same logical result can be expressed in multiple ways—for example:

• 1 < 2 is logically the same as both 2 > 1 and NOT (1 >= 2)

• 'walnut' NOT LIKE 'pea%' is logically the same as NOT ('walnut' LIKE 'pea%')

• '' IS NOT NULL is logically the same as NOT ('' IS NULL)

However, negating a comparison involves a logical operation (negation) in addition to the comparison operation, whereas a single comparison operation that immediately yields the desired result is typically more concise/readable and may be easier for the computer to optimise.

---

^{* Some languages (such as MySQL) don't have real boolean types, instead using zero and non-zero integers to represent FALSE and TRUE respectfully. Consequently an ordering does exist over their booleans, albeit that doesn't affect the conceptual distinction.}

Answer 2

Simply put, NOT LIKE, LIKE, IS NOT are used to compare two values. For example :

SELECT * FROM `table_name` WHERE `name` NOT LIKE `examplename`;
SELECT * FROM `table_name` WHERE `key_col` IS NULL;

NOT returns the inverse of the value. It's same as !=, for the most part. Example :

SELECT * FROM `student` WHERE NOT (student_id = 1);

The operator returns 1 if the operand is 0 and returns 0 if the operand is nonzero. It returns NULL if the operand is NOT NULL.

Visit : MySQL Operators

Visit : NOT ! Operator