From the course: MySQL Essential Training

Comparison operators - MySQL Tutorial

From the course: MySQL Essential Training

Start my 1-month free trial Buy for my team

Comparison operators

“

- [Bill] Hi, I am Bill Weinman. MySQL has a complete set of comparison operators, which may be used with both numbers and strings. If I say SELECT 0 equals 1, you'll notice that I get a result that says 0. So the select statement is used to get results from any data set, and an expression is a data set. It's just a very small data set with one cell, with one result, with one value. And so 0 equals 1 is a logical expression which tests whether the two values on either side of the equal sign are equal. And if they're equal, then the result is true. And if they're not equal, then the result is false. True is represented by 1 and false is represented by 0. So when we look at this result, we see that the title of the column is 0 equals 1, and the result is 0 for false because 0 does not equal 1. If I say 0 equals 0, then I get a 1 for true in the result. If I say 0.0 equals 0, I get a 1 for true. Now, 0.0 is a floating point number, it's a real number, and the single 0 without a decimal point is an integer, yet their value is equivalent. But here's the interesting thing. If I put this in single quotes, now 0.0 is a string and if I test it for 0, you notice I still get a true result. When a string is compared to the number, the string is first converted to its numeric equivalent. So if I say 0.1 is greater than 0, I also get true. If I say 0.1 is less than 0, I get 0 for false. And if I take off those quote marks, I get the same result. MySQL supports the standard comparison operators, so I'll just take two different numbers. I'll say 9 is less than 7, and of course that's false. If I say 9 is greater than 7, that is true. If I say 9 is equal to 7, that's false. If I say 9 is not equal to 7, the exclamation point or a bang followed by an equal sign that's for not equals, I get true. If I get 9 is less than or equals, that's false. If I say 9 is greater than or equals, then the result is true. MySQL also supports Boolean logical operators for combining conditions. So if I put this in parentheses and I say 9 is greater than 7 and I use the logical AND operator, AND 12 is less than 27, I get true because both of those conditions are true. If I make one of them false because both of them are no longer true, the conjunction operator, the AND operator combines them and it wants them both to be true in order for to get a true result, the overall result is now false. But if I use the OR operator now the result is true because only one of the two conditions needs to be true for the OR operator to resolve to true. MySQL also supports IS and IS NOT for testing Boolean values. So I can say 9 is greater than 7 IS TRUE and I'll get a true result. But if I say IS NOT TRUE, then I get a false result because 9 is greater than 7 is true. So is not true, is no longer true. (laughs) These are a few of the most common comparison operators in MySQL. We'll see examples of these and others throughout this course.

Contents