SQL (4) Filtering (BETWEEN, IN, OR, NOT)

In the last article, we’ve talked about the basics of SELECT and WHERE clauses. In this article, we will cover more advanced filtering such like BETWEEN, IN, OR, NOT, and so on.

■ True Basics of WHERE Clauses

Again, let’s take a look at WHERE clause.

SELECT Column-Name
FROM Table-Name
WHERE column-name Operator value;

The types of Operator are as follows:

For example, if you want to look up only the products that the BrandName column is not ‘Patagonia’ in the Products table…

SELECT *
FROM Products
WHERE BrandName <> 'Patagonia';

If you want to look up products with price from 20 to 50:

SELECT *
FROM Products
WHERE Price BETWEEN 20 AND 50;

■ IN, OR, NOT

IN is used when you want to specify a range condition. IN is used in parentheses. If you want to look up only products with Price of 10, 12, and 13, use the following query statements.

SELECT *
FROM Products
WHERE Price IN (10, 12, 13);

OR is used to return rows that meet certain conditions. For example, if you want to query a product whose BrandName is ‘Patagonia’ or ‘Birkenstock’, the query statement is:

Note that you have to rewrite the column name as well as the conditions after the OR.

SELECT *
FROM Products
WHERE BrandName = 'Patagonia' OR BrandName = 'Birkenstock';

IN and OR operate on the same principle. However, in the case of IN, you can enter multiple lists and it has the advantage of running faster than OR. Also, you don’t have to worry about the order when using IN. OR should be more careful when writing with other operators. Let’s look at the following example.

For the query on the left, some people have less weight than 210. If you want to query a person whose weight is greater than 210 among those whose birthCity is Denver or Trinidad, you should put the OR statement in parentheses, as in the query statement on the right.

NOT Operator can be used as follows:

SELECT * 
FROM People
WHERE NOT birthCity = 'Denver' AND 
NOT birthCity = 'Trinidad';