[mysql] classification of multi table query

Posted by lancey10 on Fri, 04 Mar 2022 02:53:00 +0100

Multi table query classification

Category 1: equivalent connection vs non equivalent connection

1. Equivalent connection

SELECT employees.employee_id, employees.last_name, 
       employees.department_id, departments.department_id,
       departments.location_id
FROM   employees, departments
WHERE  employees.department_id = departments.department_id;

Extension 1: multiple connection conditions AND and operators

Extension 2: distinguish duplicate column names

When multiple tables have the same column, the column name must be prefixed with the table name.
Columns with the same column name in different tables can be distinguished by table name.

SELECT employees.last_name, departments.department_name,employees.department_id
FROM employees, departments
WHERE employees.department_id = departments.department_id;

Extension 3: alias of table

Using aliases can simplify queries.
Using table name prefix before column name can improve query efficiency.

SELECT e.employee_id, e.last_name, e.department_id,
       d.department_id, d.location_id
FROM   employees e , departments d
WHERE  e.department_id = d.department_id;

It should be noted that if we use the alias of the table, we can only use the alias in the query field and filter criteria instead of the original table name, otherwise an error will be reported.

Alibaba development specification:

[mandatory] for the query and change of table records in the database, as long as multiple tables are involved, the alias (or table name) of the table needs to be added before the column name.

Note: when querying, updating and deleting records for multiple tables, if the alias (or table name) of the table is not limited to the operation column, and the operation column exists in multiple tables, an exception will be thrown.

Positive example: select T1 name from table_ first as t1 , table_ second as t2 where t1. id=t2. id;

Counterexample: in a business, because the multi table associated query statement does not have the restriction of adding the alias (or table name) of the table, after two years of normal operation, a field with the same name has been added to a table recently. After the database changes in the pre publishing environment, 1052 exceptions appear in the online query statement: column 'name' in field list is ambiguous.

Extension 4: connecting multiple tables

Summary: connecting n tables requires at least n-1 connection conditions. For example, connecting three tables requires at least two connection conditions.
Exercise: query the last of the company's employees_ name,department_ name, city

SELECT e.last_name,d.department_name,l.city
FROM employees e,departments d,locations l
WHERE e.department_id = d.department_id
AND d.location_id = l.location_id

2. Non equivalent connection

SELECT e.last_name, e.salary, j.grade_level
FROM   employees e, job_grades j
WHERE  e.salary BETWEEN j.lowest_sal AND j.highest_sal;
or
WHERE e.salary >= j.lowest_sal AND e.salary <= j.highest_sal;

Category 2: self connected vs non self connected

When table1 and table2 are essentially the same table, they are only virtualized into two tables by taking aliases to represent different meanings. Then the two tables are queried for internal connection and external connection.

Title: query the employees table and return "XXX works for XXX"

SELECT CONCAT(worker.last_name ,' works for ' 
       , manager.last_name)
FROM   employees worker, employees manager
WHERE  worker.manager_id = manager.employee_id ;

Examples of self connection: Query employee id, employee name and the id and name of its manager

SELECT emp.employee_id,emp.last_name,mgr.employee_id,mgr.last_name
FROM employees emp ,employees mgr
WHERE emp.`manager_id` = mgr.`employee_id`;

Category 3: internal connection vs external connection

In addition to querying the records that meet the conditions, the external connection can also query the records that one party does not meet the conditions.

Inner join: merge rows of more than two tables with the same column. The result set does not contain rows that do not match one table with another

SELECT employee_id,department_name
FROM employees e,departments d
WHERE e.`department_id` = d.department_id;  #Only 106 records

Outer join: merge the rows of more than two tables with the same column. In addition to the rows matching one table with another, the result set also finds the unmatched rows in the left table or right table.
Classification of external connection: left external connection, right external connection and full external connection
Left outer join: two tables return rows in the left table that do not meet the join conditions in addition to the rows that meet the join conditions. This kind of join is called left outer join.
Right outer join: in addition to the rows that meet the join conditions, two tables also return the rows that do not meet the conditions in the right table. This kind of connection is called right outer join.

Summary:

Inner join: merge rows of more than two tables with the same column. The result set does not contain rows that do not match one table with another.
In addition to the left or right rows in the table connection, it is also called the return of two rows that do not meet the left or right conditions in the table connection. When there is no matching row, the corresponding column in the result table is empty (NULL).
If it is a left outer connection, the left table in the connection condition is also called the master table, and the right table is called the slave table.
If it is a right outer connection, the right table in the connection condition is also called the master table, and the left table is called the slave table.

Topics: MySQL SQL

Programmer Think