The development of any database does not implyonly the creation and filling of tables with a variety of information, but also further work with the data. For the correct execution of various tasks for selecting data from tables and generating reports, the standard Select construct is used.
If we consider the problem of selecting data orthe construction of a certain report, you can determine the level of complexity of this operation. As a rule, when working with serious (on the volume of information) databases, which are formed, for example, in online stores or large companies, the sampling of data will not be limited to only one table. Typically, the samples can be from a fairly large number of not only interconnected tables, but also the nested queries / subqueries that the programmer itself makes, depending on the task assigned to it. For sampling from one table, you can use the simplest design:
| Select * from Person |
where Person is the name of the table from which to select the data.
If there is a need to select data from several tables, you can use one of the standard designs to combine several tables.
If we consider the use of such structures at the initial level, then we can distinguish the following mechanisms for connecting the necessary number of tables for the sample, namely:
The use of join operators tables in practice can be learned by considering the use of the operator SQL - Inner Join. An example of its use will look like this:
Select * from Person Inner join Subdivision on Su_Person = Pe_ID |
The SQL language and the Join Inner Join operator can beUse not only to merge two or more tables, but also to connect other subqueries, which greatly facilitates the work of database administrators and, as a rule, can significantly speed up the execution of certain structurally complex queries.
If you consider connecting a large number of subqueries and assembling data into a single table row by row, you can also use the Union and Union All operators.
The application of these designs will depend on the task assigned to the developer and the result that he wants to achieve in the end.
In most cases, to merge severaltables in SQL use the operator Inner Join. The description of Inner Join in SQL is quite simple for an average programmer to understand, which is just beginning to understand the databases. If we consider the description of the mechanism of operation of this construction, we get the following picture. The logic of the operator as a whole is based on the possibility of intersecting and sampling only those data that exist in each of the tables entering into the query.
If we consider this work from the point of view of graphical interpretation, we get the structure of SQL Inner Join, an example of which can be shown with the help of the following scheme:
For example, we have two tables, the scheme of whichis shown in the figure. They, in turn, have a different number of records. In each of the tables there are fields that are linked together. If you try to explain the work of the operator based on the figure, the returned result will be in the form of a set of records from two tables, where the numbers of related fields coincide. Simply put, the query will return only those records (from table number two), data about which is in the table number one.
As mentioned earlier, the operator Inner Join, andit is his syntax that is unusually simple. To organize links between tables within one sample, it will be enough to remember and use the following principal scheme for constructing an operator, which is written into one line of the program SQL-code, namely:
For communication in this operator, the maintable keys. As a rule, in the group of tables that store information about employees, previously described Person and Subdivision have at least one similar record. So, let's take a closer look at the SQL Inner Join statement, an example of which was shown a little earlier.
We have a Person table where we storeinformation about all employees working in the company. Just note that the main key of this table is the field - Pe_ID. Just on it and there will be a bunch.
The second Subdivision table will storeInformation on the units in which employees work. It, in turn, is linked with the help of the Su_Person field with the Person table. What does it say? Based on the data schema, you can say that the department table for each entry in the Employees table will contain information about the department in which they work. It is for this connection that the operator Inner Join will work.
For a more understandable use, consider the SQL Inner Join statement (examples of its use for one and two tables). If we consider an example for a single table, then everything is quite simple:
Select * from Person Inner join Subdivision on Su_Person = Pe_ID |
SQL Inner Join operator, usage exampleswhich for the selection of data from several tables can be organized as above, works on a slightly more complicated principle. For two tables, we complicate the problem. For example, we have the Depart table, which stores information about all departments in each department. In this table, the department number and employee number are recorded, and the data sample should be supplemented with the name of each department. Looking ahead, it is worthwhile to say that two methods can be used to solve this problem.
The first way is to connect the department table to the sample. In this case, you can organize the request in this way:
Select Pe_ID, Pe_Name, Su_Id, Su_Name, Dep_ID, Dep_Name from Person Inner join Subdivision on Su_Person = Pe_ID Inner join Depart on Su_Depart = Dep_ID and Pe_Depart = Dep_ID |
The second method of solving the problem is to usesubquery, in which not all data but only necessary data will be selected from the department table. This, in contrast to the first method, will reduce the query time.
Select Pe_ID, Pe_Name, Su_Id, Su_Name, Dep_ID, Dep_Name from Person Inner join Subdivision on Su_Person = Pe_ID Inner join (Select Dep_ID, Dep_Name, Pe_Depart from Depart) as T on Su_Depart = Dep_ID and Pe_Depart = Dep_ID |
It should be noted that this design is not alwayscan speed up the query. Sometimes there are cases when it is necessary to use additional sampling of data in the temporary table (if their volume is too large), and then it is combined with the main sample.
Building complex queries impliesuse to select data a significant number of tables and subqueries related to each other. These requirements can satisfy the SQL Inner Join syntax. Examples of the use of the operator in this case can be complicated not only by samples from many places of data storage, but also from a large number of nested subqueries. For a specific example, you can take a sample of data from system tables (the Inner Join SQL operator). An example - 3 tables - in this case will have a rather complex structure.
In this case, three more are added (to the main table) and several data selection conditions are entered.
When using the operator Inner Join isremember that the more complex the query, the longer it will be implemented, so it is worth looking for ways to more quickly execute and solve the task.
In the end I would like to say one thing: working with databases is not the most difficult thing that is in programming, so if you wish absolutely everyone can master the knowledge of building databases, and eventually, gaining experience, you will be able to work with them on a professional level.
</ p>
