In most cases, it is necessary to receive not all records, but only those that meet certain criteria. That is why there is a special operator WHERE in SQL for sampling filtering.
Let's select from our table, for example, records that relate only to a certain product. To do this, we specify an additional selection parameter that will filter the values by the Product column.
Run SQLSELECT *
FROM Sumproduct
WHERE Product = 'Bikes'
As you can see, the selection condition is enclosed in single quotes, which is mandatory when filtering text values. Quotation marks are not required when filtering numeric values.
Run SQLSELECT *
FROM Sumproduct
WHERE Amount > 40000
ORDER BY Amount
In this example, we selected records in which the sales revenue was more than $40,000 and, additionally, sorted all records in ascending order by the Amount field.
The table below lists the conditional statements supported by SQL:
| Operation sign | Value |
|---|---|
| = | Is equal to |
| <> | Not equal to |
| < | Less |
| <= | Less than or equal to |
| > | More |
| >= | Greater than or equal to |
| BETWEEN | Between two values |
| IS NULL | There is no record |
The operator BETWEEN is used to select data that lies in a certain range. In the next query, all values that lie between $1,000 and $2,000, inclusive, in the Amount field will be selected.
Run SQLSELECT *
FROM Sumproduct
WHERE Amount BETWEEN 1000 AND 2000
The order of sorting will depend on the order of the fields in the query. That is, in our case, the data will first be sorted by the Amount column, and then by City.
In SQL, there is a special operator for selecting empty records (called NULL). An empty entry is any cell in the table in which no character is entered. If 0 or a space is entered in the cell, it is considered that the field is filled.
SELECT *
FROM Sumproduct
WHERE Amount IS NULL
In the example above, we intentionally removed two values in the Amount field to demonstrate the NULL operator.
The SQL language is not limited to filtering by one condition, for your own purposes you can use quite complex constructions to select data simultaneously by many criteria. For this, SQL has additional operators that extend the capabilities of the WHERE operator. Such operators are: AND, OR, IN, NOT. We will give several examples of the operation of these operators.
Run SQLSELECT *
FROM Sumproduct
WHERE Amount > 40000 AND City = 'Toronto'
Run SQLSELECT *
FROM Sumproduct
WHERE Month = 'April' OR Month = 'March'
Let's combine the AND and OR operators. To do this, we will make a selection of bicycles (Bikes) and skates (Skates), which were sold in March.
Run SQLSELECT *
FROM Sumproduct
WHERE Product = 'Bikes' OR Product = 'Skates' AND Month = 'March'
We can see that many values were included in our sample (in addition to March, also January, February, and April). What is the reason? And the fact that SQL has command execution priorities. That is, the AND operator has a higher priority than the OR operator, so records with skates sold in March were selected first, followed by all records related to bicycles.
So, to get the correct sampling, we need to change the command execution priorities. For this, we will use parentheses, as in mathematics. Then, the operators in parentheses will be processed first, and then all the rest.
Run SQLSELECT *
FROM Sumproduct
WHERE (Product = 'Bikes' OR Product = 'Skates') AND Month = 'March'
Run SQLSELECT *
FROM Sumproduct
WHERE ID IN (4, 12, 58, 67)
The IN operator performs the same function as OR, but has several advantages:
Run SQLSELECT *
FROM Sumproduct
WHERE NOT City IN ('Toronto', 'Montreal')
The keyword NOT allows you to remove unnecessary values from the sample. Also, its feature is that it is placed before the name of the column involved in filtering, and not after.