SQL INNER JOIN statement: examples, syntax and features

The development of any database implies not only 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.

Data fetches from tables

If you consider the task of selecting data or constructing 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:

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.

Ways to connect additional 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:

1. Operator Inner Join.
2. Left Join or, this is the second way of recording, Left Outer Join.
3. Cross Join.
4. Full Join.

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:

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 structured queries.

Combining data in tables row by row

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 set before the developer and the result that he wants to achieve in the end.

Description of operator Inner Join

In most cases, you use the Inner Join operator to join multiple tables in SQL. 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 that are included in 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 diagram of which is 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), the data of which is in table number one.

Syntax of Inner Join operator

As mentioned earlier, the Inner Join operator, namely its syntax, is extremely simple. To organize links between tables within a single sample, it will be enough to remember and use the following principal scheme 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 connect] = [Key field of the connected table].

For communication in this operator, the main keys of the tables are used. 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.

Example and description of connection to a single table selection

We have a Person table that stores information about all the 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 table Subdivision will store information about the departments in which employees work. It, in turn, is associated with the help of the Su_Person field with the Person table. What does this mean? Based on the data schema, you can say that in the unit table for each entry in the Employees table 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:

An example of connecting two tables and a subquery

The SQL Inner Join operator, which can be used to select data from several tables in the above manner, works on a slightly more complicated principle. For two tables, we complicate the problem. For example, we have a Depart table, which stores information about all departments in each department. In this table, the department number and employee number are recorded and you need to supplement the data sample with the name of each department. Looking ahead, it is worth mentioning 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 query in this way:

The second method of solving the problem is to use a subquery in which not all the data, but only the necessary ones, will be selected from the department table. This, in contrast to the first method, will reduce the query time.

It is worth noting that this design may not always 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.

Example of using the Inner Join operator for selections from a large number of tables

Building complex queries involves using a significant number of tables and subqueries related to each other to retrieve data. 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 data storage locations, 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 conditions for data selection are entered.

When using the operator Inner Join, remember that the more complex the query, the longer it will be implemented, so it is worth looking for ways to more quickly perform and solve the task.

Conclusion

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 .