Shulou(Shulou.com)06/01 Report--

MySQL query is divided into inner join query and outer join query. The difference between them is that the two representations of inner join query are peer-to-peer, which are matched according to conditions; the outer join is mainly based on a certain table, and two tables are associated according to conditions. The outer connection is divided into left outer connection, right outer connection and full external connection. This paper focuses on the idea of each external connection, and how to realize the full external connection, and gives an example.

Left outer connection

The left outer join is based on the left table. According to the condition, attach the right table to the left table. Syntax: SELECT * FROM A LEFT JOIN B ON condition. The geometry relationship is shown in the following figure, that is, the query result set contains not only all the data of Table A, but also the data of Table B that meets the conditions:

Right outer connection

The right outer join is based on the right table. According to the condition, attach the left table to the right table. Syntax: SELECT * FROM A RIGHT JOIN B ON condition. The geometry relationship is shown in the following figure, that is, the query result set not only contains all the data in Table B, but also contains Table A data that meets the conditions:

Full external connection

Full external join is not only the data that can be matched according to the conditions, but also contains data that do not match in the left and right tables (the default should be null). In the case of the application of full external join, there is usually a main line that connects the left and right tables. The geometric relationship is shown in the following figure, corresponding to the union of An and B (de-duplicated):

Unfortunately, MySQL does not support full-outer join, so how can it be implemented when full-outer join query is required? The most common is the merging of left and right connections.

Example

There is such a scenario in the project: a task task has two different states, todo and done, which are stored in the todolist and donelist tables, and the tasks are stored in the task table. Now you need to count the processed and unprocessed status of each task. First of all, to the full external connection, so how to achieve it?

For example, the structure of the table is as follows:

There are four ways to implement:

1, left connection, right connection, merge; (need to keep the structure of the two result sets consistent)

The first is the left connection:

1 SELECT 2 A.id AS Aid, 3 B.id AS Bid, 4 A.taskid tid 5 FROM 6 (7 SELECT 8 * 9 FROM10 todolist11 WHERE12 todolist.user = 'Zhang San' 13) A14 LEFT JOIN (15 SELECT16 * 17 FROM18 donelist19 WHERE20 donelist.user = 'Zhang San' 21) B ON A.taskid = B.taskid

Query results:

The second is the right join (note that due to the need for merging, the result set structure of the left and right joins needs to be consistent):

1 SELECT 2 A.id AS Aid, 3 B.id AS Bid, 4 A.taskid tid 5 FROM 6 (7 SELECT 8 * 9 FROM10 todolist11 WHERE12 todolist.user = 'Zhang San' 13) A14 RIGHT JOIN (15 SELECT16 * 17 FROM18 donelist19 WHERE20 donelist.user = 'Zhang San' 21) B ON A.taskid = B.taskid

Query results:

Finally, merge and join inside the task table:

1 SELECT 2 SUM (IF (Aid IS NOT NULL, 1,0)) todo, 3 SUM (IF (Bid IS NOT NULL, 1,0)) done, 4 task.name 5 FROM 6 (7 SELECT 8 A.id AS Aid, 9 B.id AS Bid) 10 A.taskid tid11 FROM12 (13 SELECT14 * 15 FROM16 todolist17 WHERE18 todolist.user = 'Zhang San' 19) A20 LEFT JOIN (21 SELECT22 * 23 FROM24 donelist25 WHERE26 donelist.user = 'Zhang San' 27) B ON A.taskid = B.taskid28 UNION29 SELECT30 A.id AS Aid 31 B.id AS Bid 32 B.taskid tid33 FROM34 (35 SELECT36 * 37 FROM38 todolist39 WHERE40 todolist.user = 'Zhang San' 41) A42 RIGHT JOIN (43 SELECT44 * 45 FROM46 donelist47 WHERE48 donelist.user = 'Zhang San' 49) B ON A.taskid = B.taskid50) AS AB51 INNER JOIN task ON task.id = AB.tid52 GROUP BY53 task.name

The running result is shown in the following table to achieve full external connection:

2. AbeliB is connected to the left, and Bmura removes the record from the left connection to A, and then merges the two result sets; (the structure of the two result sets needs to be kept consistent)

This is another way to achieve full external join, that is, first query the left join of A B, then remove the records left connected to An in query B, and finally merge (A represents todolist,B for donelist):

AbeliB left connection

1 SELECT 2 1 AS todo, 3 CASE 4 WHEN B.id IS NOT NULL THEN 5 1 6 ELSE 7 0 8 END AS done 9 A.taskid tid 10 FROM11 (12 SELECT13 * 14 FROM15 todolist16 WHERE17 todolist.user = 'Zhang San' 18) A19 LEFT JOIN (20 SELECT21 * 22 FROM23 donelist24 WHERE25 donelist.user = 'Zhang San' 26) B ON A.taskid = B.taskid

Query results:

Bmura removes the record connected to An on the left.

1 SELECT 20 AS todo, 3 1 AS done, 4 donelist.taskid tid 5 FROM 6 donelist 7 WHERE 8 donelist.user = 'Zhang San' 9 AND NOT EXISTS (10 SELECT11 * 12 FROM13 todolist14 WHERE15 todolist.taskid = donelist.taskid16 AND donelist.user = 'Zhang San' 17 AND odolist.user = donelist.user18)

Query results:

Merge

1 SELECT 2 SUM (AB.todo) todo, 3 SUM (AB.done) done, 4 task.name 5 FROM 6 (7 SELECT 8 1 AS todo, 9 CASE10 WHEN B.id IS NOT NULL THEN11 112 ELSE13 014 END AS done 15 A.taskid tid16 FROM17 (18 SELECT19 * 20 FROM21 todolist22 WHERE23 todolist.user = 'Zhang San' 24) A25 LEFT JOIN (26 SELECT27 * 28 FROM29 donelist30 WHERE31) Donelist.user = 'Zhang San' 32) B ON A.taskid = B.taskid33 UNION34 SELECT35 0 AS todo 36 1 AS done 37 donelist.taskid tid38 FROM39 donelist40 WHERE41 donelist.user = 'Zhang San' 42 AND NOT EXISTS (43 SELECT44 * 45 FROM46 todolist47 WHERE48 todolist.taskid = donelist.taskid49 AND donelist.user = 'Zhang 3'50 AND odolist.user = donelist.user51) 52) AB53 INNER JOIN task ON task.id = AB.tid54 GROUP BY55 task.name

The result is the same as above.

3. Based on the task table, connect An and B tables to the left to realize the query.

The idea of this method is that no matter what the relationship between An and B tables is, they are all related to the table task as the main line, and only need to join An and B tables to the task table on the left to get the joined data set, which is the final result set that needs to be queried. The SQL code is as follows:

1 SELECT 2 SUM (AB.todo) AS todo, 3 SUM (AB.done) AS done, 4 task.name 5 FROM 6 (7 SELECT 8 task.name, 9 CASE10 WHEN A.id IS NULL THEN11 012 ELSE13 114 END AS todo 15 CASE16 WHEN B.id IS NULL THEN17 018 ELSE19 120 END AS done21 FROM22 task23 LEFT JOIN (24 SELECT25 * 26 FROM27 todolist28 WHERE29 todolist.user = 'Zhang San' 30) An ON A.taskid = task.id31 LEFT JOIN (32 SELECT33 * 34 FROM35 donelist36 WHERE37 donelist.user = 'Zhang San' 38) B ON B.taskid = task.id39 WHERE40 A.id IS NOT NULL41 OR B.id IS NOT NULL42) AB43 GROUP BY44 task.name

The query results are the same as above, but this method has some drawbacks, that is, when the mainline table (task table) is very large, the performance will be poor.

4. Table A looks up status a, table B looks up status b, and then merges. (need to keep the structure of the two result sets consistent)

The method is that regardless of the relationship between An and B tables, it is queried according to the conditions, and then merged. The SQL statement is as follows:

1 SELECT 2 SUM (A.todo) todo, 3 SUM (A.done) done, 4 task.name 5 FROM 6 (7 SELECT 8 1 todo, 9 0 done,10 todolist.taskid tid11 FROM12 todolist13 WHERE14 todolist.user = 'Zhang San' 15 UNION ALL16 SELECT17 0 todo 18 1 done,19 donelist.taskid tid20 FROM21 donelist22 WHERE23 donelist.user = 'Zhang San' 24) A25 INNER JOIN task ON task.id = A.tid26 GROUP BY27 task.name

The query results are the same as above.

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.