MySQL JOIN: a description, an example of using the command and recommendations

Development of databases of Internet resources practically does not differ from standard databases developed in MS SQL SERVER. Typically, for such resources, the MY SQL language is used, although it can also be used to develop standard software products for local use. But this article is not about this.

Often when working with databases in each language, the task is to make a selection of data for output to a variety of reports, graphs and so on. As a rule, when implementing such tasks, it is necessary to use not one, but several tables, combining them into one query, considerably complicating its design. At the same time, it is necessary to take into account how data will be output, how the tables will be "pulled up" and what result will be the most acceptable for the programmer. To solve such problems, one of the standard constructs of the MySQL language is Join.

The concept of the word Join

Database development languages, no matter what language it is, standard words from English-speaking dictionaries are used as a basis (that's why, provided that you know English, it will be much easier for you to work with tables). To implement the connection of tables in the sample, the same word is used - Join. In the database programming language My SQL is used. The translation of this official word is exactly the same as in the language itself - "unification".

Interpreting the MySQL-Join construct, and any of them, will be exactly the same. If we decipher the design purpose, namely the scheme of its operation, we get the following value: the constructions will allow to collect the necessary fields from different tables or nested queries into one sample.

Types of constructions for the association

If the programmer needs to collect a sample from several tables and he knows which key fields in them and which data are needed for the report, then one of the basic constructions of the union can be used to achieve the desired result. The main constructions (for merging tables) are four:

1. Inner join.
2. Cross Join.
3. Left join.
4. Right Join.

Depending on the task, each of the standard designs will give different results, which will allow you to receive reports on different parameters in a short time.

Creating and populating tables for later use

Before you start, for example, consider the mechanisms for working with data federations, it is worthwhile to prepare several tables, with which we will continue to work. This will help visually show all the principles of the operators, in addition, thus, beginners are easier to learn all the basics of programming tables.

The first table will describe some of the items that a person constantly meets throughout his life.

In the second table we describe some properties of 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 by example. Now you can begin to consider our designs.

Using Inner Join

When using the MySQL-Join Ineer construct, it's worth taking into account some of its features. This design allows you to select from both tables only those records that are both in the first and second table. How it works? In the first table, we have the master key - ID, which indicates the serial number of records 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 you select data, the Select operator will only determine the records whose sequence numbers are the same, which means they are in the first and second table.

When using the design, you need to understand what data you need to get. The most common mistake, especially for a novice database programmer , is the irrational and incorrect use of the Inner Join design. As an example, MySQL Inner Join, you can consider a script that will return to us from previously described and filled tables information about objects and their properties. But there can be several ways to use the structure. In this respect My SQL is a very flexible language. So, you can consider examples of using MySQL Inner Join.

Combine tables without specifying any parameters. In this case, we get the result of such a plan:

If we indicate using the Utility word "Using", that it is necessary to take into account the main keys of the records in the tables, the result of the selection will be radically changed. In this case, we get a sample that will return only those rows that have the same main keys.

It is also possible to use the third variant of using the structure, when in the query the fields on which the tables are to be joined should be indicated via the word "on". In this case, the sample will return the following data:

Features of the use of Left Join

If we consider another way of combining tables using the MySQL-Join construct, you can see the difference in the data that is output. Such a mechanism is the Left construct.

Using the Left Join construct MySQL has some features and, like Inner, requires a clear understanding of the result that needs to be obtained.

In this case, all records from the first table will be selected first, and subsequently records from the second property table will be attached to them. In this case, if there is an entry in the first table, for example, a "stool", and in the second table there are no properties for it, then the Left statement will display null in front of this record, which tells the programmer that there are no signs for this kind of object .

Using this design will allow you to determine which fields or, for example, goods in the store are not exposed to the price, warranty period and so on.

Left example

To examine the Left Join MySQL construct operator in practice, we use the previously described tables. Say, you need to select the entire list of products that are in the store, and check which of them are not marked with signs or properties. In this case, the sample will display all the products on the screen, and for those that do not have a property, empty values will be displayed.

Using Where in the Join construct

As a parameter, a join can include not only the fields that you want to connect tables to, but can also include the Where clause operator.

For example, consider a script that should return to us only those records that do not have a feature. In this case, in the Join construct, you must add a condition statement and specify what exactly you want to return as a result.

When used in 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 requests allow you to make selections on specific data that relate to the condition chosen by the programmer. There are several such conditions, but at the same time specifying parameters of data selection from the combined tables.

Using Join to change data in tables

The Join construct is, in fact, universal. It allows not only to make a variety of samples, but also to include in the requests from one to several tables, enter additional conditions into the sample. The design can also be used for other data operations. So, Join can be used to change data in a table. Rather, to clarify the conditions in the table or in those cases, if you want to update the data in several tables for the same conditions.

For example, consider this problem. Three tables are given in which there are some data. You need to make a change to the data in both tables using a single query. Just to solve this kind of tasks, you can use the Join construct in the Update command. The type of the Join construct itself depends, as in the case of data fetching, on the result the programmer wants to get.

Let's consider the simplest example. You need to update the same query for the same conditions. Such queries are built to optimize the work with the database. Why write different queries for each of the tables, if you can perform all the manipulations of data with one query? The MySQL Update Join example in this case will be:

Building complex queries

Quite often when working with a database it is necessary to build queries not only with the merging of several tables, but also with the use of subqueries. Such tasks are rather difficult to understand for a beginner database programmer. The difficulty is that you have to think through each step, determine which 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. The ideal option will be MySQL Left Join examples.

This request will return us 58 records of sales contracts for which the balance of funds for the selected date is filled or exists. In this case, this is the current date. Also in the sample the condition was added that the name of the contract should contain symbols "123". Information displayed on the screen (data) will have sorting - ordering by contract number.

The following example will display the details of all payments in which the contract number is specified.

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 used mainly to speed up the query and optimize it.

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 required fields, and merge 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 which of its forms, the programmer decides, but it is worth remembering and taking into account what result should be obtained when the query is executed.