Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to achieve join table query in MySQL. The content of the article is of high quality, so the editor shares it for you as a reference. I hope you will have some understanding of the relevant knowledge after reading this article.

In MySQL, the grammatical functions of JOIN, CROSS JOIN and INNER JOIN are the same and interchangeable, while in the SQL standard, INNER JOIN needs to be matched with ON statements.

When multi-table federated queries, the JOIN keyword can be omitted and multiple tables can be separated by commas, which will be treated as INNER JOIN by default. such as,

SELECT table1.*, table2.* FROM table1, table2

Equivalent to:

SELECT table1.*, table2.* FROM table1 INNER JOIN table2

However, this form of concatenated table, which is implicitly specified by comma, has lower priority than that specified directly by keywords (INNER JOIN, CROSS JOIN, LEFT JOIN). So T1, T2 JOIN T3 will be parsed to (T1, (T2 JOIN T3)) instead of (T1, T2) JOIN T3)

It is important to note that when the comma form is combined with other join table keywords, an error is reported when a join table condition is specified, such as through the ON condition.

The concatenated table condition specified by ON has the same syntax as WHERE, and all expressions acceptable to the latter can be used with ON. The two look similar in function. ON is generally used to specify join table conditions, that is, how tables are joined, while WHERE is used to filter results.

When LEFT JOIN, if the conditions specified by ON or USING are not met in the table on the right, it will be rendered as NULL in the result.

SELECT left_tbl.* FROM left_tbl LEFT JOIN right_tbl ON left_tbl.id = right_tbl.id WHERE right_tbl.id IS NULL

Through this method, the records that do not meet the conditions in the table on the right can be easily filtered.

When you join a table query, you can specify an alias for each participating table for easy reference in other expressions. There are two ways, one is through the AS keyword tbl_name AS alias_name, and the other is to directly follow the table name with the alias, tbl_name alias_name.

SELECT t1.name, t2.salary FROM employee AS T1 INNER JOIN info AS T2 ON t1.name = T2 name _ select t1.name, t2.salary FROM employee T1 INNER JOIN info T2 ON t1.name = t2.name

A subquery in a query statement must have an alias so that it can be referenced in other expressions.

SELECT * FROM (SELECT 1,2,3) AS T1

The USING (join_column_list) statement specifies the columns contained in both tables, and the query is compared only against the columns specified here.

A LEFT JOIN b USING (C1, c2, c3)

NATURAL [LEFT] JOIN is equivalent to the use of USING in conjunction with INNER JOIN and LEFT JOIN to specify all columns in a table.

RIGHT JOIN is similar to LEFT JOIN, except that the final result is based on the right table, and the nonconformities in the left table are presented as NULL in the results. To facilitate migration between different databases, it is recommended that you always use LEFT JOIN.

Some examples of JOIN:

SELECT * FROM table1, table2;SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id;SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id;SELECT * FROM table1 LEFT JOIN table2 USING (id); SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id LEFT JOIN table3 ON table2.id = table3.id

There are no duplicate columns in the results of NATURAL JOIN. Because it is similar to USING, there are no complex columns in USING.

Consider the following example:

CREATE TABLE T1 (I INT, j INT); CREATE TABLE T2 (k INT, j INT); INSERT INTO T1 VALUES (1,1); INSERT INTO T2 VALUES (1,1); SELECT * FROM T1 NATURAL JOIN T2 select * FROM T1 JOIN T2 USING (j)

Query results:

+-+

| | j | I | k | |

+-+

| | 1 | 1 | 1 |

+-+

+-+

| | j | I | k | |

+-+

| | 1 | 1 | 1 |

+-+

As a result, columns with the same name appear only once, and they are all records with the same value.

Test the j of the two tables by inserting a new record into them.

Mysql > INSERT INTO T1 VALUES (2,2); Query OK, 1 row affected (0.00 sec) mysql > INSERT INTO T2 VALUES (2,3); Query OK, 1 row affected (0.00 sec) mysql > select * from T1 natural join T2 +-+ | j | I | k | +-+ | 2 | 2 | 1 | +-+ 1 row in set (0.00 sec)

When USING and ON are used as conditions, the joint conditions of other restrictions are the same and can be converted into each other. However, there is a difference when SELECT * returns the result. The former returns the merged results only in the columns specified in the USING, while the latter is for all columns in the table.

A LEFT JOIN b USING (C1, c2, c3) a LEFT JOIN b ON a.c1 = b.c1 AND a.c2 = b.c2 AND a.c3 = b.c3

Return in the case of USING:

COALESCE (a.c1, b.c1), COALESCE (a.c2, b.c2), COALESCE (a.c3, b.c3)

The return value of ON:

A.c1, a.c2, a.c3, b.c1, b.c2, b.c3

Only tables in its operation table (operands) can be referenced in an ON statement.

CREATE TABLE T1 (i1 INT); CREATE TABLE T2 (i2 INT); CREATE TABLE T3 (i3 INT)

For the above table, the following query reports an error:

Mysql > SELECT * FROM T1 JOIN T2 ON (i1 = i3) JOIN T3 scape error 1054 (42S22): Unknown column'i3'in'on clause'

The following query can:

Mysql > SELECT * FROM T1 JOIN T2 JOIN T3 ON (i1 = i3); Empty set (0.00 sec) is here on how to implement linked table query in MySQL. I hope the above content can be helpful to you and learn more. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.