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

This article introduces the relevant knowledge of "MySQL multi-table query case analysis". In the operation of actual cases, many people will encounter such a dilemma. Next, let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Multi-table query case column description

The understanding of Cartesian product

Wrong select id,department_namefrom employees,departments;#, wrong select id,department_id,department_namefrom employees CROSS JOIN departments;#.

Each employee matches each department once (number of entries checked out = id * department)

Error reason: lack of connection condition

The solution of Cartesian product

Write connection conditions: table 1. Column = Table 2. Column (if multiple tables are joined, at least 1 join condition should be used)

Select id,employees.name,department_name from employees,departmentsWHERE employees.name = departments.name

Note: if the column to be displayed has the same name in the table to be queried, indicate which table it comes from, eg: employees.name

It is recommended that when querying multiple tables, indicate which table is displayed (optimization).

Optimization: aliases for tables can be used after FROM, but once aliases are used, aliases must be used later.

Classified equivalent join and non-equivalent join of multi-table query

Equivalent connection: above with =

Non-equivalent connection: no =

Select t1.idt2WHERE ti.salary BETWEEN t2.lowest_salary AND t2.highest_salary t1.nameMagery t2.gradefrom employees t1recoverable t2WHERE ti.salary BETWEEN t2.lowest_salary AND t2.highest_salary; # non-equivalent self-join and non-self-join

Non-self-linking: table 1 and Table 2 connections

Self-linking: table 1 connects to yourself

# display the basic information of employees (T1) and their managers (T2) select t1.iddiary t1.namememery t2.idrecoveryt2.namefrom employees T1 precincts one table as two tables WHERE t1.manage_id = t2.id; # self-join inner join and outer join

Inner join: merge tables with the same column, excluding rows that do not match one table with another

Outer join: merge tables with the same column, resulting in a query for mismatched rows in addition to the results of the inner join

Classification of outer joins: left outer joins (left table, right side), right outer joins (right table, left side), full outer joins

SQL92: use (+) to create a connection

Internal connection: see above

Outer join: if there is a data mismatch in the left table, add (+) in the right table; otherwise, add (+) in the left table, but MySQL does not support it.

WHERE t1.department_id = t2.department_id (+) # left connection SQL99: use JOIN...ON to connect select t1.iddiary t1.namememery t2.departmenttrainnamehandlert3.mentfrom employees T1 JOIN departments t2ON t1.department_id = t2.department_idJOIN locations tweak to join the second table ON t2.department_location = t3.departmentalization location; external connection

Use OUTER JOIN...ON...

Left outer connection: LEFT OUTER JOIN

Right outer connection: RIGHT OUTER JOIN

Full external connection: FULL OUTER JOIN (not supported by MySQL)

Select t1.namedirection t2.departmentaccountame# left outer connection from employees T1 LEFT OUTER (may be omitted) JOIN departments t2ON t1.department_id = t2.departmentaccounidence usage of user

Merge query results

SELECT colum... FROM table1UNION (ALL) SELECT colum... FROM table2

UNION operator

The union of two query results, deduplicated (inefficient)

UNION ALL operator (recommended)

The union of two query results, not duplicated (efficient)

Implementation of 7 kinds of SQL JOINS

Middle picture (inner connection):

Select t1.namedirection t2.departmentdesignnamefrom employees T1 JOIN departments t2ON t1.department_id = t2.department_id

Top left (left outer link):

Select t1.namedirection t2.departmentdesignnamefrom employees T1 LEFT JOIN departments t2ON t1.department_id = t2.department_id

Top right (right outer connection):

Select t1.namedirection t2.departmentdesignnamefrom employees T1 RIGHT JOIN departments t2ON t1.department_id = t2.department_id

Middle left:

Select t1.namedirection t2.departmentdesignnamefrom employees T1 LEFT JOIN departments t2ON t1.department_id = t2.department_idWHERE t2.department_id IS NULL

Middle right:

Select t1.namedirection t2.departmentdesignnamefrom employees T1 RIGHT JOIN departments t2ON t1.department_id = t2.department_idWHERE t1.department_id IS NULL

The following image on the left (full outer connection):

# method 1: upper left, middle right, UNION ALL, select t1.name, LEFT JOIN departments t2ON t1.department_id from employees T1, LEFT JOIN departments t2ON t1.department_id = t2.department_idUNION ALL select, t1.name, RIGHT JOIN departments t2ON t1.department_id, from employees T1, RIGHT JOIN departments t2ON t1.department_id = t2.department_idWHERE t1.department_id IS NULL # method 2: middle left, UNION ALL, top right, select t1.name, LEFT JOIN departments t2ON t1.department_id from employees T1, LEFT JOIN departments t2ON t1.department_id = t2.department_idWHERE t2.department_id IS NULLUNION ALLselect, t1.name. RIGHT JOIN departments t2ON t1.department_id from employees T1, RIGHT JOIN departments t2ON t1.department_id = t2.department_id

The image below on the right:

# Middle left, middle right, select t1.namecamera t2.departmentyognamefrom employees T1 LEFT JOIN departments t2ON t1.department_id = t2.department_idWHERE t2.department_id IS NULLUNION ALLselect t1.namelemagement t2.departmentsecretnamenamefrom employees T1 RIGHT JOIN departments t2ON t1.department_id = t2.department_idWHERE t1.department_id IS NULL;SQL syntax New Features Natural connection

Use keywords: NATURAL JOIN (inflexible), automatically query all the same fields in the table, and then make an equivalent join

USING connection (not applicable for self-connection)

Use the keyword: USING (field with the same name) to automatically equate fields with the same name in the table.

Select t1.namedirection t2.departmentaccountamefrom employees T1 JOIN departments t2ON t1.department_id = t2.departmentroomid; equivalent to select t1.namememery t2.departmentaccountamefrom employees T1 JOIN departments t2USING (department_id); this is the end of the content of MySQL multi-table query case analysis. Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.