Summary of several MySQL join query methods and mysql operation methods

Summary of several MySQL join query methods and mysql operation methods

Preface

The various services of the system are so complex that data is stored in various tables in the database. This primary key, that foreign key, the tables and tables depend on these primary keys and Foreign keys. When we perform business operations, we need to establish a relationship between multiple tables using SQL statements, and then perform various SQL operations. When using SQL to write various operations, how can I use SQL statements to join multiple tables for business operations? This article summarizes this knowledge point.

Join query is a common database operation, that is, matching between two tables (multiple tables. The MySQL database supports the following join queries:

• CROSS JOIN)
• Inner join (inner join)
• Outer join (outer join)
• Others

When performing various join operations, remember the first three steps of SQL logic query statement execution summarized in SQL logic query statement execution sequence:

• Execute the FROM Statement (Cartesian Product)
• Execute ON Filter
• Add external row

Each join only occurs between two tables, even if the FROM clause contains multiple tables. Each join operation only performs the first three steps of the logical query statement. Each time a virtual table is generated, the virtual table is connected to the next table in the FROM clause in sequence. Repeat the preceding steps, until all tables in the FROM clause are processed.
Preparations

1. Create a test database TestDB;

create database TestDB;

Create test tables table1 and table2;

   CREATE TABLE table1   (     customer_id VARCHAR(10) NOT NULL,     city VARCHAR(10) NOT NULL,     PRIMARY KEY(customer_id)   )ENGINE=INNODB DEFAULT CHARSET=UTF8;   CREATE TABLE table2   (     order_id INT NOT NULL auto_increment,     customer_id VARCHAR(10),     PRIMARY KEY(order_id)   )ENGINE=INNODB DEFAULT CHARSET=UTF8;

Insert 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');

After the preparation is complete, table1 and table2 should look like the following:

   mysql> select * from table1;   +-------------+----------+   | customer_id | city   |   +-------------+----------+   | 163     | hangzhou |   | 9you    | shanghai |   | baidu    | 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)

The preparation work is almost done. Let's start with today's summary.
Cross join (cross join)

Cross join performs the FROM Statement (Cartesian Product) operation on the two tables and returns a combination of all columns in the two tables. If the left table has m rows of data and the right table has n rows of data, execute cross join to return m * n rows of data. Cross join only executes the first step in the first three steps of SQL logical query statement execution.

What can cross join do? Because cross join only performs the Cartesian Product Operation and does not filter data, we can use cross join to generate a large amount of test data.

Use the following query to query the test data:

select * from table1 cross join table2;

The following result is displayed:

+-------------+----------+----------+-------------+| 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 (inner join)

Inner join is more powerful than cross join, because inner join can match data between tables based on some filtering conditions. In the first three steps of SQL logic query statement execution, INNER JOIN executes Step 1 and step 2, that is, no step 3, no external rows are added, this is one of the biggest differences between inner join and outer join.

Now let's take a look at using inner join:

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

The following result is displayed:

+-------------+----------+----------+-------------+| 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     |+-------------+----------+----------+-------------+

If the ON condition is not used for inner join, inner join and cross join have the same effect. When the filtering condition column set in ON has the same name, we can use the USING keyword to abbreviated ON filtering condition, which can simplify the SQL statement, for example:

select * from table1 inner join table2 using(customer_id);

When writing an SQL statement, we can omit the INNER keyword, for example:

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

But remember, this is still inner join.
Outer join (outer join)

Oh, I remember I had an interview and asked me this question. I 'd like to summarize it here. Through outer join, we can match data between tables according to some filtering conditions. The result set of outer join is equal to the result set of inner join plus external rows. That is to say, when outer join is used, the first three steps of SQL logic query statement execution are all performed. For more information about how to add external rows, see add external rows in SQL logic query statement execution sequence.

MySQL supports left outer join and right outer join. Like the INNER keyword, We can omit the OUTER keyword. For outer join, you can also use USING to simplify the ON clause. Therefore, for the following SQL statements:

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

This can be abbreviated as follows:

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

However, there is another difference with inner join: For outer join, The ON (or using) clause must be specified. Otherwise, the MySQL database will throw an exception.
Natural join (natural join)

Natural join is equivalent to the combination of INNER (OUTER) JOIN and USING. It implicitly matches columns with the same name in two tables. Similarly, natural left (RIGHT) JOIN is equivalent to the combination of LEFT (RIGHT) JOIN and USING. For example:

select * from table1 join table2 using(customer_id);

And

select * from table1 natural join table2;

Equivalent.

For example:

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

And

select * from table1 natural left join table2;

Equivalent.
STRAIGHT_JOIN join

STRAIGHT_JOIN is not a new JOIN type, but a user's control over the SQL optimizer. It is equivalent to JOIN. Through STRAIGHT_JOIN, the MySQL database forcibly reads the table on the left. For example, the following SQL statement:

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

Its main output is as follows:

+----+-------------+--------+------+---------------+| id | select_type | table | type | possible_keys |+----+-------------+--------+------+---------------+| 1 | SIMPLE   | table2 | ALL | NULL     || 1 | SIMPLE   | table1 | ALL | PRIMARY    |+----+-------------+--------+------+---------------+

We can clearly see that MySQL is the first selected table 2 and then matched. 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 of the preceding 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 the STRAIGHT_JOIN method is specified, MySQL selects Table 1 first and then performs matching.

So what are the advantages of this? Performance or performance. Because I tested a small amount of data here and accessed a large amount of data, we specified STRAIGHT_JOIN to allow MySQL to read the table on the left first, so that MySQL can complete the join operation as needed. When optimizing performance, we can consider using STRAIGHT_JOIN.
Multi-table join

In all the examples above, I use the join between two tables. In more cases, we may not just join two tables at work, the join query operation for three or more tables may be involved.

For inner join multi-table JOIN queries, you can arrange the order of the tables without affecting the query results. This is because the optimizer automatically accesses the query table order based on the cost evaluation. If you want to specify the join order, you can use the STRAIGHT_JOIN summarized above.

For the multi-table JOIN query of outer join, the table location is different and the problem of adding external rows may affect the final result.
Summary

This is all the content of the connection operation in MySQL. Although there are many content, it is still relatively simple. Combined with the example in the article, you can perform the operation on your own. This is the case in this article.