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 FROM clause:

SELECT column1, column2, ...
FROM table_name
WHERE expression;

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.

Creating expressions

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

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.

When used in a query, these can be used to select individual records.

For example:

SELECT FirstName, LastName 
FROM Customer 
WHERE CustomerID = 13;

Or we can use them to retrieve records that meet a specified criteria.

For example:

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.

For example:

SELECT DISTINCT City 
FROM Customer
WHERE Country != ‘France’;

Relational operators

Relational operators compare two operands and return true if the operands meet the specified relationship. Relational operators in SQLite are shown below.

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.

For example:

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.

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.

The AND and 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 AND and OR in action.

sqlite> SELECT FirstName, LastName, Email
 ...> FROM Customer
 ...> WHERE FirstName = 'Mark' AND LastName = 'Philips';
FirstName LastName Email 
---------- ---------- ------------------
Mark Philips mphilips12@shaw.ca

sqlite> SELECT FirstName, LastName, Email
 ...> FROM Customer
 ...> WHERE FirstName = 'Mark' OR LastName = 'Philips';
FirstName LastName Email 
---------- ---------- ------------------
Mark Philips mphilips12@shaw.ca
Mark Taylor mark.taylor@yahoo.

The 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 BETWEEN:

SELECT CustomerID, InvoiceId, Total
FROM Invoice
WHERE Total BETWEEN 10 AND 13;

BETWEEN can be used with other data types, such as dates.

For example:

SELECT InvoiceId, InvoiceDate, Total
FROM Invoice
WHERE InvoiceDate BETWEEN '2010-01-01' AND '2010-01-31';	

The 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 %. The % 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. The NOT operator can be used to negate expressions.

For example:

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 IN, BETWEEN, and LIKE.

IS NULL

A final expression we should mention is IS NULL (or IS NOT NULL).

In SQLite, 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!