Mysql Query tip: Use Join to refine subqueries

1. Data preparation

Mysql> select * from student;

+----+--------+----------+---------+-------------+

| ID | name | Idcardno | Iscadre | Nickname |

+----+--------+----------+---------+-------------+

| 1 | Tom |       350020 | 1 | Big T |

| 2 | Jim |    350022 | NULL | PP |

| 3 | Lucy |    460311 | NULL | Little Girl |

| 4 | Liming |       733098 | 1 | NULL |

+----+--------+----------+---------+-------------+

Mysql> select * from cadre;

+----+--------+----------+-----------+

| ID | name | Idcardno | Nickname |

+----+--------+----------+-----------+

| 1 | Tom | 350020 | Last Tom |

| 2 | Max | 636095 | Hand |

| 3 | Liming | 733098 | China Boy |

+----+--------+----------+-----------+

Requirements: Query out all personnel information in the student table and their nickname in the Cadre table

① Sub-Query method

Select A.idcardno, A.name, A.nickname, (select B.nickname from cadre b WHERE b.name=a.name) as nickname_b from student A;

+----------+--------+-------------+------------+

| Idcardno | name | Nickname | Nickname_b |

+----------+--------+-------------+------------+

| 350020 | Tom | Big T | Last Tom |

| 350022 | Jim | PP | NULL |

| 460311 | Lucy | Little Girl | NULL |

| 733098 | Liming | NULL | China Boy |

+----------+--------+-------------+------------+

Sub-query, each record for the student table is queried for the cadre table and is less efficient.

② using the Join optimization method

SELECT A.idcardno, A.name, A.nickname, b.nickname from student a left joins cadre B on a.name=b.name;

+----------+--------+-------------+-----------+

| Idcardno | name | Nickname | Nickname |

+----------+--------+-------------+-----------+

| 350020 | Tom | Big T | Last Tom |

| 733098 | Liming | NULL | China Boy |

| 350022 | Jim | PP | NULL |

| 460311 | Lucy | Little Girl | NULL |

+----------+--------+-------------+-----------+

　

Mysql Query tip: Use Join to refine subqueries