MySQL multi-table join introductory tutorial

Connections can be used to query, update, and establish factual foreign keys (referring to the correspondence between two tables created artificially. In contrast, FORGIEN KEY is also called physical foreign key)

The essence of table connection is the inverse constraint of foreign key

Connection conditions

Use ON to set the connection condition, or you can use WHERE instead.

In general

• ON: Set the connection condition
• WHERE: Filter the result set records

The essence of an unconditional JOIN inner join is a Cartesian product.

[INNER] JOIN inner join

In MySQL, JOIN, CROSS JOIN and INNER JOIN are equivalent.

Inner join means intersection, and only records in tables A and B that meet the join conditions are displayed. Records that do not meet the connection conditions are not displayed.

SELECT goods_id,goods_name,cate_name 
FROM tdb_goods 
INNER JOIN tdb_goods_cate 
ON tdb_goods.cate_id = tdb_goods_cate.cate_id;

Use inner join to update multiple tables:

--Change the goods_cate stored in Chinese in the tdb_goods table to the corresponding cate_id in the tdb_goods_cates table to save space UPDATE tdb_goods 
INNER JOIN tdb_goods_cates
ON goods_cate=cate_name 
SET goods_cate=cate_id;
--tdb_goods The name of the table you want to change --tdb_goods_cates The associated appendix --goods_cate=cate_name The relationship between the corresponding columns of the two tables --goods_cate=cate_id; Set value

Outer Join

Inner joins are used more often than outer joins.

If a field exists only in one table, the field in the other table returns NULL

LEFT [OUTER] JOIN Left outer join

Display all records in the left table and records in the right table that meet the connection conditions.

• If LEFT JOIN is used, and there is a record A in the left table, but no corresponding record is found in the right table, then the returned result will contain only the corresponding field content in record A, and all other fields are NULL (similar to RIGHT JOIN).

RIGHT [OUTER] JOIN Right outer join

Display all records in the right table and records in the left table that meet the connection conditions.

Multi-table join

Similar to joining two tables

Such as the connection of three tables:

SELECT goods_id,goods_name,b.cate_name,c.brand_name,goods_price
FROM products AS a 
INNER JOIN products_cate AS b 
ON a.goods_cate = b.cate_id
INNER JOIN products_brand AS c 
ON a.brand_name = c.brand_id;

Self-join

Design unlimited classification data table

There are both parent classes and child classes in the same table, which is essentially a tree:

You can query the same data table by connecting to it:

--Find the name corresponding to the parent class id SELECT s.type_id,s.type_name,p.type_name AS parent_name
FROM tdb_goods_types AS s 
LEFT JOIN tdb_goods_types AS p 
ON s.parent_id=p.type_id;

--Find the name corresponding to the subclass id SELECT p.type_id,p.type_name,s.type_name AS child_name
FROM tdb_goods_types AS p
LEFT JOIN tdb_goods_types AS s
ON p.type_id=s.parent_id;

--Find out how many children there are SELECT p.type_id,p.type_name,COUNT(s.type_name) AS child_count
FROM tdb_goods_types AS p
LEFT JOIN tdb_goods_types AS s
ON p.type_id=s.parent_id 
GROUP BY p.type_name
ORDER BY p.type_id;

Multiple table query and delete

Here, we use a self-join to simulate two tables, delete duplicate items in the table, and keep records with smaller goods_id.

DELETE t1 
FROM tdb_goods AS t1 
LEFT JOIN(--Select duplicate records of goods_nameSELECT goods_id,goods_name 
 FROM tdb_goods 
 GROUP BY goods_name --MySQL 5.7.5 and above versions enable the only_full_group_by SQL mode. The selected columns must be in the group or be aggregate columns (SUM, AVG, MAX, MIN). HAVING COUNT(goods_name)>=2 is not enabled here. 
AS t2 --Left join t1 and t2. In fact, inner join and right join are also possible. ON t1.goods_name=t2.goods_name --Join condition of t1 and t2 WHERE t1.goods_id>t2.goods_id; --Select the records that satisfy t1.goods_id>t2.goods_id in the LEFT JOIN result set

To help understand, the result of LEFT JOIN is:

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.