If you want to read other articles about MySQL database, please jump to MySQL Self-taught Catalogue

1. connection

1. Relation table
   In our daily life, it is possible to design a table to include all the information in the table, but it is very complicated. At this point, a relational table appears.
   Relational tables ensure that information is decomposed into multiple tables, one table for a class of data. Tables are correlated by some commonly used values, i.e. relationships in relational design. Relational tables have better scalability.

2. coupling
   Why use links?
   As mentioned above, decomposing data into multiple tables can be stored more efficiently, processed more conveniently, and has greater scalability. But these benefits come at a cost. What do we do when we want to retrieve data from multiple tables?
   At this point, you have to use the join. Simply put, join is a mechanism used to associate tables in a select statement, so it is called join.

   Before I talk about the three ways of connection, I would like to ask what Descartes product is.
   Cartesian product refers to the Cartesian product of two sets X and Y in mathematics. Assuming that set A={a, b}, set B={0, 1, 2}, the Cartesian product of the two sets is {(a, 0), (a, 1), (a, 2), (b, 0), (b, 1), (b, 2)}.
   In database, Cartesian product refers to the Cartesian product returned by table relations without join conditions. Expressed as: table1,table2
   Raise a chestnut.

mysql> select * from promary;
+-------+-----------+
| proID | proName   |
+-------+-----------+
|     5 | Hebei Province    |
|     6 | Shanxi Province    |
|    16 | Shandong Province    |
+-------+-----------+
3 rows in set (0.00 sec)

mysql> select * from city;
+--------------+-------+
| cityName     | proID |
+--------------+-------+
| Taiyuan City       |     6 |
| Shijiazhuang City     |     5 |
| Changchun City       |     9 |
+--------------+-------+
3 rows in set (0.00 sec)

mysql> # We use promary and city tables as Cartesian products
mysql> select * from promary,city;
+-------+-----------+--------+--------------+-------+
| proID | proName   | cityID | cityName     | proID |
+-------+-----------+--------+--------------+-------+
|     5 | Hebei Province    |      1 | Taiyuan City       |     6 |
|     6 | Shanxi Province    |      1 | Taiyuan City       |     6 |
|    16 | Shandong Province    |      1 | Taiyuan City       |     6 |
|     5 | Hebei Province    |      1 | Shijiazhuang City     |     5 |
|     6 | Shanxi Province    |      1 | Shijiazhuang City     |     5 |
|    16 | Shandong Province    |      1 | Shijiazhuang City     |     5 |
|     5 | Hebei Province    |      1 | Changchun City       |     9 |
|     6 | Shanxi Province    |      1 | Changchun City       |     9 |
|    16 | Shandong Province    |      1 | Changchun City       |     9 |
+-------+-----------+--------+--------------+-------+
9 rows in set (0.10 sec)

As can be seen from the above, this is the combination of each line in promory and each line in city (a total of 3*3 rows), forming a Cartesian product.

2. Left Connections of External Connections

We can see that the Cartesian product of two tables can not get the data we want. How can we filter out the data we want?
At this point we can use the left link to do it.
Format:
select column 1, column 2, column n from
tableA left outer join tableB
on tableA. column = table. column (condition) (here outer can be saved)
Left join is based on the left table A, go to the right table B to match the data, can not find the match, and use null to complete.
Raise a chestnut.

mysql> # left join
mysql> # Query the provinces corresponding to each city
mysql> select promary.*,city.* from
    -> promary left join city
    -> on promary.proID = city.proID;
+-------+-----------+--------------+-------+
| proID | proName   | cityName     | proID |
+-------+-----------+--------------+-------+
|     6 | Shanxi Province    | Taiyuan City       |     6 |
|     5 | Hebei Province    | Shijiazhuang City     |     5 |
|    16 | Shandong Province    | NULL         |  NULL |
+-------+-----------+--------------+-------+
3 rows in set (0.00 sec)

mysql> # left join
mysql> # Query the provinces corresponding to each city
mysql> select promary.*,city.* from
    -> city left join promary
    -> on promary.proID = city.proID;
+-------+-----------+--------------+-------+
| proID | proName   | cityName     | proID |
+-------+-----------+--------------+-------+
|     5 | Hebei Province    | Shijiazhuang City     |     5 |
|     6 | Shanxi Province    | Taiyuan City       |     6 |
|  NULL | NULL      | Changchun City       |     9 |
+-------+-----------+--------------+-------+
3 rows in set (0.40 sec)

3. Right Connection of External Connection

Format:
select column 1, column 2, column n from
tableA right outer join tableB
on tableA. column = table. column (condition)
Right join is based on right table B, go to left table A to match data, can not find matching, fill with null.
Left connection is interchangeable with right connection. TableA right outer join tableB = tableB left outer join tableA
Raise a chestnut.

mysql> # Right join
mysql> # Query the cities corresponding to each province
mysql> select promary.*,city.* from
    -> city right join promary
    -> on promary.proID = city.proID;
+-------+-----------+--------------+-------+
| proID | proName   | cityName     | proID |
+-------+-----------+--------------+-------+
|     6 | Shanxi Province    | Taiyuan City       |     6 |
|     5 | Hebei Province    | Shijiazhuang City     |     5 |
|    16 | Shandong Province    | NULL         |  NULL |
+-------+-----------+--------------+-------+
3 rows in set (0.00 sec)

After all, left join and right join are the same thing, but generally we use left join more.

4. inner Connection

Internal join, also known as equivalence join, forms a record set join by those records that match the join relationship between the fields in the two tables.
Simply put, it is to query the data in both left and right tables. That is, do not have null parts in the left-right connection.
The inner connection is the intersection of left and right connections.
Format:
select column 1, column 2, column n from
tableA right outer join tableB
on tableA. column = table. column (condition)
Raise a chestnut.

 mysql> # inner Connection
mysql> # Query the cities corresponding to each province, and there must be cities that are not null
mysql> select promary.*,city.* from
    -> promary inner join city
    -> on promary.proID = city.proID;
+-------+-----------+--------------+-------+
| proID | proName   | cityName     | proID |
+-------+-----------+--------------+-------+
|     5 | Hebei Province    | Shijiazhuang City     |     5 |
|     6 | Shanxi Province    | Taiyuan City       |     6 |
+-------+-----------+--------------+-------+
2 rows in set (0.00 sec)

#### 5. Join multiple tables
Needless to say, a chestnut is understandable.

 mysql> # prod_id, vend_id, prod_name, vend_name and note_id of goods with price less than 5 were found in products, product notes and venders tables.
 mysql> select products.prod_id,products.vend_id,prod_name,productnotes.note_id,vendors.vend_name from
    -> products  left join productnotes
    -> on products.prod_id = productnotes.prod_id
    -> left join vendors
    -> on products.vend_id = vendors.vend_id
    -> where prod_price < 5;
+---------+---------+---------------+---------+-------------+
| prod_id | vend_id | prod_name     | note_id | vend_name   |
+---------+---------+---------------+---------+-------------+
| FC      |    1003 | Carrots       |     104 | ACME        |
| FC      |    1003 | Carrots       |     110 | ACME        |
| SLING   |    1003 | Sling         |     111 | ACME        |
| FU1     |    1002 | Fuses         |    NULL | LT Supplies |
| TNT1    |    1003 | TNT (1 stick) |    NULL | ACME        |
+---------+---------+---------------+---------+-------------+
5 rows in set (0.00 sec)

From the above chestnuts, we can see that if we want to combine multiple tables, we only need to add one more join at the back. In addition, the joined table can be regarded as a table, which can be operated on by clauses such as where, group by and so on.

tips

• In union, order by alone will not work and will be optimized
• Clauses should be enclosed when using operations such as order by, or they can be used at the end.