multi-table query

• Multi table query, also known as association query, refers to two or more tables completing the query operation together.
• Prerequisite: these tables queried together are related (one to one, one to many). There must be an associated field between them. This associated field may or may not have a foreign key. For example: employee table and department table, which are related by "department number".

1. Multi table connection caused by one case

1.1 case description

• Which city does the person named 'Abel' work in?

SELECT * 
FROM employees
WHERE last_name = 'Abel';

SELECT *
FROM departments
WHERE department_id = 80;


SELECT *
FROM locations 
WHERE location_id = 2500;

• Get data from multiple tables:
  
• Case: query employee's name and department name SELECT last_name, department_name FROM employees, departments;

• Query results:

+-----------+----------------------+
| last_name | department_name      |
+-----------+----------------------+
| King      | Administration       |
| King      | Marketing            |
| King      | Purchasing           |
| King      | Human Resources      |
| King      | Shipping             |
| King      | IT                   |
| King      | Public Relations     |
| King      | Sales                |
| King      | Executive            |
| King      | Finance              |
| King      | Accounting           |
| King      | Treasury             |
...
| Gietz     | IT Support           |
| Gietz     | NOC                  |
| Gietz     | IT Helpdesk          |
| Gietz     | Government Sales     |
| Gietz     | Retail Sales         |
| Gietz     | Recruiting           |
| Gietz     | Payroll              |
+-----------+----------------------+
2889 rows in set (0.01 sec)

• Analysis error: SELECT COUNT(employee_id) FROM employees; #Output 107 lines SELECT COUNT(department_id)FROM departments; #Output 27 lines SELECT 107*27 FROM dual;

We call the problem in the above multi table query as the error of Cartesian product.

• Cartesian product error occurs. The reason for the error: the connection condition of multiple tables is missing

#Wrong implementation: each employee matches each department.
SELECT employee_id,department_name
FROM employees,departments;  #2889 records were found

#Wrong way
SELECT employee_id,department_name
FROM employees CROSS JOIN departments;#2889 records were found

1.2 understanding of Cartesian product (or cross connection)

• Cartesian product is a mathematical operation. Suppose there are two sets X and y, then the Cartesian product of X and Y is all possible combinations of X and y, that is, the first object comes from X and the second object comes from all possible combinations of Y. The number of combinations is the product of the number of elements in the two sets.

• In SQL92, Cartesian product is also called CROSS JOIN, which is CROSS JOIN in English. In SQL99, CROSS JOIN is also used to represent cross connection. Its function is to connect any table, even if the two tables are not related. In MySQL, Cartesian product will appear in the following cases:
• Query employee name and department name SELECT last_name,department_name FROM employees,departments; SELECT last_name,department_name FROM employees CROSS JOIN departments; SELECT last_name,department_name FROM employees INNER JOIN departments; SELECT last_name,department_name FROM employees JOIN departments;

1.3 case analysis and problem solving

• The error of Cartesian product will occur under the following conditions:
• Omit the join condition (or association condition) of multiple tables
• The connection condition (or association condition) is invalid
• All rows in all tables are connected to each other
• In order to avoid Cartesian product, effective connection conditions can be added in WHERE.
• After adding connection conditions, query syntax:

  SELECT	table1.column, table2.column
  FROM	table1, table2
  WHERE	table1.column1 = table2.column2;  #Connection conditions

• Write the join condition in the WHERE clause.
• Correct writing:

Case: query employee's name and department name

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

• When there are the same columns in the table, prefix the column name with the table name.
• If a field exists in more than one table in the query statement, you must indicate the table in which this field is located.

SELECT employees.employee_id,departments.department_name,employees.department_id
FROM employees,departments
WHERE employees.`department_id = departments.department_id;

• Suggestion: from the perspective of sql optimization, it is recommended to indicate the table in front of each field when querying multiple tables.
• You can alias the table and use the alias of the table in SELECT and WHERE.

SELECT emp.employee_id,dept.department_name,emp.department_id
FROM employees emp,departments dept
WHERE emp.department_id = dept.department_id;

• If an alias is given to a table, once the table name is used in SELECT or WHERE, the alias of the table must be used instead of the original name of the table.

#The following operation is wrong:
SELECT emp.employee_id,departments.department_name,emp.department_id
FROM employees emp,departments dept
WHERE emp.department_id = departments.department_id;

• Conclusion: if there are n tables to realize multi table query, at least n-1 connection conditions are required
• Exercise: query employees of employees_ id,last_ name,department_ name,city

SELECT e.employee_id,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;