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.
- 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.