Previously, we covered the SQL SELECT Statement, to pull data from a SQL database. Here, we’ll constrain our search for items that match a given string, or pattern using the WHERE clause to filter search results, rather than getting all of the records and parsing the output.
The WHERE clause extracts records that fulfill a specified string and follows the general syntax as follows, replacing the word column with the name of the column in one of your tables and the word table with the name of a table that you’d like to search within:
SQL WHERE Syntax
SELECT column,column
FROM table
WHERE column operator value;
Below is a selection from the “Customers” table that showed in our first article an we will use it to run some SQL sorting statements using the ORDER BY keyword:
ID Site Contact Address City Zip Country
1 Krypted Charles Edge my house Minneapolis 55418 US
2 Apple Tim Cook spaceship Cupertino 95014 US
3 Microsoft Satya Nadella campus Redmond 98053 US
4 Facebook Mark Zuckerberg foodhall Menlo Park 94025 US
5 JAMF Dean Hager Grain Exchange Minneapolis 55418 US
The following SQL statement selects all the customers with the zip code (Zip column) matching “55418”, in the “Customers” table:
SELECT * FROM Customers
WHERE Zip=55418;
In the above search, I didn’t have to quote what I was looking for. The reason is that an integer doesn’t require quoting; however, if we were searching for a name, or any other text record, we should use quotes. So to repeat the search for Site, looking for Krypted, we would use the following:
SELECT * FROM Customers
WHERE Site=Krypted;
We used an = operator, but it’s worth noting that there are a number of others that can be super-helpful. The following operators are available when using a WHERE clause:
- = Equal
- <> or != Not equal to
- > Greater than
- IN Indicates multiple potential values for a column
- < Less than
- >= Greater than or equal
- <= Less than or equal
- BETWEEN Between an inclusive range
- LIKE Looks for a provided pattern
So using another operator from above, we can also search for all sites that do not contain Krypted, using the following:
SELECT * FROM Customers
WHERE Site!=Krypted;
So far, we’ve looked at searching for exact matches. We can also fuzzy our logic up by looking for items that contain a pattern AND something else, using the LIKE operator in a WHERE clause to search for items that contain part of a pattern in a search. The syntax for a SQL LIKE is similar, it begins with the SELECT statement that we’ve used throughout these articles so far, and then continues with the WHERE and then a LIKE to define the pattern, as follows, replacing column with the name of one of your columns, table with the name of the table that column is in and pattern with the actual search you’re looking to run:
SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern;
The following SQL statement selects all customers with a City starting with the letter “M” with the % inside a single quote to show where the wildcard data is (in this case, anything that appears after the letter M in a city name):
SELECT * FROM Customers
WHERE City LIKE 'M%';
The following SQL statement selects all customers with a City containing the pattern “Park (e.g. “Menlo Park”):
SELECT * FROM Customers
WHERE Country LIKE '%Park%';
You can also add NOT in front of Like to search for records that do not match a pattern (note that you don’t have all the same operators available, so this helps to get a little more logic in searches when needed).
The following SQL statement selects all customers with Country NOT containing the pattern “US” (of which there are none in our sample data set):
SELECT * FROM Customers
WHERE Country NOT LIKE '%US%';
Or if we weren’t sure that we wanted to work in a spaceship, we could search for all addresses that weren’t spaceship:
SELECT * FROM Customers
WHERE Address NOT LIKE '%spaceship%';
Overall, the SELECT statement has some pretty basic logic and operations, because searching for data within a given table is a pretty straight forward task, you have a pattern, you look for items that match that pattern. As these articles continue, we’ll get into slightly more complex operations, but much of the work that we do with SQL is done after a query with another tool in order to get our data to display or be manipulated in ways that SQL doesn’t do on its own. One of the beauties of SQL, and why it can be so fast, is that it’s simple.