Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
MySQL supports the standard SQL conditional expression syntax. This is not really a function, but I'm covering it here because it's important to understand it. The syntax is a little cumbersome, but here's how it works. We're going to use the scratch data base for this, and we're going to create a little table. We'll destroy it later. Create table, and we'll call it booltest. Bool for Boolean, and we'll give it two simple columns integers, and we'll insert a couple values into it just a one and a zero we're going to be using these as Boolean values.
In MySQL a one is a Boolean true. And a zero is a Boolean false. Actually, zero is false. And anything else is true. But one is traditionally used for true. And so we'll select from our booltest. So we can see the values here. And so, there's our table. And it has two values in one row, a one and a zero. We're going to use this to test our conditional expression. So, I'm going to go ahead and create a SELECT statement here.
And I'm going to put the body of the SELECT statement. On separate lines, and so there'll be a case here when, and there's actually two different syntax's for case. I'm going to show you one, then I'm going to show you the other one. So when a and it's true if there's a non-zero value in the a column. And it's false if there's a zero value in the a column. THEN, and we'll give the value true, so this returns that value for the select statement.
ELSE false in a string. END this ends the case statement. And so I can say AS boolA, for the next value. And then I'm going to do another case statement. I'm just going to Copy this and Paste it here. We won't need the comma. And we'll select for b and we'll name it boolB. And we'll save FROM booltest. And we have the semicolon on the next line. So what do we expect to have happen? Well.
If a is true then it will return the value true for this column and otherwise it will return the value false. And this will be named a and b will be true or false depending on it b column is true or false. So the a column is true because it has a one and the b column is. False because it has a zero. So, let's see if we get the expected result here boolA is true and boolB is false. So, this is very simply how the case statement works, in this syntax model I'll show you the other one in a moment.
So it has a WHEN clause. And then WHEN clause has a Boolean expression, which in this case is just that column, because it's Boolean true or false. And then an optional ELSE for each WHEN. And then you can have more than one WHEN as well. And END ends the case syntax so that you can go ahead and do whatever else you're going to do with that column as in this case we have an as alias. So the other way this can work is, I can say CASE a and I can say when it's value is one.
Then true ELSE false and likewise with the other column here. I can say b and when one, then true ELSE false. So this allows you to select a particular variable or a column and have multiple possible values for it. So again we get exactly the same result when I press Go. So, now I'm going to remove our temporary table. To return the database to its' original state, and the case expression can be a bit awkward but, it's very useful and you do well to become familiar with it.
Use case wherever you need a conditional in MySQL. You'll see one or two other examples of this later in the course.
Get unlimited access to all courses for just $25/month.Become a member