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:
|Operator Type Type||meaning|
|<>|| Not the same |
(in some versions it is also known as != )
|>=||greater or equal|
|<=||less or equal|
|BETWEEN||there are between some values|
|IS NULL||NULL value|
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';