The SQL WHERE keyword is used to select data conditionally, by adding it to already existing SQL SELECT query. The WHERE keyword can be used to insert, update and delete data from table(s), but for now we'll stick with conditionally retrieving data, as we already know how to use the SELECT keyword.
In order to illustrate better the WHERE keyword applications, we are going to add 2 columns to the Users table we used in the previous chapters and we'll also add a few more rows with actual data entries:
Consider the following SQL query:
The result of the SQL expression above will be the following:
Our SQL query used the "=" (Equal) operator in our WHERE criteria:
City = 'Los Angeles'
As you can see we have selected only the users which entries have the value 'Los Angeles' in the City column. You may also have noticed that we put the Los Angeles string value into single quotes. Whenever you use string (character) values in your SQL queries, you have to put them between single quotes. For example the SQL query below will fail because it uses double quotes instead of single quotes for the string value:
But what to do if we want to retrieve all users having LastName O'Neil? The SQL statement below will fail:
The reason for the failure is the single quote which is part of the string we used in our WHERE criteria. The SQL engine will try to interpret our SQL statement and will consider the single quote inside the string as the end of that string. The remaining part of the SQL statement will be Neil', which cannot be interpreted correctly, thus we'll get an error. So how do we deal with strings having single quotes then?
The answer is simple - by replacing all single quotes in our string with two single quotes. When we have two single quotes together, they are interpreted by SQL as one single quote. Here is our improved SQL statement which will work correctly:
We used the = (Equal) operator in the examples above, but you can use any of the following comparison operators in conjunction with the SQL WHERE keyword:
<> (Not Equal)
> (Greater than)
>= (Greater or Equal)
< (Less than)
<= (Less or Equal)
In addition to the comparison operators you can use WHERE along with logical operators. SQL logical operators are used to combine two or more criterions in the WHERE clause of an SQL statement.
If we want to select all users from our Users table, which live in New York and are born after 10/10/1975 we will use the following SQL query:
Here is the result of the above SELECT:
As you can see we now have to criteria concatenated with the AND logical operator, which means that both conditions have to be true.
If we want to select all users from our Users table, which live in New York or are born after 10/10/1975 we will use the following SQL query:
The result is:
This time the two criteria are joined with OR, which means that all rows satisfying at least one of them will be returned.
You can use the NOT logical operator in your SQL statements too. Consider the following example:
This statement will select all users whose city name doesn't contain the string York. (I've explained the LIKE statement below).
LIKE (similar to)
We'll talk about the LIKE keyword later, but for now it's enough to know that the SQL statement above returns all users with first name starting with the letter S. When you use the % character inside a LIKE expression, the % is considered to be a wildcard (note that the syntax I've used is for SQL Server, and different SQL implementations may have different syntax for wildcard character %).
You can use the WHERE keyword along with the BETWEEN keyword which defines a range:
You can use the WHERE keyword along with the IN keyword which defines a criteria list:
The SQL statement above will return all users from Los Angeles and New York.