One of the most common programming languages ββfor working with databases is SQL. The language constructs allow not only to create a database, but also to carry out various manipulations with it to modify data or select it.
To select data from the database, use the Select [data set] from [table name] construct. As experience shows, in 80% of cases of using queries for data sampling, various conditions must be applied - parameters. To do this, the language, as an addition to the query, its complication, introduced the SQL-Where condition.
Ways to apply the Where clause
Quite often, a programmer needs to select, mainly for reporting, the data stored in the database. To do this, it may not be enough to build a simple query on the sample. As a rule, it is still necessary to take into account various conditions, sampling parameters, which can be quite a lot, or check. whether the data are in the outlined range or are in a specific table.
The SQL-Where construct can be used to specify the conditions for selecting data or to verify that data is in a selection or a foreign table.
Using Where to Set Selection Parameters
If you need to set certain 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 construct.
Determine with the help of the key Join construct from which tables the choice will be made.
Using the Where construct, specify a list of parameters for the selection.
Such requests are quite simple to build and do not cause difficulties even for beginners.
Using a construct to verify entry
If a programmer has been given the task not only to select data from a table by condition, but also to check their occurrence in one or several 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.
As a rule, for such purposes, a temporary table is formed in which all the data set necessary for checking the entry is recorded.
Where examples
We will now give examples of Where SQL. For starters, imagine that there are two tables with data - Tovar and TovarAmount. The first contains the names of the goods sold, price, date of sale and the client who purchased the goods. The second indicates the availability of goods, or rather, which is available.
An example of a query with a parameter that indicates all the goods sold in a certain number of days is a structure 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 plan will return the list of goods and 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 available, then the design will be of 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 of these, and then compare the data in them, than to build a condition with several levels of data verification.