Mysql from entry to enchantment-6. table join, combined query

Mysql from entry to enchantment-6. table join, combined query

❤ Series content❤

Mysql from entry to enchantment-1. Data preparation (
starting chapter) Mysql from entry to enchantment-2. Manipulating databases and tables
Mysql from entry to enchantment-3. Query, sort, and WHERE filtering
Mysql from entry to enchantment- 4. Wildcards, calculated fields, and functions
Mysql from entry to enchantment-5. Aggregation, grouping, and sub-query
Mysql from entry to enchantment-6. Table join and combination query
Mysql from entry to enchantment-7. Insert, update and Delete (to be updated...)
Mysql from entry to enchantment-8. Views, stored procedures, transaction processing (to be updated...)
Mysql from entry to enchantment-9. Cursors, advanced SQL features (to be updated...)
Mysql from entry to Enchanting——10. Shorthand of knowledge points (end) (to be updated...)


Main content of this article

Self-connection, equivalent connection, internal connection, natural connection, external connection and comparison chart, UNION combination query. If you don’t understand the structure of the table in the text, you can check the series homepage. Not much to say, let's get to the point!



1. Table join

A coupling mechanism, used in an SELECTassociation table statement, so called links. Using a special syntax, you can join multiple tables to return a set of output, and join the correct rows in the associated table at runtime. The connection is not a physical entity. In other words, it does not exist in the actual database table. It only exists during query execution.


1.1 Self-connection

If contact with the customer to give the name Jim Jonesof the same company in a letter sent to all customers. This query requires first findingJim Jones work of the company name, and then find customers working in the company. The following is implemented separately using subqueries and self-joins.

Let's take a look at the way of subquery:

mysql> SELECT cust_id, cust_name, cust_contact
    -> FROM Customers
    -> WHERE cust_name = (SELECT cust_name
    ->                    FROM Customers
    ->                    WHERE cust_contact = 'Jim Jones');
+------------+-----------+--------------------+
| cust_id    | cust_name | cust_contact       |
+------------+-----------+--------------------+
| 1000000003 | Fun4All   | Jim Jones          |
| 1000000004 | Fun4All   | Denise L. Stephens |
+------------+-----------+--------------------+

Next, use the self-connection method:

mysql> SELECT c1.cust_id, c1.cust_name, c1.cust_contact
    -> FROM Customers AS c1, Customers AS c2
    -> WHERE c1.cust_name = c2.cust_name
    ->  AND c2.cust_contact = 'Jim Jones';
+------------+-----------+--------------------+
| cust_id    | cust_name | cust_contact       |
+------------+-----------+--------------------+
| 1000000003 | Fun4All   | Jim Jones          |
| 1000000004 | Fun4All   | Denise L. Stephens |
+------------+-----------+--------------------+

Under normal circumstances, the self-joining method is much faster than the subquery method.


1.2 Equivalent connection

In order to better compare the effect of the various links, the following examples use the following table1, table2the data in the table.
Two tables table1, table2the following data:

table1                      table2
+------+------+------+      +------+------+------+
| A    | B    | C    |      | C    | D    | E    |
+------+------+------+      +------+------+------+
|    1 |    2 |    3 |      |    2 |    3 |    4 |
|    4 |    5 |    6 |      |    6 |    7 |    8 |
+------+------+------+      +------+------+------+

Now through equivalence join, get the data in the two tables.

mysql> SELECT *
    -> FROM table1 AS t1, table2 AS t2
    -> WHERE t1.C = t2.C;
+------+------+------+------+------+------+
| A    | B    | C    | C    | D    | E    |
+------+------+------+------+------+------+
|    4 |    5 |    6 |    6 |    7 |    8 |
+------+------+------+------+------+------+

Note : the above example WHERElimits the join condition, if there are no conditions, the result is returned two tables Cartesian product returns 6 × 9Total 54


1.3 Inner join

The above connection can also be called an inner connection, and it has another syntax. The returned result is the same as above.

mysql> SELECT *
    -> FROM table1 AS t1 INNER JOIN table2 AS t2
    ->   ON t1.C = t2.C;
+------+------+------+------+------+------+
| A    | B    | C    | C    | D    | E    |
+------+------+------+------+------+------+
|    4 |    5 |    6 |    6 |    7 |    8 |
+------+------+------+------+------+------+


1.4 Natural connection

Natural connection is a special kind of equivalence connection, which automatically compares the same attribute columns in two relational tables without adding join conditions, and eliminates duplicate attribute columns in the result.

mysql> SELECT *
    -> FROM table1 AS t1 NATURAL JOIN table2 t2;
+------+------+------+------+------+
| C    | A    | B    | D    | E    |
+------+------+------+------+------+
|    6 |    4 |    5 |    7 |    8 |
+------+------+------+------+------+


1.5 External connection

1.5.1 Left outer connection

In the left outer link, table1all the records in the left table ( ) will be displayed, while the right table ( table2) will only display the records that meet the search criteria. The lack of records in the table on the right are all NULL.

mysql> SELECT *
    -> FROM table1 AS t1 LEFT JOIN table2 AS t2
    ->   ON t1.C = t2.C;
+------+------+------+------+------+------+
| A    | B    | C    | C    | D    | E    |
+------+------+------+------+------+------+
|    4 |    5 |    6 |    6 |    7 |    8 |
|    1 |    2 |    3 | NULL | NULL | NULL |
+------+------+------+------+------+------+


1.5.1 Right outer connection

In the right outer link, table2all the records in the right table ( ) will be displayed, while the left table ( table1) will only display the records that meet the search criteria. The lack of records in the table on the left are all NULL.

mysql> SELECT *
    -> FROM table1 AS t1 RIGHT JOIN table2 AS t2
    ->  ON t1.C = t2.C;
+------+------+------+------+------+------+
| A    | B    | C    | C    | D    | E    |
+------+------+------+------+------+------+
|    4 |    5 |    6 |    6 |    7 |    8 |
| NULL | NULL | NULL |    2 |    3 |    4 |
+------+------+------+------+------+------+


1.6 Comparison of four types of connections

Inner join
Natural connection (de-duplication)
Left outer link
Right outer join


2. Combined query

2.1 UNION combination query

If you need to Illinois, Indianaand Michiganall customer reports, and several other states, also want to include all located no matter what state the Fun4Allcustomers of the company.

mysql> SELECT cust_name, cust_contact, cust_email
    -> FROM Customers
    -> WHERE cust_state IN ('IL', 'IN', 'MI')
    -> UNION
    -> SELECT cust_name, cust_contact, cust_email
    -> FROM Customers
    -> WHERE cust_name = 'Fun4All';
+---------------+--------------------+-----------------------+
| cust_name     | cust_contact       | cust_email            |
+---------------+--------------------+-----------------------+
| Village Toys  | John Smith         | [email protected] |
| Fun4All       | Jim Jones          | [email protected]    |
| The Toy Store | Kim Howard         | NULL                  |
| Fun4All       | Denise L. Stephens | [email protected] |
+---------------+--------------------+-----------------------+

UNION The default deduplication, the results of executing two subqueries separately should add up to 5 rows.

If the situation does not require de-emphasis, can be used UNION ALLto achieve.

mysql> SELECT cust_name, cust_contact, cust_email
    -> FROM Customers
    -> WHERE cust_state IN ('IL', 'IN', 'MI')
    -> UNION ALL
    -> SELECT cust_name, cust_contact, cust_email
    -> FROM Customers
    -> WHERE cust_name = 'Fun4All';
+---------------+--------------------+-----------------------+
| cust_name     | cust_contact       | cust_email            |
+---------------+--------------------+-----------------------+
| Village Toys  | John Smith         | [email protected] |
| Fun4All       | Jim Jones          | [email protected]    |
| The Toy Store | Kim Howard         | NULL                  |
| Fun4All       | Jim Jones          | [email protected]    |
| Fun4All       | Denise L. Stephens | [email protected] |
+---------------+--------------------+-----------------------+

That's all there is to this article, if it feels good. ❤ Click a like before leaving! ! ! ❤Follow

Insert picture description here


-up will continue to share the "Mysql From Getting Started to Enchanting" series of articles, if you are interested, you can click to pay attention to not get lost~.