SQL is considered one of the most common programming languages ​​for working with databases. The language constructs allow you not only to create a database, but also to carry out various manipulations with it to change data or select it.

To select data from the database, use the Select [data set] from [table name] construct. Experience shows that in 80% of cases when using queries to select data, you need to apply various conditions - parameters. To do this, the SQL-Where condition was introduced into the language as an addition to the query, to complicate it.

Ways to Use the Where Condition

Quite often, the programmer needs to select, mainly for reporting, data stored in the database. To do this, it may not be enough to build a simple select query. As a rule, it is also necessary to take into account various conditions, sampling parameters, of which there can be quite a lot, or check them. whether the data falls within a defined range or is in a specific table.

The SQL-Where construct can be used to set conditions for selecting data or to check whether data is included in a select or a third-party table.

Using Where to Set Selection Options

If you need to specify certain parameters for selection from a reporting database, the syntax of the SQL-Where construct can be organized quite simply. To do this, you can use the following rules:

You need to build a standard query using the Select * from construct.

Using the Join key construct, determine from which tables the selection will be made.

Using the Where construct, specify a list of parameters for selection.

These kinds of queries are quite simple to construct and do not cause difficulties even for beginners.

Using a Construct to Test an Occurrence

If a programmer is tasked with not only selecting data from a table based on a condition, but also checking its inclusion in one or more tables of another plan, the SQL-Where construct will be indispensable.

Using the syntax of this construct, you can build the same conditions, but with nested queries that will check whether the selected rows are included in a set of third-party database tables.

As a rule, for such purposes, a temporary table is formed in which the entire set of data necessary to check the occurrence is recorded.

Examples of using Where

Examples will now be given. To begin with, let's imagine that there are two tables with data - Tovar and TovarAmount. The first contains the names of the goods being sold, the price, the date of sale and the customer who purchased the goods. The second indicates the availability of the product, or more precisely, which one is in stock.

An example of a request with a parameter that will indicate all products sold over a certain number of days would be the following construction:

Select * from Product

Where T_Date >= ‘12/01/2016’ and T_Date<= ‘’12/07/1016 and T_PriceOut >0

A query of this type will return a list of products, data from the table, that were sold in the first seven days of December, as indicated by one of the selection conditions: T_PriceOut >0.

If we consider the condition for the withdrawal of goods that are in stock, then the design will be as follows:

Select * from Product

Where T_Tovar in (select TA_Tovar where TA_ Amount >0)

There can be many nested conditions in Where, but it is worth mentioning that the more conditions are imposed, the longer the query will take to run. This is precisely the reason for using temporary tables. It is much faster to create several of these and then compare the data in them than to build a condition with several levels of data verification.

In this article, I will tell you about the conditional CASE operator, without which data accounting in a number of tasks would turn into piles of pieces of code, as well as how to use it in SQL queries.

No matter how well you design your database, there will always be problems where conditional statements can't get by. For example, instead of huge numbers, get a verbal description, or, depending on the presence (absence) of data, add additional characters to the line, or as a more complex example, depending on various nuances, assign a record to one or another group. There are a lot of situations.

In principle, of this operator there are at least two forms in different databases. The first option resembles a regular switch from any programming language. The second one depends on logical expressions.

However, for the purposes of this article, I will consider the second option, since it does not have problems with situations like CASE WHEN NULL (null within the database is not a specific value, so it cannot be used in a switch-like statement). In addition, in everyday life, tasks most often occur specifically for the second option - calculation through logical expressions. Therefore, it is better to learn right away and continue to use it.

Typically, it is described like this (syntax may vary depending on the database):

CASE WHEN bool_expression1 THEN value1 ..... WHEN bool_expressionN THEN valueN ELSE valueElse END

bool_expressionX is a boolean condition

valueX is the value that will be substituted if the corresponding logical condition is met

valueElse is the value that will be substituted if no condition was previously met.

After this little information, let's move on to practice.

Note: By the way, it is worth knowing that usually this operator can be used not only in select, but also in any place where fields can be used. For example, when joining tables or even filtering (having) when grouping (group by).

Conditional statement CASE...WHEN...THEN

To better understand the conditional statement CASE...WHEN...THEN, let's imagine a small problem. Let's say you have a table with data about customers and their total number of purchases. And the task is to dynamically create a discount. It would be possible, of course, to set the discount manually. But, you have a threshold, and the thresholds are hardwired (something like: an amount greater than 1000 will receive a 2% discount, and more than 5000 will receive a 5%) and you would like to automate this process so that you don’t have to look for errors every time delve into the database (the client has accumulated the required amount - the discount automatically appears).

Let's take a conditional client table with three clients. For example, they will be quite sufficient.

Now, let's set several conditions for automatically providing a discount based on the task. At the same time, we believe that the client is given the maximum discount in any case.

1. Amount from 1000 - 2% discount

2. Amount from 5000 - 5% discount

3. Amount from 10,000 - 8% discount

4. Number of orders from 10 - 7% discount

5. Number of orders from 20 - 8% discount

As you can see, the discount depends on two factors: the amount and quantity. Now, let’s try to create conditions from them based on the discount, that is, the rules are the opposite, so that they can be used in an sql query. We get the following:

1. 2% - Amount from 1000 to 4999 and the number of orders is less than 10.

2. 5% - Amount from 5000 to 9999 and the number of orders is less than 10.

3. 7% - Number of orders from 10 to 19 and amount less than 10,000

4. 8% - Quantity from 20 or amount from 10,000

Now all that remains is to write it down. Let's get the following sql query

We display the name and other data select name, order_count, total_sum, -- And now we display the discount CASE -- First rule 2% WHEN c.total_sum >= 1000 and c.total_sum<= 4999 and c.order_count < 10 THEN 2 -- Второе правило 5% WHEN c.total_sum >= 5000 and c.total_sum<= 9999 and c.order_count < 10 THEN 5 -- Третье правило 7% WHEN c.total_sum < 10000 and c.order_count >= 10 and c.order_count<= 19 THEN 5 -- Четвертое правило 8% WHEN c.total_sum >= 10000 or c.order_count >= 20 THEN 5 -- No rules are met, which means the discount is 0. ELSE 0 END as discount from client c

As a result of execution we get the following table:

As you can see, two customers received an 8 percent discount and one customer received a 2 percent discount. In this case, with each order the percentage will be automatically calculated and you will not need to adjust anything. For example, if Petya’s amount increases to 5000, then his discount will automatically rise to 5% (at least, since there are still a number of orders).

In most cases, you do not need to retrieve all records, but only those that meet certain criteria. Therefore, to filter the sample in SQL there is a special operator WHERE.

1. Simple filtering using the WHERE operator.

Let's, for example, select records from our table that relate only to a specific product. To do this we will indicate additional parameter selection that will filter the value by column Product.

Example query for selecting text values:

SELECT * FROM Sumproduct WHERE Product = "Bikes"

As you can see, the selection condition is enclosed in single quotes, which is mandatory when filtering text values. When filtering numeric values, quotes are not needed.

Example query for selecting numeric values:

SELECT > 40000 ORDER BY Amount

In this example, we selected records in which sales revenue amounted to more than $40 thousand and, additionally, all records were sorted in ascending order by field Amount

The table below lists the list of conditional statements supported SQL:

2. Filtering by value range ( BETWEEN).

To select data that lies in a certain range, the operator is used BETWEEN. The following query will select all values ​​ranging from 1000 $ V 2000 $ inclusive, in the field Amount.

SELECT * FROM Sumproduct WHERE Amount BETWEEN 1000 AND 2000

The sorting order will depend on the order of the fields in the request. That is, in our case, first the data will be sorted by column Amount, and then by City.

3. Selection of empty records ( IS NULL).

IN SQL there is a special operator for selecting empty records (called NULL). A blank entry is any cell in a table in which no characters have been entered. If entered into a cell 0 or space, then the field is considered to be filled.

SELECT * FROM Sumproduct WHERE Amount IS NULL

In the example above, we deliberately removed two values ​​in the field Amount to demonstrate the operator's work NULL.

4. Advanced filtering ( AND, OR).

Language SQL is not limited to filtering by one condition; for your own purposes, you can use quite complex structures to select data simultaneously according to many criteria. For this purpose in SQL there are additional operators that expand the operator's capabilities WHERE. Such operators are: AND, OR, IN, NOT. Here are some examples of how these operators work.

SELECT * FROM Sumproduct WHERE Amount > 40000 AND City = "Toronto"

SELECT * FROM Sumproduct WHERE Month= "April"OR Month= "March"

Let's combine the operators AND And OR. To do this, we will make a selection of bicycles ( Bikes ) and skates ( Skates ), which were sold in March (March ).

SELECT * FROM Sumproduct WHERE Product= "Bikes"OR Product= "Skates" AND Month= "March"

We see that our sample included many values ​​(except for March ( March), also January ( January), February ( February) and April ( April)). What is the reason? And the fact is that SQL has command execution priorities. That is, the operator AND has higher priority than operator OR, so first the records with skates that were sold in March were selected, and then all the records related to bicycles.

So, to get the correct selection, we need to change the command execution priorities. For this we use brackets, as in mathematics. Then, the operators in brackets will be processed first, and then all the others.

SELECT * FROM Sumproduct WHERE (Product= "Bikes"OR Product= "Skates") AND Month= "March"

5. Advanced filtering ( IN operator).

SELECT * FROM Sumproduct WHERE IDIN (4, 12, 58, 67)

Operator IN performs the same function as OR, however, has a number of advantages:

• When working with long lists, a clause with IN easier to read;
• Fewer operators are used, which speeds up request processing;
• The most important advantage IN is that an additional structure can be used in its design SELECT, What
opens up great opportunities for creating complex subqueries.

6. Advanced filtering ( NOT operator).

SELECT * FROM Sumproduct WHERE NOT CityIN ("Toronto", "Montreal")

Keyword NOT allows you to remove unnecessary values ​​from the sample. Another unique feature is that it is placed before the name of the column involved in filtering, and not after.

It is difficult to explain the syntax for the SQL Server WHERE clause, so let's look at some examples.

We"ll start by looking at how to use the WHERE clause with only a single condition.

SELECT * FROM employees WHERE first_name = "Jane";

In this SQL Server WHERE clause example, we"ve used the WHERE clause to filter our results from the employees table. The SELECT statement above would return all rows from the employees table where the first_name is "Jane". Because the * is used in the SELECT, all fields from the employees table would appear in the result set.

Example - Using AND condition

Let's look at how to use the WHERE clause with the AND condition.

SELECT * FROM employees WHERE last_name = "Anderson" AND employee_id >= 3000;

This SQL Server WHERE clause example uses the WHERE clause to define multiple conditions. In this case, this SELECT statement uses the AND condition to return all employees that have a last_name of "Anderson" and the employee_id is greater than or equal to 3000.

Example - Using OR condition

Let's look at how to use the WHERE clause with the OR condition.

SELECT employee_id, last_name, first_name FROM employees WHERE last_name = "Johnson" OR first_name = "Danielle";

This SQL Server WHERE clause example uses the WHERE clause to define multiple conditions, but instead of using the AND condition , it uses the OR condition . In this case, this SELECT statement would return all employee_id, last_name, and first_name values ​​from the employees table where the last_name is "Johnson" or the first_name is "Danielle".

Example - Combining AND & OR conditions

Let's look at how to use the WHERE clause when we combine the AND & OR conditions in a single SQL statement.

SELECT * FROM employees WHERE (state = "California" AND last_name = "Smith") OR (employee_id = 82);

This SQL Server WHERE clause example uses the WHERE clause to define multiple conditions, but it combines the AND condition and the OR condition . This example would return all employees that resides in the state of "California" and whose last_name is "Smith" as well as all employees whose employee_id is equal to 82.

The parents determine the order that the AND and OR conditions are evaluated. Just like you learned in the order of operations in Math class!

Example - Joining Tables

Let's look at how to use the WHERE clause when we join multiple tables together.

SELECT employees.employee_id, contacts.last_name FROM employees INNER JOIN contacts ON employees.employee_id = contacts.contact_id WHERE employees.first_name = "Sarah";

This SQL Server WHERE clause example uses the WHERE clause to join multiple tables together in a single SELECT statement. This SELECT statement would return all rows where the first_name in the employees table is "Sarah". And the employee s and contacts tables are joined on the employee_id from the employees table and the contact_id from the contacts table.

When selecting records from a table, it is almost always necessary to set certain conditions by which we determine which records we need and which we do not. And these conditions can be set using WHERE clauses in SQL. I already introduced you to it, but in this article I decided to introduce you to WHERE a little closer.

I'll start with simple example sampling using WHERE clauses in SQL:

SELECT * FROM table WHERE count=5

Records will be returned in which the field " count" matters 5 . Now let's complicate the query:

SELECT * FROM table WHERE count=5 AND id< 100

Thus, records will be returned whose field " count" matters 5 AND field " id" matters less 100 .

Of course, you can use other logical operations. Their full list:

• ! (negation)
• AND (AND)
• OR (OR)
• XOR (EXCLUSIVE OR, sometimes also called MOUNTING OR, but this name is found mainly in microprocessor literature)

Example using multiple logical operators:

SELECT * FROM table WHERE !(id<= 120 AND (count=10 OR date > "10/11/1980"))

This is, at first glance, complex SQL query. Try to figure it out on your own.

Also WHERE clause in SQL may contain LIKE. LIKE allows you to determine whether a specified string matches a specific pattern. To make it a little clearer, I’ll give an example:

SELECT * FROM table WHERE text LIKE "%some text%"

Given SQL query will return result_set containing records in which the field " text" has the following text: " some text". Note that this is not an equality test. The text may be huge, but if it contains the line: " some text", That LIKE will return true.

Let me write how to set the pattern for LIKE:

• % - this is what you and I used. It is used most often and means it any string of any length. In fact, the line " %some text%" we say that any string of any length comes first, then " some text", and then again any string of any length. If the text matches this pattern, then return true, otherwise false.
• is a single character. To use this template you must specify ranges, for example, like this: " some%". This pattern will mean that first comes 1 character (any character from a to z), then " some" and then any string of any length.
• _ is any single character.
• [^] - this is the opposite. For example, you can give this example: " [^az]some_". This pattern means that any character comes first, but only NOT "a" And NOT "z". Next should be the line " some", and then only one single character.

Knowledge and ability to use LIKE very important, believe my experience. The simplest example of using LIKE- This site search. After all, the content is in the database, and you need to pull out only those records that contain the string specified in the search string. And then it comes to the rescue LIKE. This is exactly how I implemented my search on this site.

Review