How to Filter Query Results in SQL
WHEREclause 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
WHEREclause follows the
SELECT column1, column2, ... FROM table_name WHERE expression;
Want to keep
FutureLearn online course,
SQL for Data Analysis
Equality operatorsEquality 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|
SELECT FirstName, LastNameFROM CustomerWHERE CustomerID = 13;
SELECT FirstName, LastName, City FROM Customer WHERE Country = ‘France’;
SELECT DISTINCT CityFROM Customer WHERE Country != ‘France’;
Relational operatorsRelational 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|
SELECT CustomerID, InvoiceId, TotalFROM InvoiceWHERE Total > 20;
Comparison operators can also be combined to find values within a range. For example:
CustomerId InvoiceId Total---------- ---------- ---------- 45 96 21.8646 194 21.8626 299 23.866 404 25.86
SELECT CustomerID, InvoiceId, TotalFROM InvoiceWHERE Total >= 10 AND Total <= 13;
That brings us nicely to our final operators—logical operators.
CustomerId InvoiceId Total---------- ---------- ---------- 17 298 10.9128 311 11.9434 312 10.91
Logical operatorsWe use logical operators to define multiple conditions required to return rows or records from a statement. SQLite supports the following logical operators.
ORoperators require two expressions and will return values depending on how each expression evaluates. The
ANDoperator requires both expressions to evaluate to true, whereas the
ORoperator will return a value if either expression evaluates to true. The examples below show
sqlite> SELECT FirstName, LastName, Email...> FROM Customer...> WHERE FirstName = 'Mark' AND LastName = 'Philips'; FirstName LastName Email---------- ---------- ------------------ Mark Philips firstname.lastname@example.org> SELECT FirstName, LastName, Email...> FROM Customer...> WHERE FirstName = 'Mark' OR LastName = 'Philips'; FirstName LastName Email---------- ---------- ------------------ Mark Philips email@example.com Mark Taylor mark.taylor@yahoo.
BETWEENoperator tests whether a value exists in a range of values, and includes its lower and upper expression, like the
operators. ‘BETWEEN’ behaves similar to combining these two operators with anAND` 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;
SELECT CustomerID, InvoiceId, Total FROM Invoice WHERE Total BETWEEN 10 AND 13;
BETWEENcan 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';
INoperator 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,
INprovides an alternative to multiple
ORoperators. The following queries would produce the same results.
SELECT FirstName, LastName, CityFROM CustomerWHERE City IN ('London', 'Paris', 'Milan');SELECT FirstName, LastName, CityFROM CustomerWHERE City = 'London' OR City ='Paris' OR City='Milan';
LIKEcomes in. Use
WHEREclause 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, NameFROM TrackWHERE Name LIKE 'Rock%';
’%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, NameFROM TrackWHERE Name LIKE '%Rock%';
NOToperator can be used to negate expressions. For example:
SELECT FirstName, LastName, City FROM Customer WHERE City NOT IN ('London', 'Paris', 'Milan');
NOToperator can be used with
IS NULLA final expression we should mention is
IS NOT NULL). In SQLite,
NULLis 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
NULLin our database. We can use
IS NOT NULLin our
WHEREclauses 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;
SELECT COUNT(TrackID)FROM TrackWHERE Composer IS NULL;
That’s a lot of missing information about composers!
COUNT(TrackID) -------------- 978
SQL for Data Analysis
Our purpose is to transform access to education.
We offer a diverse selection of courses from leading universities and cultural institutions from around the world. These are delivered one step at a time, and are accessible on mobile, tablet and desktop, so you can fit learning around your life.
We believe learning should be an enjoyable, social experience, so our courses offer the opportunity to discuss what you’re learning with others as you go, helping you make fresh discoveries and form new ideas.
You can unlock new opportunities with unlimited access to hundreds of online short courses for a year by subscribing to our Unlimited package. Build your knowledge with top universities and organisations.