Simple example of MySQL join table query

MySql will use joint table query, which may be difficult for beginners to understand. The following article will give you a detailed introduction to MySQL table query related content, share it for your reference and study, the following words are not much to say, to see a detailed introduction

In relational database, there are various references and associations between tables. These associations are formed by matching primary keys with foreign keys. Therefore, when fetching data, a single table can't meet the demand in many cases, and other tables need to be added to the query for additional data, which is the operation completed by JOIN keyword.

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

In a multi-table union query, you can omit the JOIN keyword and separate multiple tables with commas, which will be treated as INNER JOIN by default. For example,

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

Equivalent to:

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

However, this join table form implicitly specified by commas has lower priority than the form directly specified by keywords (INNER JOIN, CROSS JOIN, LEFT JOIN). So t1, t2 JOIN t3 will be resolved into (t1, (t2 JOIN t3) instead of ((t1, t2) JOIN t3)

It should be noted that when comma form is combined with other join table keywords, an error will be reported when join table conditions are specified, such as ON conditions.

ON specifies a join-table condition that has the same syntax as WHERE, and all the expressions accepted by the latter can be used with ON. The two look functionally identical, with ON 1 generally used to specify join conditions, i.e. how tables are joined, and WHERE used to filter results. When LEFT JOIN, when the conditions specified by ON or USING are not met in the right table, it will be presented 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;

By this method, the unqualified records in the right table can be easily filtered out.

When querying a join table, you can specify an alias for each participating table, which is convenient to refer to 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 alias after the table name, 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;

Subqueries in 1 query statement must take 1 alias in order to 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 compares only the columns specified here.

a LEFT JOIN b USING (c1, c2, c3)

NATURAL [LEFT] JOIN works with INNER JOIN and LEFT JOIN to specify the equivalent of all columns in the table. RIGHT JOIN is similar to LEFT JOIN, except that the final result is based on the right table, and the non-conformance in the left table is presented as NULL in the result. To facilitate migration between different databases, it is recommended to always use LEFT JOIN.

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

Columns with the same name in the result appear only once, and they are all records with the same value.

By inserting a new record into the two tables, make their j different, and then test it.

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 like 1 and can be converted to each other. However, when SELECT * returns results, there are still differences. The former returns the merged results only in the columns specified in 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 for USING:

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

0

Return of ON:

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

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

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

1

For the above table, the following query will report an error:

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

2

The following query can:

mysql> SELECT * FROM t1 JOIN t2 JOIN t3 ON (i1 = i3);
Empty set (0.00 sec)

Because t3 is now within the scope of the ON statement.

Related resources

MySQL 8.0 Reference Manual - 13.2.10.2 JOIN Syntax MySQL 8.0 Reference Manual - 13.2.10.3 UNION Syntax

Summarize