Mysql multiple table data records query detailed _mysql

In the practical application, often need to implement in a query statement to display more than one table of data, this is called a multiple table data record connection query, referred to the next year will be the query.

When you implement a connection query, you first connect two or more tables to a certain condition, and then query to the required data records. The connection query is divided into inner join query and outer join query.

In the specific application, if the need to implement a multiple table data record query, the general use of the connection query, because the operation is relatively low efficiency. MySQL then provides an alternative to connection queries, subquery operations.
1. Relational Data operations:
in a connection query, you first need to connect two or more tables. A connection operation is a relational operation that is dedicated to data operations in relational data operations.

1.1 and (UNION):
There is a relational data operation in the SQL language called and manipulated. "and" is to combine tables with the same number of fields and field types. Combine data records from two of tables by and by operation. The number of fields that are merged is the number of fields in table 1 or the number of fields in table 2; The total number of records combined is: the number of records in table 1 + the number of records in Table 2-table 1 and Table 2 Duplicate records.

1.2 Cartesian product (Cartesian product):
There is a relational data operation in the SQL language called the Cartesian product operation. Descartes is an arbitrary combination of data in a table without a connection condition. The number of fields in the Cartesian product is the number of fields in table 1 + the number of fields in table 2. The number of records for Cartesian product is: The number of records in table 1 * Table 2.

1.3 INNER JOIN (INNER join):
For ease of operation, a specific operation-connection (join) is provided for database operations. The so-called connection is in the Cartesian product data record of the table relation, and chooses to generate a new relation according to the comparison condition of the corresponding field value. The connection is divided into inner joins (INNER join), outer joins (OUTER join), and Cross joins (CROSS join).
In the Cartesian product data of the table relationship, the so-called inner join is to keep all the matching data records in the table relationship and discard the mismatched data records. According to the matching conditions can be divided into natural connections, equivalent connections and unequal connections.
1.3.1 Natural Connection (NATURAL join):
The natural connection is in the Cartesian product of the table relationship, first automatically record the match based on the field of the same name in the table relationship, and then remove the duplicate fields.
After a natural connection, the number of fields in the new relationship is: Table 1 field count + Table 2 fields-table 1 and table 2 for the number of duplicate fields. The number of records for a new relationship after a natural connection is: records in table 1 * Number of records in Table 2-table 1 and table 2 the values of the same field are not equal in number of records.
The natural connection features the following:
1. In the actual execution of the natural connection, the fields of the same name are automatically judged, and then the data values are matched.
2. In a new relationship that finishes a natural connection, although you can specify which fields to include, you cannot specify the matching criteria in the execution, that is, which fields have their values matched.
3. In a new relationship that performs a natural connection, there is only one field name that is matched during execution, which removes the repeating field.
1.3.2 Equivalent Connection:
The equivalent connection operation is the Cartesian product of the table relationship, and the data records of the matching field values are selected.
After the equivalent connection, the number of fields in the new relationship is: Table 1 field number + Table 2 field count. The number of records for a new relationship after an equivalent connection is: Records in table 1 * Number of records in Table 2-table 1 and table 2 the values of the same field are not equal in number of records.
Compared to a natural connection, an equivalent join operation needs to specify a matching criterion with "=" in the execution process, and no duplicate fields are removed from the new relationship.
1.3.3 Unequal Connection:
The unequal join operation is the Cartesian product of the table relationship, and the data records of the matching field values are selected.
After an unequal connection, the number of fields in the new relationship is: Table 1 field number + Table 2 field count. The number of records for a new relationship after an equivalent connection is: Record number of records in table 1 * Number of records in table 2-records with equal values for table 1 and table 2 for the same field.
In contrast to a natural connection, an equivalent join operation needs to specify a matching condition with "!=" during execution and no duplicate fields are removed from the new relationship.

1.4 outer Joins (OUTER join):
    The so-called outer join (OUTER join), which is in the Cartesian product data record of the table relationship, not only preserves all the matching data records in the table relationship, Also, some mismatched data records are preserved. According to the retention and mismatch criteria, data record sources can be divided into: Left outer joins, right outer joins, all outer joins.
    1.4.1 Left outer join:
The so-called left outer join operation is the Cartesian product of the table relationship, in addition to selecting the matching data record and including the mismatched data records associated with the left table.
    after a left outer join, the number of fields in the new relationship is: Number of left table fields + Right table field. The number of records in the new relationship after the left outer join is: number of records in the left table * number of records in the right table-number of records with unequal values for the same field in the left table and right table + the number of unmatched records in the left table.
    1.4.2 Right outer join:
The so-called right outer join operation is the Cartesian product of the table relationship, in addition to selecting the matching data record and including the mismatched data records in the associated right table.
    after a right outer join, the number of fields in the new relationship is: Number of left table fields + Right table field. The number of records in the new relationship after the right outer join is the number of records in the left table * number of records in the right table-number of records with unequal values for the same fields in the left table and right tables + the number of records that did not match in the right table.
    1.4.3 Full outer joins:
The so-called right outer join operation is the Cartesian product of the table relationship, in addition to selecting the matching data records and including data records that do not match the two sides of the table.
    after a full outer join, the number of fields in the new relationship is: Number of left table fields + Right table field. The number of records for a new relationship after an all-outer join is: number of records on the left table * number of records in the right table-number of records with unequal values for the same field in the left table and right table + record number of unmatched records in the left table + the number of unmatched records in the right table.

2. Internal connection query:
There are two kinds of syntax for implementing a connection query in MySQL:
1. Use commas to differentiate multiple tables in the FROM clause, and implement matching criteria through logical expressions in the WHERE clause to implement a table connection.
2. ANSI connection syntax, which uses the "join on" keyword in the FROM clause, while the join condition is written in the keyword on clause. It is recommended that the second approach be used.
According to the matching condition, the inner join query can be divided into two kinds: equivalent connection; unequal connection.

The INNER JOIN query syntax is:

select field1, field2 ... fieldn
  from join_tablename1 inner join join_tablename2 [inner join join_tablename]
    on join_condition
// The parameter filedn indicates the name of the field to be queried, which is derived from the joined tables join_tablename1 and join_tablename2, the inner join table is used for the inner join, and the join_condition represents the matching condition.

2.1 Self-connection:
    There is a special kind of equivalent join in inner join query—self join. The so-called self-join refers to the connection between the table and itself.

Example (query each employee's name, position, and leader name):
mysql> select e.ename, e.job, l.ename from t_employee e inner join t_employee l on e.MGR = l.empno;
+ --------- + ---------- + ------- +
| ename | job | ename |
+ --------- + ---------- + ------- +
SCOTT | ANALYST | JONES |
| FORD | ANALYST | JONES |
| ALLEN | SALESMAN | BLAKE |
| MARD | SALESMAN | BLAKE |
| MARRTIN | SALESMAN | BLAKE |
| TURNER | SALESMAN | BLAKE |
| JAMES | CLEAR | BLAKE |
| MILLER | CLEAR | CLARK |
| ADAMS | CLEAR | SCOTT |
| JONES | MANAGER | KING |
| BLAKE | MANAGER | KING |
| CLARK | MANAGER | KING |
| SMITH | CLEAR | FORD |
+ --------- + ---------- + ------- +
13 rows in set (0.00 sec)
2.2 Equivalent connection:
    The equivalent join in the inner join query is to achieve the equivalent condition by using the equal relation operator "=" in the matching condition after the keyword on.

Example:
mysql> select e.empno, e.ename, e.job, d.dname, d.loc from t_employee e inner join t_dept d on e.deptno = d.deptno;
+ ------- + --------- + ----------- + ------------ + ------ ---- +
| empno | ename | job | dname | loc |
+ ------- + --------- + ----------- + ------------ + ------ ---- +
| 7788 | SCOTT | ANALYST | ACCOUNTING | NEW YORK |
7839 | KING | PRESIDENT | ACCOUNTING | NEW YORK |
| 7934 | MILLER | CLEAR | ACCOUNTING | NEW YORK |
| 7369 | SMITH | CLEAR | RESEARCH | DALLAS |
7499 | ALLEN | SALESMAN | RESEARCH | DALLAS |
7566 | JONES | MANAGER | RESEARCH | DALLAS |
7782 | CLARK | MANAGER | RESEARCH | DALLAS |
7876 | ADAMS | CLEAR | RESEARCH | DALLAS |
7902 | FORD | ANALYST | RESEARCH | DALLAS |
| 7521 | MARD | SALESMAN | SALES | CHICAGO |
| 7654 | MARRTIN | SALESMAN | SALES | CHICAGO |
| 7698 | BLAKE | MANAGER | SALES | CHICAGO |
7844 | TURNER | SALESMAN | SALES | CHICAGO |
| 7900 | JAMES | CLEAR | SALES | CHICAGO |
+ ------- + --------- + ----------- + ------------ + ------ ---- +
14 rows in set (0.00 sec)
2.3 Unequal connections:
    The inequality join in the inner join query is to implement the inequality condition in the matching condition after the keyword on in addition to the equality relation operator. The relational operators that can be used include>> = <<=! =

Example:
mysql> select e.ename employeename, e.job, l.ename loadername from t_employee e inner join t_employee l on e.mgr = l.empno
and e.empno> l.empno;
+ -------------- + ---------- + ------------ +
| employeename | job | loadername |
+ -------------- + ---------- + ------------ +
SCOTT | ANALYST | JONES |
| FORD | ANALYST | JONES |
| TURNER | SALESMAN | BLAKE |
| JAMES | CLEAR | BLAKE |
| MILLER | CLEAR | CLARK |
| ADAMS | CLEAR | SCOTT |
+ -------------- + ---------- + ------------ +
6 rows in set (0.00 sec)
3. Outer join query:
    An outer join query returns all data for at least one of the tables being manipulated. There are three types of outer joins: left outer join, right outer join, full outer join

The syntax is:
select field1, field2, ... fieldn
  from join_tablename1 left | rigth | full [outer] join join_tablename2
  on join_condition
3.1 Left outer connection:
    The left outer join in the outer join query refers to the table on the left of the keyword left join when the matching conditions are performed in the new relationship.

Example:
mysql> select e.ename employeename, e.job job, l.ename leadername from t_employee e left join t_employee l on e.mgr = l.empno;
+ -------------- + ----------- + ------------ +
| employeename | job | leadername |
+ -------------- + ----------- + ------------ +
| SMITH | CLEAR | FORD |
| ALLEN | SALESMAN | BLAKE |
| MARD | SALESMAN | BLAKE |
| JONES | MANAGER | KING |
| MARRTIN | SALESMAN | BLAKE |
| BLAKE | MANAGER | KING |
| CLARK | MANAGER | KING |
SCOTT | ANALYST | JONES |
| KING | PRESIDENT | NULL |
| TURNER | SALESMAN | BLAKE |
| ADAMS | CLEAR | SCOTT |
| JAMES | CLEAR | BLAKE |
| FORD | ANALYST | JONES |
| MILLER | CLEAR | CLARK |
+ -------------- + ----------- + ------------ +
14 rows in set (0.00 sec)
3.2 Right outer connection:
    The right outer join in the outer join query refers to the table on the right side of the keyword right join when the matching condition is performed in the new relationship.
4. Merge query data records:
    The keyword UNION is used to implement and operate in MySQL, that is, the query results of multiple select statements can be combined to form a new relationship.
    Merge operation of the keyword union
       The keyword union will directly merge the query result set together, and will remove duplicate data records.
    2. The merge operation of the keyword union all
       The keyword union all will directly merge the query result set together.

The syntax is:
select field1, field2, ... fieldn
  from tablename1
union | union all
select field1, field2, ... fieldn
  from tablename2
union | union all
select field1, field2, ... fieldn
  from tablename3
...
5. Sub-query:
    Although it is possible to implement multi-table query data records through join queries in MySQL, it is not recommended. This is because the performance of the join query is poor. As a result, a subquery instead of a join query appears. It is recommended to use subqueries to implement multi-table query data records.
5.1 Why use a subquery:
    In daily development, you are often exposed to querying multi-table data record operations, such as querying data records of department table t_dept and employee table t_employee table. For novices, directly use select * from t_dept t, t_employee e where t.deptno = e.deptno; when this SQL statement is executed, it first performs a Cartesian product operation on the two tables, and then selects data that meets the matching conditions recording. If the data size of the two tables is large, it will cause a crash when performing a Cartesian product operation. Experienced developers usually first use statistical functions to view the number of data records after the Cartesian product of the operation table, and then perform multi-table queries. Therefore, multi-table queries generally go through the following steps:
    1. Query the number of records of the Cartesian product of the associated table through the statistical function count (1). Then perform a multi-table query.
    2. If the number of data records queried by mysql is acceptable, then perform multi-table query, otherwise you should consider other ways to achieve.
    If the Cartesian product data is much larger than the acceptable range of mysql software, in order to solve multi-table queries, mysql provides sub-queries to implement multi-table queries.
    The so-called subquery means that several other queries are nested in one query, that is, the where or from clause of a select query contains another select query. In a query statement, the outer select query statement is called the main query, and the select query statement in the where clause is called a subquery, also called a nested query.
    Sub-queries can be used to implement multi-table queries. The query may contain keywords such as in, any, all, exists, etc. It may also include comparison operators. In theory, subqueries can appear anywhere in the query, but in actual development, subqueries often appear in where or from clauses.
    Subqueries in the where clause The subquery at this position generally returns a single row and single column, multiple rows and multiple columns, and a single row and multiple columns of data records.
    The subquery in the from clause. The subquery at this position generally returns multiple rows and multiple columns of data records, which can be used as a temporary table.

5.2 The results returned are single-row single-column and single-row multi-column subqueries:
When the result of a subquery is a single-row egg data record, the subquery statement is usually in the where clause of the main query statement, and usually contains comparison operators (> <=! =, Etc.)
    5.2.1 Single row and single column subquery:

Example (all employees with higher salary than Smith):
mysql> select * from t_employee where sal> (select sal from t_employee where ename = 'smith');
+ ------- + --------- + ----------- + ------ + ------------ + --------- + --------- + -------- +
empno | ename | job | MGR | Hiredate | sal | comm | deptno |
+ ------- + --------- + ----------- + ------ + ------------ + --------- + --------- + -------- +
| 7499 | ALLEN | SALESMAN | 7698 | 1982-03-12 | 1600.00 | 300.00 | 20 |
| 7521 | MARD | SALESMAN | 7698 | 1983-03-12 | 1250.00 | 500.00 | 30 |
7566 | JONES | MANAGER | 7839 | 1981-03-12 | 2975.00 | NULL | 20 |
| 7654 | MARRTIN | SALESMAN | 7698 | 1981-03-12 | 2850.00 | 1400.00 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-03-12 | 2850.00 | NULL | 30 |
7782 | CLARK | MANAGER | 7839 | 1985-03-12 | 2450.00 | NULL | 20 |
7788 | SCOTT | ANALYST | 7566 | 1981-03-12 | 3000.00 | NULL | 10 |
7839 | KING | PRESIDENT | NULL | 1981-03-12 | 5000.00 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1989-03-12 | 1500.00 | 0.00 | 30 |
7876 | ADAMS | CLEAR | 7788 | 1998-03-12 | 1100.00 | NULL | 20 |
7900 | JAMES | CLEAR | 7698 | 1987-03-12 | 950.00 | NULL | 30 |
7902 | FORD | ANALYST | 7566 | 0000-00-00 | 3000.00 | NULL | 20 |
7934 | MILLER | CLEAR | 7782 | 1981-03-12 | 1300.00 | NULL | 10 |
+ ------- + --------- + ----------- + ------ + ------------ + --------- + --------- + -------- +
13 rows in set (0.00 sec)
    5.2.2 Single-row multi-column subquery:

    In addition to returning data records with a single row and a single column, the subquery in the where clause can also return data records with multiple rows and columns, but such subqueries rarely occur.

Example (all employees with the same salary and position as Smith):
mysql> select ename, job, sal from t_employee where (sal, job) = (select sal, job from t_employee where ename = 'smith');
+ ------- + ------- + -------- +
| ename | job | sal |
+ ------- + ------- + -------- +
| SMITH | CLEAR | 800.00 |
+ ------- + ------- + -------- +
1 row in set (0.00 sec)
5.3 The result is a multi-row single-column subquery:
    When the result of a subquery is a multi-row single-column data record, the subquery statement generally appears in the where clause of the main query statement, and usually contains keywords such as IN ANY ALL EXISTS.
    5.3.1 Subqueries with the keyword in:
        When the condition of the main query is in the query result of the sub-query, it can be judged by the keyword in. On the contrary, if you want to realize that the conditions of the main query are not in the query results of the subquery, you can use the keyword not in to determine.

Example:
mysql> select * from t_employee where deptno in (select deptno from t_dept);
+ ------- + --------- + ----------- + ------ + ------------ + --------- + --------- + -------- +
empno | ename | job | MGR | Hiredate | sal | comm | deptno |
+ ------- + --------- + ----------- + ------ + ------------ + --------- + --------- + -------- +
7369 | SMITH | CLEAR | 7902 | 1981-03-12 | 800.00 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1982-03-12 | 1600.00 | 300.00 | 20 |
| 7521 | MARD | SALESMAN | 7698 | 1983-03-12 | 1250.00 | 500.00 | 30 |
7566 | JONES | MANAGER | 7839 | 1981-03-12 | 2975.00 | NULL | 20 |
| 7654 | MARRTIN | SALESMAN | 7698 | 1981-03-12 | 2850.00 | 1400.00 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-03-12 | 2850.00 | NULL | 30 |
7782 | CLARK | MANAGER | 7839 | 1985-03-12 | 2450.00 | NULL | 20 |
7788 | SCOTT | ANALYST | 7566 | 1981-03-12 | 3000.00 | NULL | 10 |
7839 | KING | PRESIDENT | NULL | 1981-03-12 | 5000.00 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1989-03-12 | 1500.00 | 0.00 | 30 |
7876 | ADAMS | CLEAR | 7788 | 1998-03-12 | 1100.00 | NULL | 20 |
7900 | JAMES | CLEAR | 7698 | 1987-03-12 | 950.00 | NULL | 30 |
7902 | FORD | ANALYST | 7566 | 0000-00-00 | 3000.00 | NULL | 20 |
7934 | MILLER | CLEAR | 7782 | 1981-03-12 | 1300.00 | NULL | 10 |
+ ------- + --------- + ----------- + ------ + ------------ + --------- + --------- + -------- +
14 rows in set (0.00 sec)
    5.3.2 Subqueries with the keyword any:
        Keyword any: The condition of the main query is to satisfy any data record in the query result of the subquery. There are three ways to match this keyword;
1. = any: its function is the same as the keyword in
2.> any (> = any): As long as it is greater than (greater than or equal to) the smallest one in the subquery.
3. <any (<= any): As long as it is less than (less than or equal to) the largest one in the subquery.

Example (Query the salary of the employee is not lower than the salary of the manager):
mysql> select ename, sal from t_employee where sal> any (select sal from t_employee where job = 'manager');
+ --------- + --------- +
| ename | sal |
+ --------- + --------- +
| JONES | 2975.00 |
| MARRTIN | 2850.00 |
| BLAKE | 2850.00 |
| SCOTT | 3000.00 |
| KING | 5000.00 |
| FORD | 3000.00 |
+ --------- + --------- +
6 rows in set (0.00 sec)
    5.3.3 Subqueries with the keyword all:
    The keyword all is used to indicate that the condition of the main query is to satisfy all data records in the query result returned by the subquery. There are two matching methods:
    1.> all (> = all): data records that are larger (greater than or equal to) than the largest in the subquery result;
    2. <all (<= all): data records that are smaller (less than or equal to) than the smallest of the subquery results.

Example:
mysql> select ename, sal from t_employee where sal> all (select sal from t_employee where job = 'manager');
+ ------- + --------- +
| ename | sal |
+ ------- + --------- +
| SCOTT | 3000.00 |
| KING | 5000.00 |
| FORD | 3000.00 |
+ ------- + --------- +
3 rows in set (0.00 sec)
    5.3.4 Subquery with keyword exists:
    The keyword exists is a boolean type, which is true when the result set can be returned, and false when the result set cannot be returned. When querying, the external table is traversed one by one. Each query will compare the conditional statement of exists. When the conditional statement in exists returns a record row, the condition is true, and the current traversed record is returned; otherwise, if exists If the condition statement cannot return the record row, the currently traversed record is discarded.
  5.4 The result is a multi-row, multi-column subquery:
    When the result of a subquery is a multi-row, multi-column data record, the subquery statement is generally treated as a temporary table in the from clause of the main query statement.

Example (Query the department number, department name, department address, number of employees, and average salary of each department in the employee table):
Achieved through internal connections:
mysql> select d.deptno, d.dname, d.loc, count (e.empno) number, avg (e.sal) average from t_employee e inner join t_dept d on e
.deptno = d.deptno group by d.deptno;
+ -------- + ------------ + ---------- + -------- + ------- ------ +
| deptno | dname | loc | number | average |
+ -------- + ------------ + ---------- + -------- + ------- ------ +
| 10 | ACCOUNTING | NEW YORK | 3 | 3100.000000 |
| 20 | RESEARCH | DALLAS | 6 | 1987.500000 |
| 30 | SALES | CHICAGO | 5 | 1880.000000 |
+ -------- + ------------ + ---------- + -------- + ------- ------ +
3 rows in set (0.00 sec)
To achieve through subqueries:
mysql> select d.deptno, d.dname, d.loc, number, average from t_dept d inner join (select deptno dno, count (empno) number, avg (s
al) average from t_employee group by deptno) employee on d.deptno = employee.dno;
+ -------- + ------------ + ---------- + -------- + ------- ------ +
| deptno | dname | loc | number | average |
+ -------- + ------------ + ---------- + -------- + ------- ------ +
| 10 | ACCOUNTING | NEW YORK | 3 | 3100.000000 |
| 20 | RESEARCH | DALLAS | 6 | 1987.500000 |
| 30 | SALES | CHICAGO | 5 | 1880.000000 |
+ -------- + ------------ + ---------- + -------- + ------- ------ +
3 rows in set (0.00 sec)
The above is the entire content of this article. I hope it will be helpful to everyone's learning. I also hope everyone supports the Yunqi community.