[00:00] In this lesson, we are going to take a look at a Conditional WHERE clause. To start, I have copied the Filtered Select Query from before and created a new named query called Conditional Filtered Select. Here, we have our Select Query with the WHERE clause that requires the name column, be a certain value provided by a parameter. However, in this case, I want my WHERE clause to be conditional applying sometimes but other times I don't want to have a WHERE Clause and I simply want to select all of the rows out of the database. Your initial thought might be to simply not provide a parameter value. But if we were to test that out, we can see that providing no parameter value is not going to return any rows.

[01:01] This is because none of my rows have a name column value of null, which is essentially what I'm providing it. To accomplish what we're after, we can use a simple bit of logic in our query. I'd like to add another condition to our WHERE clause and I'm going to separate it from my original with or, my condition is then going to look something like this. All =, and then my parameter value. Now what I've done here may not initially be obvious. I don't have any name values in my table called all, but in this case, I'm not checking the value of all against the table. I'm simply checking the hard-coded value of all to the parameter value. So in this case, if my parameter value is the string all, technically the string all is going to be equal to the parameter all for every single row in the table.

[02:04] So even though the word all will not match up to any of the values in the name column, according to my first condition, it will return true for every single row based on my second condition, let's go try it out. I'm going to go to the testing tab, type in my parameter value of all. And when I execute the query, you can see that I return all of the rows in the table. Of course the first condition can still act like a filter. I simply need to provide a value that I want to filter by and execute my query, and only those rows will be returned. The trick to the Conditional WHERE clause is to ensure that the second condition uses a value that's not going to show up within your regular table.

[03:04] In this case, I don't expect to have a name of all. So I can use that. Similarly, if I was using quantity within the WHERE clause, instead of all, I would need a numeric value. So I could use something like negative one in the secondary condition to denote that I want to see all of them because I know my quantities are never going to be below zero.