MySQL Study-MySQL table connection

MySQL Study-MySQL table connection
MySQL Study-MySQL table connection 1. join syntax overview join is used to associate fields in multiple tables. Syntax :... FROM table1 INNER | LEFT | right join table2 ON conditiontable1: LEFT table; table2: RIGHT table. JOIN is divided into the following categories by function: inner join (internal JOIN, or equivalent JOIN): Records of the two tables with connection matching relationships are obtained. Left join: The full record of the LEFT table (table1) is obtained, that is, the right table (table2) does not have a matched record. Right join (right join): opposite to left join, the full record of the RIGHT table (table2) is obtained, that is, the LEFT table (table1) does not match the corresponding record. Note: mysql does not support Full join, but you can use the UNION keyword to merge left join and right join to simulate FULL join. Case Analysis: 1. CASE Environment

mysql> select * from emp;+-------+--------+-----------+------+------------+------+------+--------+| empno | ENAME  | JOB       | MGR  | HIRE       | SAL  | COMM | deptno |+-------+--------+-----------+------+------------+------+------+--------+|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800 | NULL |     20 ||  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600 |  300 |     30 ||  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250 |  500 |     30 ||  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975 | NULL |     20 ||  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250 | 1400 |     30 ||  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850 | NULL |     30 ||  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450 | NULL |     10 ||  7788 | SCOTT  | ANALYST   | 7566 | 1987-07-13 | 3000 | NULL |     20 ||  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL |     10 ||  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500 |    0 |     30 ||  7876 | ADAMS  | CLERK     | 7788 | 1987-06-13 | 1100 | NULL |     20 ||  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950 | NULL |     30 ||  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000 | NULL |     20 ||  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300 | NULL |     10 |+-------+--------+-----------+------+------------+------+------+--------+14 rows in set (0.00 sec)mysql> select * from dept;+--------+------------+---------+| deptNO | DNAME      | LOC     |+--------+------------+---------+|     10 | ACCOUNTING | NEWYORK ||     20 | RESEARCH   | DALLAS  ||     30 | SALES      | CHICAGO ||     40 | OPERATIONS | BOSTON  |+--------+------------+---------+4 rows in set (0.00 sec)

 

Inner join: (internal join, or equivalent join): Records of the two tables with connection matching relationships are obtained.

mysql> select e.empno,e.ename,e.sal,e.deptno,d.dname     ->   from emp e    ->   inner join dept d     ->    where e.deptno=d.deptno;+-------+--------+------+--------+------------+| empno | ename  | sal  | deptno | dname      |+-------+--------+------+--------+------------+|  7782 | CLARK  | 2450 |     10 | ACCOUNTING ||  7839 | KING   | 5000 |     10 | ACCOUNTING ||  7934 | MILLER | 1300 |     10 | ACCOUNTING ||  7369 | SMITH  |  800 |     20 | RESEARCH   ||  7566 | JONES  | 2975 |     20 | RESEARCH   ||  7788 | SCOTT  | 3000 |     20 | RESEARCH   ||  7876 | ADAMS  | 1100 |     20 | RESEARCH   ||  7902 | FORD   | 3000 |     20 | RESEARCH   ||  7499 | ALLEN  | 1600 |     30 | SALES      ||  7521 | WARD   | 1250 |     30 | SALES      ||  7654 | MARTIN | 1250 |     30 | SALES      ||  7698 | BLAKE  | 2850 |     30 | SALES      ||  7844 | TURNER | 1500 |     30 | SALES      ||  7900 | JAMES  |  950 |     30 | SALES      |+-------+--------+------+--------+------------+14 rows in set (0.00 sec)mysql> select e.empno,e.ename,e.sal,e.deptno,d.dname     ->   from emp e    ->   inner join dept d     ->    on  e.deptno=d.deptno;+-------+--------+------+--------+------------+| empno | ename  | sal  | deptno | dname      |+-------+--------+------+--------+------------+|  7782 | CLARK  | 2450 |     10 | ACCOUNTING ||  7839 | KING   | 5000 |     10 | ACCOUNTING ||  7934 | MILLER | 1300 |     10 | ACCOUNTING ||  7369 | SMITH  |  800 |     20 | RESEARCH   ||  7566 | JONES  | 2975 |     20 | RESEARCH   ||  7788 | SCOTT  | 3000 |     20 | RESEARCH   ||  7876 | ADAMS  | 1100 |     20 | RESEARCH   ||  7902 | FORD   | 3000 |     20 | RESEARCH   ||  7499 | ALLEN  | 1600 |     30 | SALES      ||  7521 | WARD   | 1250 |     30 | SALES      ||  7654 | MARTIN | 1250 |     30 | SALES      ||  7698 | BLAKE  | 2850 |     30 | SALES      ||  7844 | TURNER | 1500 |     30 | SALES      ||  7900 | JAMES  |  950 |     30 | SALES      |+-------+--------+------+--------+------------+14 rows in set (0.00 sec)

 

Implicit inner join:

mysql> select e.empno,e.ename,e.sal,e.deptno,d.dname     -> from emp e,dept d    ->  where e.deptno=d.deptno;+-------+--------+------+--------+------------+| empno | ename  | sal  | deptno | dname      |+-------+--------+------+--------+------------+|  7782 | CLARK  | 2450 |     10 | ACCOUNTING ||  7839 | KING   | 5000 |     10 | ACCOUNTING ||  7934 | MILLER | 1300 |     10 | ACCOUNTING ||  7369 | SMITH  |  800 |     20 | RESEARCH   ||  7566 | JONES  | 2975 |     20 | RESEARCH   ||  7788 | SCOTT  | 3000 |     20 | RESEARCH   ||  7876 | ADAMS  | 1100 |     20 | RESEARCH   ||  7902 | FORD   | 3000 |     20 | RESEARCH   ||  7499 | ALLEN  | 1600 |     30 | SALES      ||  7521 | WARD   | 1250 |     30 | SALES      ||  7654 | MARTIN | 1250 |     30 | SALES      ||  7698 | BLAKE  | 2850 |     30 | SALES      ||  7844 | TURNER | 1500 |     30 | SALES      ||  7900 | JAMES  |  950 |     30 | SALES      |+-------+--------+------+--------+------------+14 rows in set (0.00 sec)

 

Left join: obtain the full records of the left table (table1). That is, the right table (table2) does not have matching records.

mysql> update emp set deptno=null where empno=7788;Query OK, 1 row affected (0.07 sec)Rows matched: 1  Changed: 1  Warnings: 0mysql> commit;Query OK, 0 rows affected (0.00 sec)mysql> select * from emp where empno=7788;+-------+-------+---------+------+------------+------+------+--------+| empno | ENAME | JOB     | MGR  | HIRE       | SAL  | COMM | deptno |+-------+-------+---------+------+------------+------+------+--------+|  7788 | SCOTT | ANALYST | 7566 | 1987-07-13 | 3000 | NULL |   NULL |+-------+-------+---------+------+------------+------+------+--------+1 row in set (0.00 sec)

 

Equivalent connections:

mysql> select e.empno,e.ename,e.sal,e.deptno,d.dname    -> from emp e    -> inner join dept d on e.deptno=d.deptno;+-------+--------+------+--------+------------+| empno | ename  | sal  | deptno | dname      |+-------+--------+------+--------+------------+|  7782 | CLARK  | 2450 |     10 | ACCOUNTING ||  7839 | KING   | 5000 |     10 | ACCOUNTING ||  7934 | MILLER | 1300 |     10 | ACCOUNTING ||  7369 | SMITH  |  800 |     20 | RESEARCH   ||  7566 | JONES  | 2975 |     20 | RESEARCH   ||  7876 | ADAMS  | 1100 |     20 | RESEARCH   ||  7902 | FORD   | 3000 |     20 | RESEARCH   ||  7499 | ALLEN  | 1600 |     30 | SALES      ||  7521 | WARD   | 1250 |     30 | SALES      ||  7654 | MARTIN | 1250 |     30 | SALES      ||  7698 | BLAKE  | 2850 |     30 | SALES      ||  7844 | TURNER | 1500 |     30 | SALES      ||  7900 | JAMES  |  950 |     30 | SALES      |+-------+--------+------+--------+------------+13 rows in set (0.00 sec)

 

----- For equijoin, only matching records can be seen!

mysql> select e.empno,e.ename,e.sal,e.deptno,d.dname    -> from emp e    -> left join dept d on e.deptno=d.deptno;+-------+--------+------+--------+------------+| empno | ename  | sal  | deptno | dname      |+-------+--------+------+--------+------------+|  7782 | CLARK  | 2450 |     10 | ACCOUNTING ||  7839 | KING   | 5000 |     10 | ACCOUNTING ||  7934 | MILLER | 1300 |     10 | ACCOUNTING ||  7369 | SMITH  |  800 |     20 | RESEARCH   ||  7566 | JONES  | 2975 |     20 | RESEARCH   ||  7876 | ADAMS  | 1100 |     20 | RESEARCH   ||  7902 | FORD   | 3000 |     20 | RESEARCH   ||  7499 | ALLEN  | 1600 |     30 | SALES      ||  7521 | WARD   | 1250 |     30 | SALES      ||  7654 | MARTIN | 1250 |     30 | SALES      ||  7698 | BLAKE  | 2850 |     30 | SALES      ||  7844 | TURNER | 1500 |     30 | SALES      ||  7900 | JAMES  |  950 |     30 | SALES      ||  7788 | SCOTT  | 3000 |   NULL | NULL       |+-------+--------+------+--------+------------+14 rows in set (0.00 sec)

 

----- You can use left join to view non-conforming records in the emp table! Right join: opposite to left join, the full record of the right table (table2) is obtained, that is, the LEFT table (table1) does not match the corresponding record.

mysql> select e.empno,e.ename,e.sal,d.deptno,d.dname    -> from emp e    -> right join dept d on e.deptno=d.deptno;+-------+--------+------+--------+------------+| empno | ename  | sal  | deptno | dname      |+-------+--------+------+--------+------------+|  7782 | CLARK  | 2450 |     10 | ACCOUNTING ||  7839 | KING   | 5000 |     10 | ACCOUNTING ||  7934 | MILLER | 1300 |     10 | ACCOUNTING ||  7369 | SMITH  |  800 |     20 | RESEARCH   ||  7566 | JONES  | 2975 |     20 | RESEARCH   ||  7876 | ADAMS  | 1100 |     20 | RESEARCH   ||  7902 | FORD   | 3000 |     20 | RESEARCH   ||  7499 | ALLEN  | 1600 |     30 | SALES      ||  7521 | WARD   | 1250 |     30 | SALES      ||  7654 | MARTIN | 1250 |     30 | SALES      ||  7698 | BLAKE  | 2850 |     30 | SALES      ||  7844 | TURNER | 1500 |     30 | SALES      ||  7900 | JAMES  |  950 |     30 | SALES      ||  NULL | NULL   | NULL |     40 | OPERATIONS |+-------+--------+------+--------+------------+14 rows in set (0.00 sec)

 

------ The dept table is queried. Records that do not meet the conditions are displayed !!! Analysis of Mysql Join syntax and performance optimization before talking about MySQL Join syntax, I should review the Join syntax first. Oh, I forgot about it myself, let's take a look at it. Here I have a simple memory method. The difference between the internal and external connections is that the internal connections will remove all records that do not meet the conditions, while the external connections will keep some of them. The difference between outer left join and outer right join is that if A joins B on the left, all records in A are retained in the result. In this case, only records meeting the join condition in B are retained, on the contrary, the right join will not be confused. I. Join syntax overview join is used to associate fields in multiple tables. Syntax:... FROM table1 INNER | LEFT | RIGHT JOIN table2 ON conditionatable1: LEFT table; table2: RIGHT table. JOIN is divided into the following categories by function: inner join (internal JOIN, or equivalent JOIN): Records of the two tables with connection matching relationships are obtained. Left join: The full record of the LEFT table (table1) is obtained, that is, the right table (table2) does not have a matched record. Right join (right join): opposite to left join, the full record of the RIGHT table (table2) is obtained, that is, the LEFT table (table1) does not match the corresponding record. Note: mysql does not support Full join. However, you can use the UNION keyword to merge left join and right join to simulate FULL join. Next, a column sub-statement is provided to explain the following types. The following two tables (A, B)

mysql> select A.id,A.name,B.name from A,B where A.id=B.id;+----+-----------+-------------+| id | name       | name             |+----+-----------+-------------+|  1 | Pirate       | Rutabaga      ||  2 | Monkey    | Pirate            ||  3 | Ninja         | Darth Vader ||  4 | Spaghetti  | Ninja             |+----+-----------+-------------+4 rows in set (0.00 sec)

 

2. Inner join an inner join is also called an equivalent join. Inner join produces A set of data that conforms to both A and B.

mysql> select * from A inner join B on A.name = B.name;+----+--------+----+--------+| id | name   | id | name   |+----+--------+----+--------+|  1 | Pirate |  2 | Pirate ||  3 | Ninja  |  4 | Ninja  |+----+--------+----+--------+

 

3. Left join

Mysql> select * from A left join B on. name = B. name; # Or: select * from A left outer join B on. name = B. name; + ---- + ----------- + ------ + -------- + | id | name | + ---- + ----------- + ------ + -------- + | 1 | Pirate | 2 | Pirate | 2 | Monkey | NULL | 3 | Ninja | 4 | Ninja | 4 | Spaghetti | NULL | + ---- + ----------- + ------ + -------- + 4 rows in set (0.00 sec)

 

Left join, (or left outer join: in Mysql, the two are equivalent. We recommend that you use left join .) left join generates A complete set of records from the left table (A) and matched records (right table (B )). if no match exists, the right side will contain null. If you want to generate A set of records from the left table (A) but do not contain records from the right table (B, you can run the where statement as follows:

mysql> select * from A left join B on A.name=B.name where A.id is null or B.id is null;+----+-----------+------+------+| id | name      | id   | name |+----+-----------+------+------+|  2 | Monkey    | NULL | NULL ||  4 | Spaghetti | NULL | NULL |+----+-----------+------+------+2 rows in set (0.00 sec) 

 

Likewise, the inner join can be simulated as follows:

mysql> select * from A left join B on A.name=B.name where A.id is not null and B.id is not null;+----+--------+------+--------+| id | name   | id   | name   |+----+--------+------+--------+|  1 | Pirate |    2 | Pirate ||  3 | Ninja  |    4 | Ninja  |+----+--------+------+--------+2 rows in set (0.00 sec)

 

Result set: the result is as follows:

SELECT * from a left join B ON. name = B. nameWHERE B. id IS NULLunionSELECT * from a right join B ON. name = B. nameWHERE. id is null; # result + ------ + ----------- + | id | name | + ------ + ----------- + ------ + ------------- + | 2 | Monkey | NULL | 4 | Spaghetti | NULL | 1 | Rutabaga | NULL | 3 | Darth Vader | + ------ + ----------- +

 

Iv. Right join

mysql> select * from A right join B on A.name = B.name;+------+--------+----+-------------+| id   | name   | id | name        |+------+--------+----+-------------+| NULL | NULL   |  1 | Rutabaga    ||    1 | Pirate |  2 | Pirate      || NULL | NULL   |  3 | Darth Vader ||    3 | Ninja  |  4 | Ninja       |+------+--------+----+-------------+4 rows in set (0.00 sec)

 

Same as left join. V. Cross join cross join: the result of a Cross join is the product of two tables, I .e., the product of cartesian products (Descartes) is also called a straight product. Assume that the Set A = {a, B}, and set B = {0, 1}, the Cartesian product of the Two sets is {(a, 0), (a, 1 ), (a, 2), (B, 0), (B, 1), (B, 2 )}. It can be expanded to multiple sets. In A similar example, if A represents A set of students in A school and B represents A set of all courses in the school, cartesian products of A and B represent all possible course selections.

mysql> select * from A cross join B;+----+-----------+----+-------------+| id | name      | id | name        |+----+-----------+----+-------------+|  1 | Pirate    |  1 | Rutabaga    ||  2 | Monkey    |  1 | Rutabaga    ||  3 | Ninja     |  1 | Rutabaga    ||  4 | Spaghetti |  1 | Rutabaga    ||  1 | Pirate    |  2 | Pirate      ||  2 | Monkey    |  2 | Pirate      ||  3 | Ninja     |  2 | Pirate      ||  4 | Spaghetti |  2 | Pirate      ||  1 | Pirate    |  3 | Darth Vader ||  2 | Monkey    |  3 | Darth Vader ||  3 | Ninja     |  3 | Darth Vader ||  4 | Spaghetti |  3 | Darth Vader ||  1 | Pirate    |  4 | Ninja       ||  2 | Monkey    |  4 | Ninja       ||  3 | Ninja     |  4 | Ninja       ||  4 | Spaghetti |  4 | Ninja       |+----+-----------+----+-------------+16 rows in set (0.00 sec)

 

# Run mysql> select * from A inner join B; try again # execute mysql> select * from A cross join B on. name = B. name; in fact, in MySQL (only MySQL), cross join and inner join have the same performance. The results obtained without specifying the ON condition are cartesian products, otherwise, the system returns the exact matching results for the two tables. Inner join and cross join can omit the INNER or CROSS keyword, so the following SQL results are the same :... FROM table1 inner join table2... FROM table1 cross join table2... FROM table1 JOIN table2 6. full join

mysql> select * from A left join B on B.name = A.name     -> union     -> select * from A right join B on B.name = A.name;+------+-----------+------+-------------+| id   | name      | id   | name        |+------+-----------+------+-------------+|    1 | Pirate    |    2 | Pirate      ||    2 | Monkey    | NULL | NULL        ||    3 | Ninja     |    4 | Ninja       ||    4 | Spaghetti | NULL | NULL        || NULL | NULL      |    1 | Rutabaga    || NULL | NULL      |    3 | Darth Vader |+------+-----------+------+-------------+6 rows in set (0.00 sec)

 

All records generated by full join (matching records of both parties) are in tables A and B. If no match exists, the opposite side will contain null. 7. performance Optimization 1. show (explicit) inner join VS implicit (implicit) inner join for example: select * fromtable a inner join table bon. id = B. id; VSselect. *, B. * from table a, table bwhere. id = B. id; I have compared (10 W data) in the database. They are used almost the same. The first is the displayed inner join, and the last is the implicit inner join. 2. left join/right join VS inner join try to use inner join. avoid left join and NULL. when using left join (or right join), you should be aware of the following points: (1 ). the execution sequence of on and where is used to determine how to retrieve data rows from Table B. If no row of data in Table B matches the ON condition, all columns in the column of NULL will be generated, and the WHERE clause condition in the matching stage will not be used. The WHERE clause condition is used only after the matching stage is complete. It will retrieve and filter the data generated in the matching phase. Therefore, when using Left (right) join, we must first provide as many matching conditions as possible to reduce Where execution. For example:

PASSselect * from Ainner join B on B.name = A.nameleft join C on C.name = B.nameleft join D on D.id = C.idwhere C.status>1 and D.status=1;select * from Ainner join B on B.name = A.nameleft join C on C.name = B.name and C.status>1left join D on D.id = C.id and D.status=1

 

From the above example, we can see that the ON condition is met as much as possible, but the Where condition is not used. From the perspective of execution performance, the second one is obviously more time-saving. (2) Note the differences between the ON clause and the WHERE clause. For example, the author raises a column:

mysql> SELECT * FROM product LEFT JOIN product_details       ON (product.id = product_details.id)       AND product_details.id=2;+----+--------+------+--------+-------+| id | amount | id   | weight | exist |+----+--------+------+--------+-------+|  1 |    100 | NULL |   NULL |  NULL ||  2 |    200 |    2 |     22 |     0 ||  3 |    300 | NULL |   NULL |  NULL ||  4 |    400 | NULL |   NULL |  NULL |+----+--------+------+--------+-------+4 rows in set (0.00 sec)mysql> SELECT * FROM product LEFT JOIN product_details       ON (product.id = product_details.id)       WHERE product_details.id=2;+----+--------+----+--------+-------+| id | amount | id | weight | exist |+----+--------+----+--------+-------+|  2 |    200 |  2 |     22 |     0 |+----+--------+----+--------+-------+1 row in set (0.01 sec)

 

The first query uses the ON condition to retrieve all data rows from the product_details table of left join. The second query performs a simple left join operation, and then uses the WHERE clause to filter out non-conforming data rows from the left join data. (3) Avoid subqueries as much as possible, and join is often a performance item. More often, it is reflected in a large amount of data. At this time, we should avoid complicated subqueries. Insert into t1 (a1) select b1 from t2 where not exists (select 1 from t1 where t1.id = t2.r _ id); insert into t1 (a1) select b1 from t2 left join (select distinct t1.id from t1) t1 on t1.id = t2.r _ id where t1.id is null;