Database development of Internet resources is practically no different from standard databases developed in the MS SQL SERVER system. As a rule, MY SQL is used for such resources , although it can also be applied to the development of standard software products for local use. But the article will not discuss this.
Often when working with databases in each of the languages, the task is to select data for display in a variety of reports, graphs, and so on. As a rule, when implementing this kind of tasks, you have to use not one, but several tables, combining them into one query, significantly complicating its design. In this case, it is necessary to take into account how the data should be displayed, how the tables will be “pulled up” and what result will be most acceptable for the programmer. To solve such problems, one of the standard construction of the MySQL language is used - Join.
Concept of word Join
Database development languages, no matter what kind of language it is, take standard words from English-language dictionaries as the basis (for this reason, provided that you know English, it will be much easier for you to work with tables). To implement the connection of tables, the same word is taken in the sample - Join. The database programming language uses My SQL. The translation of this official word is exactly the same as in the language itself - “association”.
The interpretation of the MySQL - Join construct, any of which will be exactly the same. If we decipher the purpose of the structure, namely, the scheme of its operation, we get the following value: the structures will allow us to collect the necessary fields from different tables or nested queries into one sample.
Types of structures to combine
If a programmer needs to collect a selection of several tables and he knows which key fields are in them and what data is needed for the report, then you can use one of the main union structures to achieve the desired result. There are four main constructions (for joining tables):
- Inner join.
- Cross join
- Left join.
- Right Join.
Depending on the task, each of the standard designs will give different results, which will allow you to receive reports on various parameters in a short time.
Create and populate tables for future reference
Before you begin, for example, to consider mechanisms for working with data federation constructs, it’s worth preparing several tables that we will work with in the future. This will help to clearly demonstrate all the principles of the operators, in addition, this way newcomers are easier to learn all the basics of programming tables.
The first table will describe some objects that a person constantly encounters throughout his life.
In the second table, we describe some properties of the objects from the first table so that you can work with them in the future.
In general, two tables will be enough to show their work using an example. Now you can begin to practical examination of our designs.
Using Inner Join
When using the MySQL - Join Ineer construct, it is worth considering some of its features. This design allows you to select from both tables only those records that are in the first and second table. How it works? In the first table, we have the main key - ID, which indicates the serial number of entries in the table.
When creating the second table, the same key is used as a serial number, an example can be seen in the figures. When selecting data, the Select operator will determine as a result only those records whose sequence numbers coincide - which means that they are in the first and second tables.
When using the design, you need to understand what kind of data you need to get. The most common mistake, especially for a novice database programmer , is to misuse and misuse the Inner Join construct. As an example, MySQL Inner Join, you can consider a script that will return information about objects and their properties from previously described and filled tables. But there can be several ways to use the design. In this regard, My SQL is a very flexible language. So, you can consider examples of using MySQL Inner Join.
Joining tables without specifying any parameters. In this case, we get the result of such a plan:
If we indicate through the service word Using that it is necessary to take into account the main keys of the records in the tables, then the selection result will change dramatically. In this case, we get a selection that returns only those rows that have the same master keys.
A third variant of using the construction is also possible, when fields through which tables should be joined are indicated in the query through the word “on”. In this case, the selection will return the following data:
Features of using Left Join
If you consider another way to join tables using the MySQL construct - Join, you can notice the difference in the data that is displayed. Such a mechanism is the Left construction.
Using the Left Join construction MySQL has some features and, like Inner, requires a clear understanding of the result you need to get.
In this case, all records from the first table will be selected first, and later records from the second property table will be attached to them. Moreover, if there is a record in the first table, for example, “stool”, and there are no properties for it in the second table, the Left operator will display null opposite this record, which tells the programmer that there are no signs for this type of object .
Using this design will allow you to determine by which fields or, for example, the goods in the store are not priced, the warranty period, and so on.
Left example
For practical consideration of the construction operator of the Join Left MySQL construct, we use the previously described tables. Let's say you need to select the entire list of products that are in the store, and check for which of them are not affixed signs or properties. In this case, the selection will display all the products, and those that do not have a property will have empty values.
Using Where in Join Join
As a parameter, a join can include not only the indication of the fields by which tables need to be connected, but can also include the Where clause operator.
For example, consider a script that should return only those records for which there is no sign. In this case, you need to add a condition operator to the Join construct and specify what exactly needs to be returned as a result.
When using MySQL Join - Where, you need to clearly understand that only those records to which the specified condition applies will be shown, and the selection will then look like this:
Such queries allow you to make selections for specific data that relate to a condition selected by the programmer. Several conditions can be specified, while maximizing the accuracy of the data selection parameters from the combined tables.
Using Join to Modify Data in Tables
The Join construct is essentially universal. It allows not only to make various selections, but also to connect from one to several tables in queries, to introduce additional conditions into the selection. The construct can also be used for other data operations. So, Join can be used to modify data in a table. Rather, to clarify the conditions in the table or in those cases if you need to update the data in several tables according to the same conditions.
For example, consider this problem. Three tables are given in which some data are available. You need to modify the data in both tables using a single query. Just to solve such tasks, you can use the Join construct in the Update command. The type of Join construct itself, in the same way as in the case of data sampling, depends on the result that the programmer wants to get.
Consider the simplest example. It is necessary to update the data on the same conditions with a single request. Such queries are built to optimize the work with the database. Why write different queries for each of the tables, if you can carry out all the manipulations with the data in one query? An example of MySQL Update Join in our case will be like this:
Building complex queries
Quite often, when working with a database, it is necessary to build queries not only with the union of several tables, but also using subqueries. Such tasks are quite difficult for an aspiring database programmer to understand. The difficulty lies in the fact that you have to think through each step, determine what data from which table or query you need to get and how you will need to work with them in the future.
For a more specific understanding, you can consider (in MySQL Join) examples of complex queries. If you are a beginner and just starting to work with databases, then such training will only benefit. Ideal would be MySQL Left Join examples.
This request will return to us 58 records of sales contracts for which a cash balance is filled or exists at the selected date. In this case, this is the current date. Also, a condition has been added to the selection that the name of the contract must contain the characters “123”. The information (data) displayed on the screen will be sorted - ordered by contract number.
The following example will display data on all payments in which the contract number will be indicated.
Using subqueries
As mentioned earlier, when working with databases, you can combine not only tables, but also a table with a query. This design is mainly used to speed up the query and its optimization.
For example, if you need to select only two fields from a table that has several hundred fields and, say, a thousand records, then you should use a query that will return only the necessary fields and combine it with the main data sample. As an example of MySQL Join Select, you can consider a query of this type:
These are not all ways to use standard MySQL constructs, but only standard ones. How to use the Join construct and in what its forms is decided by the programmer himself, but it is worth remembering and considering what result should be obtained when executing the request.