MySQL JOIN: description, example usage commands and recommendations

Development of databases of Internet resources does not differ from standard databases developed in MS SQL SERVER. Usually, for this kind of resources used language, MY SQL, although it can be applied to the development of standard software products for local use. But this article is not about that.

Often when working with databases in all languages the task is to make a sample data to derive in a variety of reports, graphics and so on. As a rule, the implementation of such tasks is necessary to use not one but several tables, combining them into one request, greatly complicating its structure. It is necessary to consider how the data will be displayed, as will "catch up” table and what the result will be most acceptable to the programmer. To solve such problems we use one of the standard design language MySQL – Join.

The concept of the word Join

Development Languages databases, no matter what kind of language, we take the standard words from the English dictionaries (which is why assuming that you know English, you bude much easier to work with tables). To implement the connection tables in the sample taken the same word - Join. In a programming language, database Used My SQL. The translation of this word is exactly the same as in the language - “Association”.

Interpretation of the structure MySQL-Join, and any of them will be exactly the same. If you decipher the purpose of the construction, namely the scheme of its work, we get the following value: design will allow you to collect the required fields from different tables or subqueries in one sample.

Recommended

How to get out of "Skype" on the "Android" and not only

How to get out of "Skype" "Android"? This question bothers many users. The thing is that the messenger is not so difficult. But there are features that are only for mobile versions. To log out of Skype in this case more difficult than it seems. But i...

Kingo ROOT: how to use the program to gain administrative rights on Android

The Gadgets on the platform Android regained the lion's share of the market. Developers are continually perfecting the OS, trying to fit the needs of the users, but from year to year admit palpable mistake: forbid the owner to "stuffing" of his gadge...

How to get from Stormwind to Tanaris: practical tips

World of Warcraft is an iconic MMORPG, uniting millions of players around the world. There are many locations and puzzles, secret trails and larger, but rather dangerous paths. Sooner or later, every player chose the side of the Alliance, we have to ...

Types of structures for enterprises

If the programmer need to collect a sample from multiple tables and he knows what the key fields in them and what data you need for your report, you can use one of the main structures of the Association, to achieve the desired result. Basic designs (for merging tables), four:

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

Depending on the task, each of the standard designs will give different results that will allow you to obtain a report on different parameters in a short time.

Creating and populating tables for future use

Before starting, for example, to consider mechanisms for construction of the enterprises data is necessary to prepare several tables that we will continue to work. This will help to demonstrate the principles to the operators, moreover, so beginners can easily learn all the basics of programming tables.

The first table describes some of the objects with which man is constantly occurs throughout his life.

The second table will describe some properties of objects from the first table to be able in the future to work with them.

In General, the two tables will be enough for example to show their work. Now you can proceed to the practical examination of our structures.

Using Inner Join

When using MySQL – Ineer Join, it is worth considering some of its features. This design will allow you to select from both tables only those records that are in the first and in the second table. How does it work? The first table we have primary key-ID, which indicates the sequence number of the records in the table.

When you create the second table, the same key is used as the ordinal number example you can see in the pictures. When selecting data the Select statement will determine the result only those records whose sequence numbers are the same - so they are in the first and in the second table.

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

Joining tables without specifying any parameters. In this case, we will get a result like:

If you specify the using function word Using that must take into account the primary keys of the records in the tables, the result set will change dramatically. In this case we get a sample that will return only those rows that have identical primary keys.

There is a third option using the design, when a request using the word “on”, you specify the fields that should be joining the tables. In this case, the sample will return following data:

Features of using Left Join

If to consider another method of combining tables using design MySQL – Join, notice the difference in the data that are displayed. Such a mechanism design is Left.

Using Left Join MySQL has some features and as Inner, requires a clear understanding of the outcome that you want to.

In this case, it first selects all records from the first table, and in the future they will incorporate the record from the second table of properties. In this case, if the first table has a record, for example, "stool", and the second table are no properties for it, then the operator will Left next to that entry to null, which tells the programmer that the signs on this kind of subject.

This construct allows you to define what fields or, for example, goods in store are not exposed to price, warranty period and so on.

Example usage Left

For consideration by the practice operator design MySQL Left Join using the previously described tables. Say, you want to select the entire list of goods available in the store, and check for which of them do not bear the characteristics or properties. In this case, the sample displays all items, and for those who have no property will be marked with empty values.

Using the Where clause in the Join design

As an argument, the Association may include not only specify the fields for which you want to connect the tables, but may include a statement of the Where clause.

For example, consider a script that must return to us only those records that are not marked with a sign. In this case, in a Join you need to add the condition statement to specify what to return as the result.

When using Join in MySQL – Where you need to clearly understand that you will only see those records, which include specified criteria, and the selection would then look like the following:

These queries allow users to make selections for specific data that relate to the selected programmer. Such conditions you can specify multiple, maximally while specifying the parameters for data selection from the joined tables.

Use joins to modify data in tables

Join, in fact, universal. It not only allows you to do a variety of the sample, and to connect to the queries one or more tables, introduce additional terms in the sample. Design can also be used for other data operations. So, Join can be used to change the data in the table. Rather, to clarify the terms in the table or in cases where need to update data in multiple tables on the same terms.

For example, consider such a task. The three tables that have some data. Need to change data in both tables using one query. For solving such kind of tasks you can use the Update command is a Join. The design type of Join depends in the same way as in the case of sampling data from the result who wants to get a programmer.

Consider a very simple example. Need to update one query data for the same conditions. This kind of queries are built to optimize the database. Why write different queries for each table, if you can spend all manipulations with the data in one request? MySQL Update Join example in our case will be:

Compound queries

Quite often, when working with a database you need to query not only with the Union of several tables and using subqueries. Such tasks are quite complex to understand for a novice programmer databases. The difficulty is that you have to think through each step, to determine which data from which table or query should be retrieved and how in the future they will need to work.

For a more concrete understanding can be considered (in MySQL Join) examples of complex queries. If you are a beginner and just starting to work with databases, the training will only benefit. The ideal option would be MySQL Left Join examples.

This query will return to us 58 records sales agreements, which are filled or there is a cash balance on selected date. In this case the current date. Also in the sample added condition that the title shall be the symbols of “123”. Displayed information (data) will be a sorting-order by contract number.

The Following example displays information about all payments, which will specify the contract number.

Using subqueries

As mentioned earlier, when working with databases it is possible to combine not only the table, but a table with a query. This design is mainly used to speed the query and its optimization.

For example, if you want from a table that has several hundred fields and, say, a thousand records to choose just two fields, you should use a query that will return only the required fields, and merge it with the main data sample. As an example, MySQL Select Join, consider the query like:

It's not all the ways to use the standard constructs of MySQL, but onlystandard. How to use a Join and in which its forms decided by the programmer, but it is necessary to consider what result should obtain when the query is executed.