MySQL DML operation--------Multi-table union query combat

1. Background

* multi-table joint query is a way to record different tables together

* The Federated (join) plan in the SQL standard is roughly the same as the connection, outer connection, and full connection. The outer connection is divided into left outer connection and right outer connection.

2. Internal connection example (inner join) [employees--departments]

* look at the employee table [Employees] and department table [departments] structure

mysql> desc employees;+-----------+---------------+------+-----+---------+----------------+|  Field     | Type           | Null | Key | Default | Extra           |+-----------+---------------+------+-----+---------+----------------+| id         | bigint (    | NO   ) | pri | null    | auto_increment | |  name      | varchar (   | no   |)      | NULL    |                 | |  sex       | enum (' M ', ' F ')  | no   |     | null    |                 | |  age       | int (one)        |  NO   |     | NULL    |                 | |  depart_id | bigint (    | NO   |   )   | NULL    |                 |+-----------+---------------+------+-----+---------+----------------+5  rows in set  (0.00 sec) mysql> desc departments;+-------+-------------+-- ----+-----+---------+----------------+| field | type        | null | key | default | extra           |+-------+-------------+------+-----+---------+----------------+| id     | bigint (  | NO   | PRI | NULL  )   | auto_increment | |  name  | varchar ( | no   |     |)  NULL    |                 |+-------+-------------+------+-----+---------+----------------+2 rows in  set  (0.00 SEC)

* View employee table [Employees] and department table [departments] data

mysql> select * from employees;+----+-------+-----+-----+-----------+| id |  name  | sex | age | depart_id |+----+-------+-----+-----+------- ----+|  1 | tom   | m   |  25 |          1 | |   2 | jak   | F   |  35 |          2 | |   3 | lisea | M   |  22 |          3 |+----+-------+-----+-----+-----------+3 rows in set  (0.00 sec) mysql> select * from departments;+----+------+| id | name  |+----+------+|  1 | dev  | |   2 | test | |   3 | ops  |+----+------+3 rows in set  (0.00 sec) 

* Query and display all employee ID, name, surname, age, Department (method one)

mysql> select e.id id, e.name name, if ( e.sex =  ' M ',  ' male ',  ' female ')  sex, e.age age, d.name     -> from employees e, departments d    ->  where e.depart_id = d.id;+----+-------+--------+-----+------+| id | name   | sex    | age | name |+----+-------+--------+-----+------+ |  1 | tom   | male   |  25 | dev   | |   2 | jak   | female |  35 | test | |   3 | lisea | male   |  22 | ops  | +----+-------+--------+-----+------+3 rows in set  (0.03 sec) 

* Query and display all employee ID, name, surname, age, Department (method two) [method one is equivalent to method two]

INNER join can be saved as a join

mysql> select e.id id, e.name name, if ( e.sex =  ' M ',  ' male ',  ' female ')  sex, e.age age, d.name     -> from employees e inner join departments d     -> on e.depart_id = d.id;+----+-------+--------+-----+------+| id  | name  | sex    | age | name |+----+------- +--------+-----+------+|  1 | tom   | male   |   25 | dev  | |   2 | jak   | female |  35 | test | |   3 | lisea | male   |  22 | ops  | +----+-------+--------+-----+------+3 rows in set  (0.00 sec) 

3. Examples of external connections

Left outer connection [Based on left table, all the data of the left table, the right table has a combination.] The right table does not have null ]

Right Outer connection [Based on the right table, the right table has all the data, the left table has a combination.] Left table NOT null ]

* View the structure of table A and B

mysql> desc a;+-------+---------+------+-----+---------+-------+| field | type     | null | key | default | extra |+-------+----- ----+------+-----+---------+-------+| data  | int (one)  | YES  |      | null    |       |+------ -+---------+------+-----+---------+-------+1 row in set  (0.00 sec) Mysql> desc  b;+-------+---------+------+-----+---------+-------+| field | type     | null | key | default | extra |+-------+---------+------+-----+ ---------+-------+| data  | int (one)  | YES  |      | null    |       |+-------+---------+------ +-----+---------+-------+1 row in set  (0.00 SEC) 

* View Table A and B data

Mysql> SELECT * FROM a;+------+|    Data |+------+|    1 | |    2 | | 4 |+------+3 rows in Set (0.00 sec) mysql> SELECT * FROM b;+------+|    Data |+------+|    2 | |    4 | |    5 | | 6 |+------+4 rows in Set (0.01 sec)

　

* LEFT OUTER join query (right join) is based on a table, showing all the data of a, table B has a combination, does not show nulll

the left outer join can be written as a LEFT join

Mysql> SELECT * from a LEFT outer join B on a.data = b.data;+------+------+| Data |    Data |+------+------+|    2 |    2 | |    4 |    4 | | 1 | NULL |+------+------+3 rows in Set (0.00 sec)

* The right outer connection query is based on B table, showing all the data of table B, the combination of a table, does not show nulll

right outer join can be written as right join

Mysql> SELECT * from a right outer join B on a.data = b.data;+------+------+| Data |    Data |+------+------+|    2 |    2 | |    4 | 4 | |    NULL | 5 | |    NULL | 6 |+------+------+4 rows in Set (0.00 sec)

* Full (cross) connection query

A cross join without a WHERE clause will produce a Cartesian product of the table involved in the join.

The number of rows in the first table multiplied by the number of rows in the second table equals the size of the Cartesian product result set.

( A and B cross-joins generate 3*4=12 Records)  

mysql> select * from a corss join b;+------+------+| data |  data |+------+------+|    1 |    2 | |     2 |    2 | |     4 |    2 | |     1 |    4 | |     2 |    4 | |     4 |    4 | |     1 |    5 | |     2 |    5 | |     4 |    5 | |     1 |    6 | |     2 |    6 | |     4 |    6 |+------+------+12 rows in set   (0.00 SEC)

4. Summary

To demand-driven technology, the technology itself does not have a better point, only the division of business.

This article is from the "Sea" blog, be sure to keep this source http://lisea.blog.51cto.com/5491873/1943732

MySQL DML operation--------Multi-table union query combat