Structured Query Language (SQL) was developed by IBM researchers in the 1970s. SQL—sometimes pronounced “sequel”—is used to store, retrieve, and manipulate data in databases.
Many data management systems have user-friendly ways to export entire sets of data, or sometimes just the transactions from a date range, for example, but what if you need a list of records with very specific criteria?
Let’s say you need a list of every person in your database who has been an email subscriber for the past six months, hasn’t purchased anything, lives in California, and has a birthday next month. You could use a spreadsheet to filter the list down, but that’s very time-consuming, especially if you need this list regularly. That’s where SQL can help.
You could build a SQL query for this and re-use it every time that specific list is needed. Depending on your marketing software, you could even automate this task, scheduling the SQL to run hourly, daily, or weekly to update your list.
SQL is also used to display data on a webpage. WordPress uses MySQL to query data and display it on the website, and a custom query written by a developer can display very specific data for users.
Getting started with SQL queries
A SQL query should start with the
SELECT keyword, followed by the columns to be queried, the
FROM keyword, and the name of the table to query.
Notice in the example that the columns (Id, Email, FirstName, LastName) are separated by commas. You do not need to separate columns (or even new clauses) with line breaks, however, but it’s a good idea as a way to keep your SQL human-readable.
FROM keyword designates the table, contacts, from which the columns are queried. Everything after
FROM contacts in the above example is part of a
WHERE clause, which we will explore later in this article. This query simply searches the table called contacts for the data in the specified columns and filters the query to only select contacts who are opted into email communication, are not opted out, and who have a value in the Email column (not null).
Note: In SQL, there is a difference between null and an empty string (‘’). It’s usually a good idea to check for both when validating data.
In SQL, an alias is a column name that is defined within the query. By default, columns in the results table are titled based on the field name. But you can change the title of a column by using an alias.
If the field name is email_address_contact, you can rename it in the results to Email by placing the alias immediately after the actual column name. You can optionally use the
AS keyword for readability.
The following examples will both change the column title to Email.
Aliases become really helpful when using functions. A SQL function can cause problems if it shows up in the results table when working with a programming language like PHP to display results on a webpage.
One last note on aliases: It’s possible to create column aliases with spaces by enclosing the alias in single quotes. This can improve readability, but again, some programming languages may have difficulty handling column titles with spaces.
Filtering a query
In order to filter a query for specific criteria, use the
WHERE clause, followed by one or more conditions. A condition should have a field, a comparison operator of some kind, and a value or range of values to check for. If using multiple conditions, separate them with the
Notice the use of parentheses to distinguish between condition groups. The example is searching the database for records matching any of the following conditions:
- Birthdate is after 1980 and the first initial of FirstName is B
- LastName contains any one letter(“_”), with the second letter being A, followed by one or more unspecified characters (“%”)
- LastName equals Caples and CustomerSince is a date equal to or after January 1, 2020
If a record meets any of those conditions, it will be returned in the results table if it also meets the condition that the Birthdate field is not null. (To find records that are null, use IS NULL in the condition.)
There are three primary data types in SQL: string, numeric, and date/time. Each of these data types has multiple subtypes within it. A good resource for information about each data type can be found at W3Schools.
Let’s take a look at our earlier example querying people who meet the following conditions:
- Email subscriber for past 6 months
- Zero purchases
- Lives in California
- Has a birthday next month
Using this query, we can capture this list of people and send an email inviting them to a product event near them with a free birthday gift offer.
Notice that we don’t actually need to count the number of purchases on the customer’s record—only whether there’s a value for LastPurchaseDate. You can get creative with your SQL queries to prevent unnecessary clauses. And be sure to look up all of the functions and keywords used in these examples, since I haven’t explained them all.
It’s important to remember that a SQL query depends on the data available in the database. If we don’t have a column for the date a person subscribes to emails, or if a contact has missing birthdate data, for example, our query will be limited and less reliable. Clean data systems are very important.
There are many more functions and features within SQL, but this is just an introduction. There are a lot of good websites for learning SQL, including Codecademy, LearnSQL, StackSkills, and W3Schools. (Here’s a free SQL cheatsheet!) You can also find good tutorials on YouTube and solutions on StackOverflow. There are plenty of resources to help you learn the basics (and more) of Structured Query Language.