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

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

SELECT * FROM table WHERE count = 5

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

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

Thus, records with the field " count" has the meaning 5 AND field " id"matters less 100 .

Of course, you can use other logical operations as well. 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 boolean operators:

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

This is so, at first glance, complex SQL query... Try to figure it out yourself.

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

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

The 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 can be huge, but if it contains the line:" some text", then LIKE will return true.

Let's write how the template for LIKE is set:

• % - this is what we have used with you. 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 need to specify ranges, for example, like this: " some%". This pattern will mean that first comes 1 character (any character from a before z), Further " some"and then any string of any length.
• _ is any single character.
• [^] 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 there should be a line" some"followed by only one single character.

Knowledge and ability to use LIKE very important, trust my experience. Simplest example using LIKE- this is 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 comes to the rescue LIKE... This is how the search is implemented by me on this site.

Within the framework of this article, I will tell you about the conditional CASE statement, without which data accounting in a number of tasks would turn into a heap 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 conditionals are indispensable. For example, instead of huge numbers, get a verbal description, or, depending on the presence (absence) of data, add additional characters to the string, or, as a more complex example, depending on different nuances, assign the entry to a particular group. There are a lot of situations.

Basically, at this operator there are at least two forms in different bases data. The first option resembles a regular switch from any programming language. The second one depends on boolean expressions.

However, within the framework 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, therefore it cannot be used in a switch statement like this). In addition, in everyday life, tasks are most often encountered for the second option - computation through logical expressions. Therefore, it is better to immediately learn and continue to use it.

Usually, it is described like this (the 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 boolean condition is met

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

After such a little help, 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 conditional operator CASE ... WHEN ... THEN, let's imagine a small task. Let's say you have a table with data about customers and their total number of purchases. And the task is to dynamically generate a discount. It would be possible, of course, to manually set the discount. But, you have a threshold, and the thresholds are hardwired (something like - the amount is more than 1000 get a discount of 2%, and more than 5000 - get 5%) and you would like to automate this process so that you do not have to look for errors and every time dig in the database (the client has accumulated the required amount - the discount has automatically appeared).

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

Now, let's set several conditions for automatically granting 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 10000 - 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 the 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 a 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% - The number of orders from 10 to 19 and the amount is less than 10,000

4.8% - Quantity from 20 or amount from 10000

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

Display the name and other data select name, order_count, total_sum, - And now display the CASE discount - 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 rule is fulfilled, so discount 0. ELSE 0 END as discount from client c

As a result, we get the following table:

As you can see, two customers received an 8 percent discount and one customer received a 2 percent discount. At the same time, 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).

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 reside in the state of "California" and whose last_name is "Smith" as well as all employees whose employee_id is equal to 82.

The parentheses 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.

Implicit type conversions can be performed in SQL implementations. So, for example, in T-SQL when comparing or combining values ​​of types smallint and int, data like smallint implicitly convertible to type int... More details about explicit and implicit type conversion in MS SQL Server can be found in BOL.

Example. Display the average price of notebooks with a prefix "average price =".
Attempting to execute a request

as a result, we get what was required:

will give the result 1926. In principle, everything is correct, since as a result we got what we asked for - YEAR. However, the arithmetic mean will be approximately 1926.2381. It should be noted here that aggregate functions (with the exception of the function COUNT which always returns an integer) inherit the data type of the values ​​being processed. Since the launched field is an integer, we got the average with the fractional part discarded (note - not rounded).
And if we are interested in the result with a given precision, say, up to two decimal places? Applying an Expression CAST to the average will not give anything for the above reason. Really,
The result is 1926.238095. Again, not that. The reason is that an implicit type conversion was performed when calculating the mean. Let's take one more step:

Those. we used an implicit conversion of an integer argument to an exact numeric type (EXACT NUMERIC) by multiplying it by a real unit, after which we applied an explicit type conversion of the result of the aggregate function.

Similar type conversions can be performed using the function CONVERT:

Here we are converting the string representation of the date to type datetime and then perform the reverse conversion to demonstrate the formatting result. Because style is not specified, the default value (0 or 100) is used. As a result, we get

List of all possible argument values style can be viewed in BOL.

CASE statement

Suppose you want to display a list of all PC models with their prices. In this case, if the model is not available for sale (not in the PC table), then instead of the price display the text: "Not available".
A list of all PC models with prices can be obtained by request:

SELECT DISTINCT product.model, price FROM product LEFT JOIN pc c ON product.model = c.model WHERE product.type = "pc";

In the result set, the missing price will be replaced with a NULL value:

model	price
1121	850
1232	350
1232	400
1232	600
1233	600
1233	950
1233	980
1260	350
2111	NULL
2112	NULL

To replace NULL values the desired text, you can use the operator CASE:
Operator CASE depending on the specified conditions, returns one of the many possible values. In our example, the condition is to check for NULL. If this condition is met, then the text "Out of stock" is returned, otherwise ( ELSE) the price value is returned. There is one fundamental point here. Since the result of the operator SELECT is always a table, then all values ​​of any column must have the same data type (taking into account the implicit type conversion). Therefore, we cannot output a symbolic constant along with the price (numeric type). This is why a type conversion is applied to the price field to cast its values ​​to symbolic representations. As a result, we get

model	price
1121	850
1232	350
1232	400
1232	600
1233	600
1233	950
1233	980
1260	350
2111	Not available
2112	Not available

Operator CASE can be used in one of two syntactic notation forms:

1st form
CASE<проверяемое выражение>
WHEN<сравниваемое выражение 1>
THEN<возвращаемое значение 1>
…
WHEN<сравниваемое выражение N>
THEN<возвращаемое значение N>
END

2nd form
CASE
WHEN<предикат 1>
THEN<возвращаемое значение 1>
…
WHEN<предикат N>
THEN<возвращаемое значение N>
END

All offers WHEN must have the same syntactic form, i.e. you cannot mix the first and second forms. When using the first syntactic form, the condition WHEN satisfied as soon as the value the expression being tested will become equal the value of the expression specified in the sentence WHEN... When using the second syntactic form, the condition WHEN satisfied as soon as predicate takes on the value TRUE. If the condition is satisfied, the operator CASE returns the value specified in the corresponding clause THEN... If none of the conditions WHEN failed, then the value specified in the sentence will be used ELSE... With absence ELSE, a NULL value will be returned. If several conditions are met, then the value of the offer will be returned THEN the first one.
In the above example, the second form of the operator was used CASE.
Note that for testing for NULL, the standard proposes a shorter form of the operator - COALESCE... This operator has an arbitrary number of parameters and returns the first non-NULL value. For two parameters, the COALESCE (A, B) statement is equivalent to the following statement CASE:

CASE WHEN A IS NOT NULL THEN A ELSE B END

The solution to the above example when using the operator COALESCE can be rewritten as follows:

Using the first syntactic form of a statement CASE can be demonstrated in the following example : Display all available PC models with price. Mark the most expensive and cheapest models.

SELECT DISTINCT model, price, CASE price WHEN (SELECT MAX (price) FROM pc) THEN "Most expensive" WHEN (SELECT MIN (price) FROM pc) THEN "Cheapest" ELSE "Average price" END comment FROM pc ORDER BY price;

As a result of executing the request, we get

model	price	comment
1232	350	The cheapest
1260	350	The cheapest
1232	400	average price
1233	400	average price
1233	600	average price
1121	850	average price
1233	950	average price
1233	980	Dearest

One of the most common programming languages ​​for working with databases is SQL. Language constructions allow not only creating a database, but also performing various manipulations with it to change data or select it.

To select data from the database, use the Select [dataset] from [table name] construct. Experience shows that in 80% of the use of queries for data selection, you need to apply a variety of conditions - parameters. For this, the SQL-Where condition is introduced into the language, as an addition to the query, its complication.

Ways of applying the Where clause

Quite often, a programmer needs to select, mainly to provide a report, the data stored in the database. It may not be enough to build a simple fetch query for this. As a rule, it is necessary to take into account various conditions, sampling parameters, of which there can be quite a lot, or to check. whether the data is in a delineated range or is in a specific table.

The SQL-Where construct can be used to specify conditions for fetching data, or to check if data is included in a selection or foreign table.

Using Where to Specify Fetch Parameters

If you need to specify specific parameters for selection from the 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 construction.

Determine with the help of the Join key construct from which tables the selection will be made.

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

Queries of this kind are quite simple to construct and do not cause difficulties even for beginners.

Using Construct to Check Occurrence

If the programmer is tasked with not only selecting data from a table by condition, but also checking their entry into one or more tables of a different plan, the SQL-Where construction will be indispensable.

Using the syntax of this construct, you can build the same conditions, but with nested queries that will check the occurrence of the selected rows in a set of foreign database tables.

As a rule, for such purposes, a temporary table is formed, in which the entire data set necessary for checking the entry is recorded.

Examples of using Where

Now examples will 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 product being sold, the price, the date of sale, and the customer who purchased the product. The second indicates the availability of the goods, or rather, what is in stock.

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

Select * from Tovar

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

Such a query 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 of the following plan:

Select * from Tovar

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

There can be many nested conditions in Where, but it's worth mentioning that the more conditions are imposed, the longer the query will take. This is what caused the use of temporary tables. It is much faster to generate several of these, and then compare the data in them, than to build a condition with several levels of data validation.