How to Filter Query Results in SQL
Being able to retrieve a set of columns from a database is useful, but we don’t really begin to leverage the power of SQL until we start to filter specific rows.
When you query table information, you can filter records with the
WHERE clause to extract only those records that fulfill a specific expression. Any row that doesn’t meet the conditions (the expression evaluates to false or NULL) is discarded. The
WHERE clause follows the
SELECT column1, column2, ... FROM table_name WHERE expression;
Want to keep
FutureLearn online course,
SQL for Data Analysis
The resulting table has the same number of columns, but generally has considerably fewer rows that contain the information that meets your expression. The complexity of the expression can range from identifying a single record (or group of records) on a single criteria to identifying those with a long series of sub-expressions over many columns.
Expressions are specified with a combination of ‘operators’. We use operators to test relationships between values, to test equality of values, or to evaluate multiple expressions based on logical conditions.
Equality operators compare two values, which are known as operands, to see if the values are equal. The quality operators in SQLite are shown below.
|= or ==||Equal|
|<> or !=||Not equal|
When used in a query, these can be used to select individual records.
SELECT FirstName, LastName FROM Customer WHERE CustomerID = 13;
Or we can use them to retrieve records that meet a specified criteria.
SELECT FirstName, LastName, City FROM Customer WHERE Country = ‘France’;
Or we can use them to return all the records that don’t meet the specified criteria.
SELECT DISTINCT City FROM Customer WHERE Country != ‘France’;
Relational operators compare two operands and return true if the operands meet the specified relationship. Relational operators in SQLite are shown below.
|>=||Greater than or equal|
|<=||Less than or equal|
When used in a query, this can identify records over, or under, certain values. For example, the query below looks for invoices over $20 and returns the customer and invoice identifiers for these orders.
SELECT CustomerID, InvoiceId, Total FROM Invoice WHERE Total > 20;
This would return:
CustomerId InvoiceId Total ---------- ---------- ---------- 45 96 21.86 46 194 21.86 26 299 23.86 6 404 25.86
Comparison operators can also be combined to find values within a range.
SELECT CustomerID, InvoiceId, Total FROM Invoice WHERE Total >= 10 AND Total <= 13;
This would return the following table.
CustomerId InvoiceId Total ---------- ---------- ---------- 17 298 10.91 28 311 11.94 34 312 10.91
That brings us nicely to our final operators—logical operators.
We use logical operators to define multiple conditions required to return rows or records from a statement. SQLite supports the following logical operators.
OR operators require two expressions and will return values depending on how each expression evaluates. The
AND operator requires both expressions to evaluate to true, whereas the
OR operator will return a value if either expression evaluates to true.
The examples below show
OR in action.
sqlite> SELECT FirstName, LastName, Email ...> FROM Customer ...> WHERE FirstName = 'Mark' AND LastName = 'Philips'; FirstName LastName Email ---------- ---------- ------------------ Mark Philips email@example.com sqlite> SELECT FirstName, LastName, Email ...> FROM Customer ...> WHERE FirstName = 'Mark' OR LastName = 'Philips'; FirstName LastName Email ---------- ---------- ------------------ Mark Philips firstname.lastname@example.org Mark Taylor mark.taylor@yahoo.
BETWEEN operator tests whether a value exists in a range of values, and includes its lower and upper expression, like the
>= or ‘<=
operators. ‘BETWEEN’ behaves similar to combining these two operators with an AND` operator. For example, BETWEEN 1 and 10 includes all numbers between 1 and 10, including 1 and 10 themselves.
Earlier, we showed you this query:
SELECT CustomerID, InvoiceId, Total FROM Invoice WHERE Total >= 10 AND Total =< 13;
This could be rewritten with
SELECT CustomerID, InvoiceId, Total FROM Invoice WHERE Total BETWEEN 10 AND 13;
BETWEEN can be used with other data types, such as dates.
SELECT InvoiceId, InvoiceDate, Total FROM Invoice WHERE InvoiceDate BETWEEN '2010-01-01' AND '2010-01-31';
IN operator determines whether a value matches any value in a list. Lists can be a set of fixed values (as shown below), or the results from a subquery (we’ll look at subqueries in the next activity).
When we use a list of fixed values,
IN provides an alternative to multiple
OR operators. The following queries would produce the same results.
SELECT FirstName, LastName, City FROM Customer WHERE City IN ('London', 'Paris', 'Milan'); SELECT FirstName, LastName, City FROM Customer WHERE City = 'London' OR City ='Paris' OR City='Milan';
So far we’ve been testing our expressions with exact values, such as 10, ‘London’, and ‘2010-01-01’. Sometimes we don’t know exactly what we’re looking for. This is where
LIKE comes in. Use
LIKE in your
WHERE clause to query data on partial information, with a ‘pattern’ of information and the wildcard character
% wildcard can be used at the start, end, or start and end of the pattern. Let’s look at some examples to see how this works.
In our first example we’ll match tracks that start with ‘Rock’.
SELECT Composer, Name FROM Track WHERE Name LIKE 'Rock%';
This would return tracks such as ‘Rock Bottom’, ‘Rock The Casbah’, and ‘Rocket’. (‘Rocket’ is included because the wildcard matches spaces and characters.)
To find tracks that end with ‘Rock’, you could use the pattern
’%Rock’. This would return tracks such as AC/DC’s ‘Let There Be Rock’.
Finally, we can use the wildcard at both ends of the pattern.
SELECT Composer, Name FROM Track WHERE Name LIKE '%Rock%';
This would return the results from both of the patterns above, and tracks such as ‘We Will Rock You’ and ‘Clash City Rockers’.
The last logical operator we’ll look at is
NOT operator can be used to negate expressions.
SELECT FirstName, LastName, City FROM Customer WHERE City NOT IN ('London', 'Paris', 'Milan');
This would return customers in cities other than London, Paris, and Milan.
As stated in the table, the
NOT operator can be used with
A final expression we should mention is
IS NULL (or
IS NOT NULL).
NULL is used to indicate unknown information. For example, a customer might not have provided a fax number, or we might not know the composer(s) of a particular music track. These would be
NULL in our database.
We can use
IS NULL and
IS NOT NULL in our
WHERE clauses to gain insight into our data.
The query below would return a list of users who have provided a fax number.
SELECT FirstName, LastName, Fax FROM Customer WHERE Fax IS NOT NULL;
Or we could find how many tracks don’t have information about the composer(s).
SELECT COUNT(TrackID) FROM Track WHERE Composer IS NULL;
This query returns:
COUNT(TrackID) -------------- 978
That’s a lot of missing information about composers!
Share this post
SQL for Data Analysis
Reach your personal and professional goals
Unlock access to hundreds of expert online courses and degrees from top universities and educators to gain accredited qualifications and professional CV-building certificates.
Join over 18 million learners to launch, switch or build upon your career, all at your own pace, across a wide range of topic areas.