MySQL must know and know -- Chapter 16 creating advanced connections

Posted by NathanLedet on Thu, 27 Jan 2022 19:54:49 +0100

Create advanced join

This chapter will explain some other join types (including their meaning and usage), and introduce how to use table aliases and aggregation functions for the joined tables.

Use table alias

Chapter 10( MySQL must know and know -- Chapter 10 creating calculation fields )Describes how to use aliases to reference table columns:

mysql> SELECT Concat(RTrim(vend_name), ' (', RTrim(vend_country), ')') AS vend_title
    -> FROM vendors
    -> ORDER BY vend_name;
+-------------------------+
| vend_title              |
+-------------------------+
| ACME (USA)              |
| Anvils R Us (USA)       |
| Furball Inc. (USA)      |
| Jet Set (England)       |
| Jouets Et Ours (France) |
| LT Supplies (USA)       |
+-------------------------+
6 rows in set (0.00 sec)

In addition to being used for table columns and calculated fields, SQL also allows aliasing of table names. Advantages:

Shorten SQL statements.
It is allowed to use the same table multiple times in a single SELECT statement.

To the previous chapter( MySQL must know and know -- Chapter 15 connection table )The following example uses a table alias:

mysql> SELECT cust_name, cust_contact
    -> FROM customers AS c, orders AS o, orderitems AS oi
    -> WHERE c.cust_id = o.cust_id
    -> AND oi.order_num = o.order_num
    -> AND prod_id = 'TNT2';
+----------------+--------------+
| cust_name      | cust_contact |
+----------------+--------------+
| Coyote Inc.    | Y Lee        |
| Yosemite Place | Y Sam        |
+----------------+--------------+
2 rows in set (0.03 sec)

All three tables in the FROM clause have table aliases. This makes it possible to omit rather than write the full name. Table aliases can be used not only in the WHERE clause, but also in the list of SELECT, ORDER BY clause and other statement parts.

Table aliases are only used in query execution and are not returned to the client.

Use different types of joins

So far, we have used simple joins called internal joins or equijoin s. There are also three other kinds of connection, namely self connection, natural connection and external connection.

Self coupling

As mentioned earlier, one advantage of aliases is that they allow the same table to be used multiple times in a single SELECT statement.

If you find a problem with an item (such as DTNTR ID), you want to know whether other items of the manufacturer of the item also have problems. Here is a way to find all the items of the manufacturer of this item:

mysql> SELECT prod_id, prod_name
    -> FROM products
    -> WHERE vend_id = (SELECT vend_id
    ->                  FROM products
    ->                  WHERE prod_id = 'DTNTR');
+---------+----------------+
| prod_id | prod_name      |
+---------+----------------+
| DTNTR   | Detonator      |
| FB      | Bird seed      |
| FC      | Carrots        |
| SAFE    | Safe           |
| SLING   | Sling          |
| TNT1    | TNT (1 stick)  |
| TNT2    | TNT (5 sticks) |
+---------+----------------+
7 rows in set (0.01 sec)

This is the first method, using subqueries( MySQL must know and know -- Chapter 14 using sub query ). First find out the supplier's ID, and then find out the supplier's items according to the supplier's ID.

Another solution is to use joins:

mysql> SELECT p1.prod_id, p1.prod_name
    -> FROM products AS p1, products AS p2
    -> WHERE p1.vend_id = p2.vend_id
    -> AND p2.prod_id = 'DTNTR';
+---------+----------------+
| prod_id | prod_name      |
+---------+----------------+
| DTNTR   | Detonator      |
| FB      | Bird seed      |
| FC      | Carrots        |
| SAFE    | Safe           |
| SLING   | Sling          |
| TNT1    | TNT (1 stick)  |
| TNT2    | TNT (5 sticks) |
+---------+----------------+
7 rows in set (0.00 sec)

Two tables in this query are the same table. However, because of ambiguity, we need to use table aliases to use two tables normally.

Self join instead of subquery is usually used as an external statement to replace the subquery statement used when retrieving data from the same table. Although the results are the same, sometimes the join is much faster than the subquery.

natural join

When joining tables, you should ensure that at least one column appears in more than one table (the joined column). A standard join returns all data, even if the same column appears multiple times. Natural join excludes multiple occurrences, so that each column returns only once.

However, the system does not complete this work, which is done by yourself. Natural joins are typically done by using wildcards (SELECT *) on the table and explicit subsets on the columns of all other tables.

mysql> SELECT c.*, o.order_num, o.order_date, oi.prod_id, oi.quantity, oi.item_price
    -> FROM customers AS c, orders AS o, orderitems AS oi
    -> WHERE c.cust_id = o.cust_id
    -> AND oi.order_num = o.order_num
    -> AND prod_id = 'FB';
+---------+-------------+----------------+-----------+------------+----------+--------------+--------------+-----------------+-----------+---------------------+---------+----------+------------+
| cust_id | cust_name   | cust_address   | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email      | order_num | order_date          | prod_id | quantity | item_price |
+---------+-------------+----------------+-----------+------------+----------+--------------+--------------+-----------------+-----------+---------------------+---------+----------+------------+
|   10001 | Coyote Inc. | 200 Maple Lane | Detroit   | MI         | 44444    | USA          | Y Lee        | ylee@coyote.com |     20005 | 2005-09-01 00:00:00 | FB      |        1 |      10.00 |
|   10001 | Coyote Inc. | 200 Maple Lane | Detroit   | MI         | 44444    | USA          | Y Lee        | ylee@coyote.com |     20009 | 2005-10-08 00:00:00 | FB      |        1 |      10.00 |
+---------+-------------+----------------+-----------+------------+----------+--------------+--------------+-----------------+-----------+---------------------+---------+----------+------------+
2 rows in set (0.01 sec)

In this example, there are no duplicate columns.

At present, the connections we establish are natural connections, and we may not encounter unnatural connections in the future.

outer join

Many joins associate rows from one table with rows from another table. But sometimes we need rows that are not associated. For example:

Count how many orders each customer has placed, including customers who have not placed orders.
List all products and quantities ordered, including products that no one has ordered.
Calculate the average sales scale, including customers who have not placed orders.

A join contains rows that have no associated rows in the related table, which is called an external join.

Retrieve the internal links of all orders and customers:

mysql> SELECT customers.cust_id, orders.order_num
    -> FROM customers INNER JOIN orders
    -> ON customers.cust_id = orders.cust_id;
+---------+-----------+
| cust_id | order_num |
+---------+-----------+
|   10001 |     20005 |
|   10001 |     20009 |
|   10003 |     20006 |
|   10004 |     20007 |
|   10005 |     20008 |
+---------+-----------+
5 rows in set (0.01 sec)

Use external links to retrieve all customers, including those who have not placed orders:

mysql> SELECT customers.cust_id, orders.order_num
    -> FROM customers LEFT OUTER JOIN orders
    -> ON customers.cust_id = orders.cust_id;
+---------+-----------+
| cust_id | order_num |
+---------+-----------+
|   10001 |     20005 |
|   10001 |     20009 |
|   10002 |      NULL |
|   10003 |     20006 |
|   10004 |     20007 |
|   10005 |     20008 |
+---------+-----------+
6 rows in set (0.00 sec)

This SELECT statement uses the OUTER JOIN keyword to specify the type of join instead of WHERE. Unlike the internal connection, the external connection includes customer 10002, which is not associated with any order. When using the OUTER JOIN syntax, you need to use LEFT or RIGHT to specify the table that needs to contain all rows, LEFT to specify the table to the LEFT of the keyword, and RIGHT to specify the table to the RIGHT of the keyword.

Select all rows of the right table:

mysql> SELECT customers.cust_id, orders.order_num
    -> FROM customers RIGHT OUTER JOIN orders
    -> ON customers.cust_id = orders.cust_id;
+---------+-----------+
| cust_id | order_num |
+---------+-----------+
|   10001 |     20005 |
|   10001 |     20009 |
|   10003 |     20006 |
|   10004 |     20007 |
|   10005 |     20008 |
+---------+-----------+
5 rows in set (0.00 sec)

MySQL does not support the use of simplified characters * = and = * without the * = operator.
There are two basic types of external connection: left external connection and right external connection. The only difference between them is the order of the associated tables. The left outer join can be converted to the right outer join by reversing the order of the table.

Use join with aggregate function

Aggregate function( MySQL must know and know - Chapter 12 summary data )Used to summarize data. Although the current examples summarize data from a single table, you can use the summary function in the join.

Retrieve all customers and their orders:

mysql> SELECT customers.cust_name,
    ->        customers.cust_id,
    ->        COUNT(orders.order_num) AS num_ord
    -> FROM customers INNER JOIN orders
    -> ON customers.cust_id = orders.cust_id
    -> GROUP BY customers.cust_id;
+----------------+---------+---------+
| cust_name      | cust_id | num_ord |
+----------------+---------+---------+
| Coyote Inc.    |   10001 |       2 |
| Wascals        |   10003 |       1 |
| Yosemite Place |   10004 |       1 |
| E Fudd         |   10005 |       1 |
+----------------+---------+---------+
4 rows in set (0.01 sec)

Aggregate functions can also be easily used with other joins:

mysql> SELECT customers.cust_name,
    ->        customers.cust_id,
    ->        COUNT(orders.order_num) AS num_ord
    -> FROM customers LEFT OUTER JOIN orders
    -> ON customers.cust_id = orders.cust_id
    -> GROUP BY customers.cust_id;
+----------------+---------+---------+
| cust_name      | cust_id | num_ord |
+----------------+---------+---------+
| Coyote Inc.    |   10001 |       2 |
| Mouse House    |   10002 |       0 |
| Wascals        |   10003 |       1 |
| Yosemite Place |   10004 |       1 |
| E Fudd         |   10005 |       1 |
+----------------+---------+---------+
5 rows in set (0.01 sec)

This example uses an external link to display all customers, including customers without orders.

Use connection and connection conditions

Key points about connection and its use:

Note the type of connection used. Generally, internal connections are used, but sometimes external connections are used.
Ensure that the correct join conditions are used, otherwise incorrect data will be returned.
The connection condition should always be provided, otherwise the Cartesian product will be obtained.
Multiple tables can be included in a join, and even different join types can be used for each join. However, each connection should be tested separately to facilitate troubleshooting.

Topics: Database MySQL SQL Server SQL

Programmer Think