A simple example of MySQL joint table query

MySql uses joined table queries, which may be difficult to understand for beginners. The following article will give you a detailed introduction to the relevant content of MySQL joint table query, and share it for your reference and learning. Let's take a look at the detailed introduction together.

In a relational database, it is inevitable that there are various references and associations between tables. These associations are formed by pairing primary keys with foreign keys. Therefore, when retrieving data, in most cases a single table cannot meet the needs, and additional data requires adding other tables to the query. This is the operation completed by the JOIN keyword.

• In MySQL, JOIN, CROSS JOIN and INNER JOIN have the same syntax and functions and are interchangeable. However, in the SQL standard, INNER JOIN needs to be used with the ON statement.

When querying multiple tables, you can omit the JOIN keyword and separate multiple tables with commas. In this case, it will be treated as an INNER JOIN by default. for example,

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

is equivalent to:

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

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

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

• The syntax of the joint table condition specified by ON is the same as that of WHERE. All expressions acceptable to the latter can be used for ON. The two seem to have similar functions. ON is generally used to specify the join condition, that is, how the tables are joined, while WHERE is used to filter the results.
• In LEFT JOIN, if the right table does not meet the conditions specified by ON or USING, 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;

This method can be used to easily filter out records in the right table that do not meet the conditions.

• When querying a joint table, you can specify an alias for each participating table to facilitate 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. Only the columns specified here are compared during the query.

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

• NATURAL [LEFT] JOIN is equivalent to INNER JOIN and LEFT JOIN with USING specifying 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 data that do not match the left table are presented as NULL in the result. To facilitate migration between different databases, it is recommended to always use LEFT JOIN.

Some JOIN examples:

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 will be no duplicate columns in the result of a NATURAL JOIN. Because it is similar to USING, there are no complex columns when 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 appear only once in the result, 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 the same and can be converted to each other. But there are still differences when SELECT * returns results. The former returns the merged results only in the columns specified in USING, while the latter targets 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

Returns in the USING case:

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

ON returns:

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

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

CREATE TABLE t1 (i1 INT);
CREATE TABLE t2 (i2 INT);
CREATE TABLE t3 (i3 INT);

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

mysql> SELECT * FROM t1 JOIN t2 ON (i1 = i3) JOIN t3;
ERROR 1054 (42S22): Unknown column 'i3' in 'on clause'

The following query works:

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

Because now t3 is within the operating range 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

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM.