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.
Operators in The WHERE Clause
The following operators can be used in the WHERE clause:
Operator Description
= Equal
<> Not equal. Note: In some versions of SQL this operator may be written as !=
> Greater than
< Less than
>= Greater than or equal
<= Less than or equal
BETWEEN Between an inclusive range
LIKE Search for a pattern
IN To specify multiple possible values for a column
| FirstName | LastName | DateOfBirth | City | |
| John | Smith | 12/12/1969 | john.smith@softsekar.com | New York |
| David | Stonewall | 01/03/1954 | david@softsekar.com | San Francisco |
| Susan | Grant | 03/03/1970 | susan.grant@softsekar.com | Los Angeles |
| Paul | O'Neil | 09/17/1982 | paul.oneil@softsekar.com | New York |
| Stephen | Grant | 03/03/1974 | sgrant@softsekar.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 "=" (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:
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:
<> (Not Equal)
SELECT FirstName, LastName FROM Users WHERE FirstName <> 'Jon'
> (Greater than)
SELECT FirstName, LastName FROM Users WHERE DateOfBirth > '02/03/1970'
>= (Greater or Equal)
SELECT FirstName, LastName FROM Users WHERE DateOfBirth >= '02/03/1970'
< (Less than)
SELECT FirstName, LastName FROM Users WHERE DateOfBirth < '02/03/1970'
<= (Less or Equal)
SELECT FirstName, LastName FROM Users WHERE DateOfBirth <= '02/03/1970'
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:
SELECT FirstName, LastName, DateOfBirth, Email, City FROM Users WHERE City = 'New York' AND DateOfBirth > '10/10/1975'
Here is the result of the above SELECT:
FirstName | LastName | DateOfBirth | City | |
| Paul | O'Neil | 09/17/1982 | paul.oneil@softsekar.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'
The result is:
| FirstName | LastName | DateOfBirth | City | |
| John | Smith | 12/12/1969 | john.smith@softsekar.com | New York |
| Paul | O'Neil | 09/17/1982 | paul.oneil@softsekar.com | New York |
| Stephen | Grant | 03/03/1974 | sgrant@softsekar.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.
0 comments:
Post a Comment