SQL Where: application methods and examples
One of the most common languagesprogramming for working with databases is considered 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 construction is usedSelect [data set] from [table name]. 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 as an addition to the query, its complication.
Ways to apply the Where clause
Quite often a programmer needs to be selected,mainly to provide a report, 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 to check. whether the data is in the outlined range or in a specific table.
The SQL-Where construct 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:
You need to build a standard query using the Select * from construct.
Define using the key Join construct, from which tables the selection will be made.
Use the Where clause to specify a list of parameters for the selection.
Such requests are fairly simple to build and do not cause difficulties even for beginners.
Using a construction to check for occurrences
If the programmer is tasked with not only selecting from the table the condition data, but also verifying their entry into 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.
As a rule, for such purposes, a temporary table is formed in which all the data set required to verify the entry is recorded.
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 the goods 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, the design will be such a plan:
Select * from Tovar
Where T_Tovar in (select TA_Tovar where TA_Amount> 0)
There can be a lot of nested conditions in Where, butit 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.</ p>>