The development of any database implies not only the creation and filling of tables with various information, but also further work with the data. To correctly perform various tasks of selecting data from tables and generating reports, the standard Select. construction is used.
Selecting data from tables
If we consider the task of selecting data or building some report, we can determine the level of complexity of this operation. As a rule, when working with serious (in terms of information volume) databases, which are formed, for example, in online stores or large companies, the data selection will not be limited to only one table. As a rule, selections can be made from a fairly large number of not only interconnected tables, but also nested queries / subqueries that the programmer himself makes, depending on the task assigned to him. To select from one table, you can use the simplest construction:
| Selectfrom Person |
where Person is the name of the table from which to select data.
If there is a need to select data from several tables, you can use one of the standard constructions to join several tables.
Methods for connecting additional tables
If we consider the use of such structures at the initial level, then we can distinguish the following mechanisms for connecting the required number of tables for sampling, namely:
- Inner Join operator.
- Left Join or, this is the second way of writing, Left Outer Join.
- Cross Join.
- Full Join.
The use of table join operators in practice can be learned by looking at the use of the SQL - Inner Join operator. An example of its use would look like this:
|
Selectfrom Person Inner join Subdivision on Su_Person=Pe_ID |
The SQL language and the Join Inner Join operator can be used not only to join 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 complex programs. query structure.
Combining data in tables line by line
If we consider connecting a large number of subqueries and collecting data into a single table row by row, then you can also use the Union and Union All operators.
Using these designswill depend on the task set for the developer and the result he wants to achieve in the end.
Description of the Inner Join operator
In most cases, SQL uses the Inner Join operator to join multiple tables. The description of Inner Join in SQL is quite simple to understand for the average programmer who is just starting to understand databases. If we consider the description of the mechanism of operation of this design, we get the following picture. The logic of the operator as a whole is built on the possibility of crossing and selecting only the data that is in each of the tables included in the query.
If we consider such work from the point of view of graphical interpretation, we will get the structure of the SQL Inner Join operator, an example of which can be shown using the following diagram:
For example, we have two tables whose schema is shown in the figure. They, in turn, have a different number of entries. Each of the tables has fields that are interconnected. If you try to explain the operation of the operator based on the figure, then the returned result will be in the form of a set of records from two tables, where the numbers of the related fields match. Simply put, the query will return only those records (from table number two) that have data in table number one.
Inner Join operator syntax
As mentioned earlier, the Inner Join operator, namely its syntax, is extremely simple. To organize relationships between tables within the same sample, it will be enough to remember anduse the following principle for constructing an operator, which is written in one line of the program SQL code, namely:
Inner Join [Table name] on [key field from the table to which we are connecting]=[Key field of the table to be connected to]
This statement uses the master keys of the tables to link. As a rule, in the group of tables that store information about employees, the previously described Person and Subdivision have at least one similar record. So, let's take a closer look at the SQL Inner Join operator, an example of which was shown a little earlier.
Example and description of connecting to a single table selection
We have a Person table that stores information about all employees working in the company. Immediately, we note that the main key of this table is the field - Pe_ID. Just on it and the bunch will go.
The second Subdivision table will store information about the departments where employees work. She, in turn, is linked via the Su_Person field to the Person table. What does it say? Based on the data schema, we can say that in the table of departments for each record from the table "Employees" there will be information about the department in which they work. It is for this connection that the Inner Join operator will work.
For a more understandable use, consider the SQL Inner Join operator (examples of its use for one and two tables). If we consider an example for one table, then everything is quite simple:
|
Selectfrom Person Inner join Subdivision on Su_Person=Pe_ID |
Example of connecting two tables and a subquery
The SQL Inner Join operator, examples of which can be organized in the above way to select data from several tables, works according to a slightly more complicated principle. For two tables, let's complicate the task. Let's say we have a Depart table that stores information about all the departments in each of the divisions. In this table, the number of the department and the number of the employee are recorded, and it is necessary to supplement the data selection with the name of each department. Looking ahead, it is worth saying that two methods can be used to solve this problem.
The first way is to connect the departments table to the selection. 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 use a subquery, in which not all data will be selected from the departments table, but only the necessary ones. This, unlike the first method, will reduce the query execution 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 is worth noting that such a construction may not always speed up the query. Sometimes there are cases when you have to use an additional data selection in a temporary table (if their volume is too large), and then combine it with the main selection.
An example of using the Inner Join operator to select from a large number of tables
Building complex queries involves using a significant number of interconnected tables and subqueries to retrieve data. The SQL Inner Join syntax can satisfy these requirements. Examples of using the operator in this case can be complicated not only by selections from many data storage locations, but also from a large number of nested subqueries. For a specific example, you can take a selection of data from system tables (Inner Join SQL statement). Example - 3 tables - in this case it will have a rather complex structure.
In this case, three more are connected (to the main table) and several data selection conditions are entered.
When using the Inner Join operator, remember that the more complex the query, the longer it will take to complete, so you should look for ways to complete and solve the task faster.
Conclusion
In the end, I would like to say one thing: working with databases is not the most difficult thing in programming, therefore, if desired, absolutely everyone can master the knowledge of building databasesdata, and over time, having gained experience, it will be possible to work with them at a professional level.
