SQL Where: application methods and examples

One of the most common programming languages for working with databases is SQL. Language constructs allow not only to create a database, but also to conduct various manipulations with it on changing data or their sample.

To select data from the database, the Select [data set] from [table name] construct is used. Experience shows that in 80% of cases when using data requests, a variety of conditions must be applied - parameters. To do this, the SQL-Where clause is introduced into the language, in addition to the query, its complication.

Methods of applying the Where clause

Quite often the programmer needs to select, mainly for reporting, the data stored in the database. For this, it may not be sufficient to construct a simple query for a sample. As a rule, it is also necessary to take into account a variety of conditions, sampling parameters that can be quite numerous, or checked. Whether the data is in the outlined range or in a certain table.

The SQL-Where clause can be used to specify data sampling conditions or to test whether data is included in a sample or a third-party table.

Using Where to Specify Selection Parameters

If you need to specify certain parameters to select 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:

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

2. Identify using the key Join construct, from which tables the selection will be made.

3. Use the Where clause to specify a list of parameters for the sample.

Such requests are fairly simple to build and do not cause difficulties even for beginners.

Use of a construction to check for occurrences

If the programmer is tasked with not only selecting from the table the condition data, but also verifying that they are included 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 the occurrence of the selected rows in a set of third-party database tables.

Typically, for such purposes, a temporary table is formed in which all the data set required for the entry check is written.

Examples of Where

Now you will see examples of Where SQL. For starters, imagine that there are two tables with data - Tovar and TovarAmount. In the first there are names of the sold goods, the price, the date of sale and the customer who purchased the goods. The second indicates the availability of the goods, or more precisely, what is available.

An example of a query with a parameter that indicates all products sold for a certain number of days will be a design of the following character:

Select * from Tovar

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

Such a plan request will return a list of goods, data from the table that were sold during the first seven days of December, as indicated by one of the conditions of the sample: T_PriceOut> 0.

If we consider the condition for the withdrawal of goods that are available, then the design will be such a 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 is worth mentioning that the more conditions are imposed, the longer the query will work. This is what caused the use of temporary tables. It is much faster to generate several such ones, and then compare the data in them than to build a condition with several levels of data validation.