The development of any database involves not only the creation and filling of tables with a variety of information, but also further work with the data. To correctly perform a variety of tasks for selecting data from tables and generating reports, the standard Select design is used.
Fetching 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 that are generated, for example, in online stores or large companies, the data selection will not be limited to just one table. As a rule, the samples can be from a fairly large number of not only related tables, but also sub-queries / sub-queries, which are made by the programmer himself, depending on the task assigned to him. To select from one table, you can use the simplest construction:
where Person is the name of the table from which data is to be fetched.
If there is a need to select data from several tables, you can use one of the standard designs to join several tables.
Ways to connect additional tables
If we consider the use of such constructions at the initial level, then we can distinguish the following mechanisms for connecting the required number of tables for sampling, namely:
- Inner Join statement.
- Left Join or, this is the second way to record, Left Outer Join.
- Cross join
- Full join
The use of table join operators in practice can be learned by considering the use of the SQL operator - 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 be used not only to join two or more tables, but also to connect other subqueries, which greatly simplifies the work of database administrators and, as a rule, can significantly speed up the execution of certain queries that are complex in structure.
Joining data in tables row by row
If you consider connecting a large number of subqueries and collecting data into a single table row by row, you can also use the Union and Union All operators.
The use of these structures will depend on the task set for the developer and the result that he wants to achieve in the end.
Description of the Inner Join statement
In most cases, the Inner Join statement is used to join multiple tables in SQL. The SQL Inner Join description is pretty straightforward for an 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 based on the possibility of intersection and selection of only the data that is in each of the tables included in the query.
If we consider this work from the point of view of graphical interpretation, we get the structure of the SQL Inner Join statement, an example of which can be shown using the following scheme:
For example, we have two tables, a diagram of which is shown in the figure. They, in turn, have a different number of entries. In each of the tables there are fields that are interconnected. If you try to explain the operator’s work 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 coincide. Simply put, a query will return only those records (from table number two) that are listed in table number one.
Inner Join Operator Syntax
As mentioned earlier, the Inner Join operator, namely its syntax, is unusually simple. To organize relationships between tables within the same sample, it will be enough to remember and use the following basic diagram of the construction of the operator, which is written in one line of program SQL-code, namely:
- Inner Join [Table name] on [key field from the table to which we connect] = [Key field of the connected table].
For communication, this operator uses the main keys of the tables. As a rule, in a 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.
Example and description of connecting to a single table selection
We have a Person table where information about all employees working in the company is stored. 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 store information about the units in which employees work. It, in turn, is connected using the Su_Person field to the Person table. What is this talking about? Based on the data scheme, we can say that in the unit table for each record from the "Employees" table there will be information about the department in which they work. It is for this reason that the Inner Join operator 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 one table, then everything is pretty simple:
Select * from Person Inner join Subdivision on Su_Person = Pe_ID |
An example of connecting two tables and a subquery
The SQL Inner Join statement, the use of which for fetching data from several tables can be organized as described above, works on a slightly more complicated principle. For two tables, we complicate the task. Let's say we have a Depart table that stores information about all departments in each of the departments. In this table, the unit number and employee number are recorded and you need 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 department table to the selection. In this case, you can organize a 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 department table, but only the necessary. This, unlike the first method, will reduce the query run 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 design can not always speed up the query. Sometimes there are cases when you have to use an additional selection of data 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
The construction of complex queries implies the use of a significant number of tables and subqueries related to each other for data sampling. The SQL Inner Join syntax can satisfy these requirements. Examples of using the operator in this case can be complicated not only by samples 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). An example - 3 tables - in this case will have a rather complicated structure.
In this case, three more are connected (to the main table) and several data selection conditions have been introduced.
When using the Inner Join operator, it is worth remembering that the more complex the request, the longer it will take to implement, so you should look for ways to more quickly complete and solve the task.
Conclusion
In the end, I would like to say one thing: working with databases is not the most difficult thing in programming, so if you want, absolutely everyone will be able to master the knowledge of building databases, and over time, gaining experience will work with them on a professional level .