In-depth understanding of MySQL self-connection and join association

1. MySQL self-connection

MySQL sometimes needs to connect to itself (self-connection) when querying information, so we need to define an alias for the table. Let's take an example. The following is a commodity purchase table. We need to find all the information whose purchase price is higher than Huihui's.

Generally speaking, when we see this table, we first operate it with statements:

SELECT * FROM shopping WHERE price>27

As you can imagine, this is very simple. What if you don’t know the detailed data of the database table or the amount of data is quite large? As a database administrator, we have to use other methods to quickly find the data we need.

Step by step query

The simplest way is also the easiest to think of:

SELECT price FROM shopping WHERE name='惠惠' //The price query result is 27
SELECT * FROM shopping WHERE price>27

Compared with the self-connection method, this method requires manual intervention on the intermediate results, which is obviously not conducive to automatic processing operations in the program.

Self-connection method:

SELECT b.* 
from shopping as a,shopping as b
where a.name='惠惠' 
and a.price<b.price 
order by b.id

We can get the following table information:

Note:

Although the aliases a and b have different names, they are the same table. The purpose of defining aliases is to make it easier to delete and select them.

Execute select through (intermediate table) to obtain b.*, which is the final result.

Subqueries

Subquery is also a commonly used method, which is to nest select in select.

The implementation code is as follows:

SELECT * FROM shopping 
WHERE price>(select price from 'shopping' where name='惠惠')

The results are shown below, and it can be seen that the results obtained by both methods are the same:

JOIN

INNER JOIN

The main function of inner join is to return the result set when there is at least one match in the table. The inner join and join here have the same function, so they are introduced together.
Two tables are given below, namely goods and category tables:

SELECT * FROM goods INNER JOIN category 
ON goods.id=category.goods_id 
ORDER BY gods.id

The result is as follows:

LEFT JOIN

The LEFT JOIN keyword returns all rows from the left table (table_name1), even if there are no matching rows in the right table (table_name2). It is recommended to use left association when doing projects. However, there are many table associations, one table is left-joined to a, but right-joined to b at the same time. In this case, adding a right join may make it easier to write.

Apply the first two tables to perform a left join query:

SELECT goods.*,category.cate_name 
FROM goods LEFT JOIN category 
ON goods.id=category.goods_id 
ORDER BY goods.id 

RIGHT JOIN

The RIGHT JOIN keyword will return all rows from the right table (table_name2), even if there are no matching rows in the left table (table_name1). Apply the first two tables to perform a right join query:

SELECT a.goods_name,a.price,b.*
FROM goods as a
RIGHT JOIN category as b
ON a.id=b.goods_id
ORDER BY b.id 

For multi-table associations, just add a few more association statements.

Summarize

The above is the full content of this article. I hope that the content of this article can bring some help to your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support of 123WORDPRESS.COM.