A summary of Join query operation methods in several Mysql _mysql

Objective

Now the various business of the system is so complex, the data are all kinds of tables in the database, the primary key ah, that foreign key ah, and the table and table rely on these primary keys and foreign keys linked together. When we do business, we need to build relationships between multiple tables, using SQL statements, and then do various SQL operations. So how do you use SQL statements to associate multiple tables together and do business operations when you write a variety of operations using SQL? And this article, on this knowledge point to summarize.

A join query is a common database operation that matches in two tables (multiple tables). The MySQL database supports the following join queries:

• CROSS join (Cross join)
• INNER join (INNER join)
• OUTER join (outer join)
• Other

When performing various join operations, be sure to recall the first three steps of the SQL logical query statement that is summarized in the "SQL Logical Query Execution order" article:

• Execute from statement (Cartesian product)
• Perform on filter
• Add external row

Each join occurs only between two tables, even if the FROM clause contains more than one table. Each join operation is only followed by the first three steps of a logical query statement, each time a virtual table is created, and the virtual table is then joined to the next table in the FROM clause, repeating the steps until the table in the FROM clause is processed.
Pre-preparation

1. Create a new test database testdb;

Create DATABASE TestDB;

Create test tables table1 and table2;

   CREATE TABLE table1
   (
     customer_id VARCHAR) not null, City
     VARCHAR (a) not NULL,
     PRIMARY KEY (Customer _id)
   ) engine=innodb DEFAULT Charset=utf8;

   CREATE TABLE table2
   (
     order_id INT not NULL auto_increment,
     customer_id VARCHAR (),
     PRIMARY KEY ( order_id)
   ) Engine=innodb DEFAULT Charset=utf8;

inserting test data;

   INSERT into table1 (customer_id,city) VALUES (' 163 ', ' Hangzhou ');
   INSERT into table1 (customer_id,city) VALUES (' 9you ', ' Shanghai ');
   INSERT into table1 (customer_id,city) VALUES (' tx ', ' Hangzhou ');
   INSERT into table1 (customer_id,city) VALUES (' Baidu ', ' Hangzhou ');

   INSERT into table2 (customer_id) VALUES (' 163 ');
   INSERT into table2 (customer_id) VALUES (' 163 ');
   INSERT into table2 (customer_id) VALUES (' 9you ');
   INSERT into table2 (customer_id) VALUES (' 9you ');
   INSERT into table2 (customer_id) VALUES (' 9you ');
   INSERT into table2 (customer_id) VALUES (' TX ');

When the preparations are done, table1 and table2 should look like this:

   Mysql> select * FROM table1;
   +-------------+----------+
   | customer_id |
   +-------------+----------+
   | 163 |     Hangzhou |
   | 9you    | shanghai |
   |    Hangzhou
   | | TX     | hangzhou |
   +-------------+----------+
   4 rows in Set (0.00 sec)

   mysql> select * from table2;
   +----------+-------------+
   | order_id | customer_id
   | +----------+-------------+
   |    1 | 163     |
   |    2 | 163     |
   |    3 | 9you    |
   |    4 | 9you    |
   |    5 | 9you    |
   |    6 | TX     |
   +----------+-------------+
   7 rows in Set (0.00 sec)

Prepare to do the same, start today's summary.
CROSS join join (cross join)

The CROSS join performs a from statement (Cartesian product) operation on two tables, returning a combination of all the columns in two tables. If the left table has m rows of data and the right table has n rows of data, performing the cross join returns m*n row data. The CROSS join performs only the first of the three previous steps in the execution of SQL logical query statements.

What can CROSS join do? Since the cross join performs only Cartesian product operations and does not filter, we can, in practice, generate a lot of test data using the cross join.

For the above test data, use the following query:

SELECT * FROM table1 cross join Table2;

You will get the following results:

+-------------+----------+----------+-------------+
| customer_id | City | order_id |
customer_id | +-------------+----------+----------+-------------+
| 163 |    Hangzhou | 1 | 163 | | 9you |    Shanghai | 1 | 163 | | Baidu |    Hangzhou | 1 | 163 | | TX |    Hangzhou | 1 | 163 | | 163 |    Hangzhou | 2 | 163 | | 9you |    Shanghai | 2 | 163 | | Baidu |    Hangzhou | 2 | 163 | | TX |    Hangzhou | 2 | 163 | | 163 |    Hangzhou | 3 | 9you | | 9you |    Shanghai | 3 | 9you | | Baidu |    Hangzhou | 3 | 9you | | TX |    Hangzhou | 3 | 9you | | 163 |    Hangzhou | 4 | 9you | | 9you |    Shanghai | 4 | 9you | | Baidu |    Hangzhou | 4 | 9you | | TX |    Hangzhou | 4 | 9you | | 163 |    Hangzhou | 5 | 9you | | 9you |    Shanghai | 5 | 9you | | Baidu |    Hangzhou | 5 | 9you | | TX |    Hangzhou | 5 | 9you | | 163 |    Hangzhou | 6 | TX | | 9you    |    Shanghai | 6 | TX | | Baidu |    Hangzhou | 6 | TX | | TX |    Hangzhou | 6 |
TX |

 +-------------+----------+----------+-------------+

INNER join join (INNER join)

The INNER join is more powerful than the cross join in that the INNER join can match the data between tables based on some filtering criteria. In the first three steps of a SQL logical query statement, the INNER join performs both step one and step two; there is no third step, and no outer row is added, which is the biggest difference between the INNER join and the next outer join.

Now let's look at the query using the INNER join:

SELECT * FROM 
table1 
inner join table2 on 
table1.customer_id=table2.customer_id;

You will get the following results:

+-------------+----------+----------+-------------+
| customer_id | city |   order_id | customer_id
| +-------------+----------+----------+-------------+
| 163 |     Hangzhou    | 1 | 163     |
| 163 |     Hangzhou    | 2 | 163     |
| 9you    | SHANGHAI    | 3 | 9you    |
| 9you    | SHANGHAI    | 4 | 9you    |
| 9you    | SHANGHAI    | 5 | 9you    |
| tx     | hangzhou    | 6 | TX     |
+-------------+----------+----------+-------------+

For INNER joins, the effect of the INNER join and the cross join is the same without filtering using the on condition. When the filter condition column set in on has the same name, we can use the Using keyword to abbreviate the filter on, which simplifies the SQL statement, for example:

SELECT * FROM table1 inner join table2 using (customer_id);

When we actually write the SQL statements, we can omit the inner keyword, for example:

SELECT * FROM 
table1 
join table2 on 
table1.customer_id=table2.customer_id;

However, remember that this is still a inner JOIN.
OUTER join join (outer join)

Oh, I remember once in the interview, but also asked me this question again, then a good summary here. by outer JOIN, we can match the data between the tables according to some filter conditions. The result set of the OUTER join equals the result set of the inner join plus the outer row; that is, when you use the OUTER join, the first three steps of the SQL logical query statement are executed. For more information about how to add an external row, refer to the addition of the external row section in the SQL logical query execution order article.

The MySQL database supports the left OUTER join and the right OUTER join, and as with the inner keyword, we can omit the OUTER keyword. For outer joins, you can also use using to simplify the ON clause. So, for the following SQL statement:

SELECT * from 
table1 left 
outer join table2 on 
table1.customer_id=table2.customer_id;

We can abbreviate this:

SELECT * from 
table1 left 
join table2 
using (customer_id);

However, the other difference with inner join is that the on (or using) clause must be specified for the outer join, otherwise the MySQL database throws an exception.
NATURAL Join join (natural connection)

A NATURAL join is equivalent to a combination of a inner (OUTER) join and a using, which implicitly matches a column with the same name in two tables. Similarly, the NATURAL left join is equivalent to the combination of a left (right) join and a using. Like what:

SELECT * FROM 
table1 
join table2 
using (customer_id);

And

SELECT * from 
table1 
natural join table2;

Equivalent.

In the example:

SELECT * from 
table1 left 
join table2 
using (customer_id);

And

SELECT * from 
table1 
Natural left join table2;

Equivalent.
Straight_join joins

Straight_join is not a new join type, but a user's control of the SQL Optimizer, which is equivalent to join. The Straight_join,mysql database forces the table on the left to be read first. For example, the following SQL statement:

Explain select * from 
table1 join table2 on 
table1.customer_id=table2.customer_id;

Its main output section is as follows:

+----+-------------+--------+------+---------------+
| id | select_type | table | type | possible_keys |
+----+-------------+--------+------+---------------+
| 1 | Simple   | table2 | All | NULL     |
| 1 | Simple   | table1 | All | PRIMARY    |
+----+-------------+--------+------+---------------+

We can see very clearly that MySQL is the first to select the Table2 table, and then make a match. If we specify the Straight_join method, for example:

Explain select * from 
table1 straight_join table2 on 
table1.customer_id=table2.customer_id;

The main output portion of the above statement is as follows:

+----+-------------+--------+------+---------------+
| id | select_type | table | type | possible_keys |
+----+-------------+--------+------+---------------+
| 1 | Simple   | table1 | All | PRIMARY    |
| 1 | Simple   | table2 | All | NULL     |
+----+-------------+--------+------+---------------+

As you can see, when you specify the Straight_join method, MySQL selects the Table1 table before matching it.

Then a reader asks, "What good is that?" Performance, or performance. Since I am here to test the data is relatively small, large data access, we specify Straight_join let MySQL read the left side of the table, so that MySQL according to our wishes to complete the join operation. We can consider using straight_join when performing performance optimization.
multiple table joins

In all of the examples above, I used a join between the two tables, and more often than not we have to join two tables in our work, possibly involving three or more table-joined query operations.

For a multiple-table join query for a INNER join, the order of the tables can be arranged without affecting the results of the query. This is because the optimizer automatically evaluates the order of Access tables based on cost. If you want to specify the join order, you can use the Straight_join summarized above.

For multiple table join queries for outer joins, the tables are positioned differently, involving the addition of external rows, which may affect the final result.
Summary

This is the entire content of the join operation in MySQL, although the content is much, but it is relatively simple, combined with the example in the article, and then the actual operation of their own, completely can be done. That's it for this article.