SQL Functions
TOP
❮ ❯

SQL-Lesson 4. Data filtering (WHERE)

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.


1. Simple filtering by WHERE operator.

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.

An example of a query for selecting text values:

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.

An example of a query for selecting 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

2. Filtering by value range (BETWEEN)

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.

3. Selection of empty records (IS NULL)

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.

4. Advanced filtering (AND, OR)

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'

5. Advanced filtering (IN operator)

Run SQLSELECT * 
FROM Sumproduct 
WHERE ID IN (4, 12, 58, 67)

The IN operator performs the same function as OR, but has several advantages:

  • When working with long lists, sentences with IN are easier to read;
  • Fewer operators are used, which speeds up request processing;
  • The most important advantage of IN is that in its construction you can use the additional construction SELECT, which
    • відкриває великі можливості для створення складних підзапитів.

    6. Advanced filtering (NOT operator)

    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.

    ❮ Previous Next ❯