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:
| FirstName | LastName | DateOfBirth | City | |
| John | Smith | 12/12/1969 | john.smith@john-here.com | New York |
| David | Stonewall | 01/03/1954 | david@sql-tutorial.com | San Francisco |
| Susan | Grant | 03/03/1970 | susan.grant@sql-tutorial.com | Los Angeles |
| Paul | O'Neil | 09/17/1982 | paul.oneil@pauls-email.com | New York |
| Stephen | Grant | 03/03/1974 | sgrant@sgrantemail.com | Los Angeles |
Consider the following SQL query:
SELECT FirstName, LastName, City FROM Users WHERE City = 'Los Angeles'The result of the SQL expression above will be the following:
| FirstName | LastName | City |
| Susan | Grant | Los Angeles |
| Stephen | Grant | Los Angeles |
Our SQL query used the " sc">SELECT FirstName, LastName FROM Users WHERE City = "Los Angeles"
But what to do if we want to retrieve all users having LastName O'Neil? The SQL statement below will fail:
SELECT FirstName, LastName FROM Users WHERE LastName = 'O'Neil'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:
SELECT FirstName, LastName FROM Users WHERE LastName = 'O''Neil'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:
SELECT FirstName, LastName FROM Users WHERE FirstName <> 'Jon' SELECT FirstName, LastName FROM Users WHERE DateOfBirth > '02/03/1970'>= (Greater or Equal)
SELECT FirstName, LastName FROM Users WHERE DateOfBirth >= '02/03/1970'Here is the result of the above SELECT:
| FirstName | LastName | DateOfBirth | City | |
| Paul | O'Neil | 09/17/1982 | paul.oneil@pauls-email.com | New York |
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:
SELECT FirstName, LastName, DateOfBirth, Email, City FROM Users WHERE City = 'New York' OR DateOfBirth > '10/10/1975'
| FirstName | LastName | DateOfBirth | City | |
| John | Smith | 12/12/1969 | john.smith@john-here.com | New York |
| Paul | O'Neil | 09/17/1982 | paul.oneil@pauls-email.com | New York |
| Stephen | Grant | 03/03/1974 | sgrant@sgrantemail.com | Los Angeles |
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:
SELECT FirstName, LastName, DateOfBirth, Email, City FROM Users WHERE City NOT LIKE '%York%'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)
SELECT FirstName, LastName FROM Users WHERE FirstName LIKE 'S%'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:
SELECT FirstName, LastName FROM Users WHERE DateOfBirth BETWEEN '02/03/1970' AND '10/10/1972'You can use the WHERE keyword along with the IN keyword which defines a criteria list:
SELECT FirstName, LastName FROM Users WHERE City IN ('Los Angeles', 'New York')The SQL statement above will return all users from Los Angeles and New York.