Filters

If we’re only interested in part of the data in the table we can filter the table. We have multiple statements that allow us to filter our tables. Filters basically select rows that match certain criteria and return the results back as a filtered data set. Filtering tables does not mutate the original table.

WHERE

The WHERE clause is used to filter records. In our editor we have a table called Customers . If we want to filter customers that are from country “USA” we use the WHERE statement.

SELECT * from Customers WHERE country = "USA";

AND, OR, and NOT

In our previous example, we had only one condition which was “where country is USA.” We can also combine multiple conditions using AND , OR , and NOT . For example, if you want customers from the USA or Brazil, you use the OR statement.

SELECT * from Customers WHERE country = "USA" OR country = "Brazil";

ORDER BY

Most of the time when we filter the table, the data set we get back is unsorted. We can sort this filtered unsorted data set using an ORDER BY statement.

SELECT * from Customers WHERE country = "USA" OR country = "Brazil"

ORDER BY CustomerName ASC;

This will order the filtered results alphabetically . If we want to sort it descending, we replace ASC with DESC .

BETWEEN

Sometimes we would like to select rows whose values satisfy a specific range. We use the BETWEEN statement to select and choose the range.

SELECT * from Products

WHERE Price BETWEEN 10 AND 20;

The statement above filters products whose price falls between 10 and 20.

Note: In a BETWEEN operation, the lower bound and upper bound are both inclusive in nature.

LIKE

Sometimes we want to filter the table with a specific pattern in mind. To do so we use the LIKE statement.

SELECT * from Customers

WHERE CustomerName LIKE 'A%';

The SQL statement above filters the table to show only customers whose name begin with the letter A. If you bring the percentage sign forward it would filter customers whose name ends with the letter A.

GROUP BY

GROUP BY groups the filtered result set into groups. Think of it as a summary group for each column data set.

SELECT COUNT(CustomerID), Country

FROM Customers

GROUP BY Country;

This statement counts the number of customers from each country, then groups it into countries. GROUP BY is mostly used with aggregate functions which we’ll talk about in detail later in the article.

HAVING

HAVING was introduced because the WHERE statement doesn’t work with aggregate functions; it only deals with direct values in the database.

SELECT COUNT(CustomerID), Country

FROM Customers

GROUP BY Country

HAVING COUNT(CustomerID) > 3;

This statement does the same thing as the last example. The only difference is that we only include countries that have more than three customers.