MySQL must know-16th chapter-Create an advanced junction

16th-Create an advanced junction

This chapter explains the other types of joins (including their meanings and how to use them), and describes how to use table aliases and aggregate functions for joined tables.

16.1 Using table Aliases

The 10th chapter describes how to use aliases to reference the retrieved table columns. The syntax for listing aliases is as follows:

Aliases in addition to column names and calculated fields, SQL allows you to alias table names. There are two main reasons for this:

• Shorten SQL statements;
• Allows the same table to be used more than once in a single SELECT statement.

Take a look at the SELECT statement below. It is basically the same as the statement used in the example in the previous chapter, but it is changed to use aliases:

As you can see, the 3 tables in the FROM clause all have aliases. Customers as C establishes C as the alias of the customers, and so on. This makes it possible to use the province-written c instead of the full name customers. In this example, the table alias is used only in the WHERE clause. However, a table alias can be used not only in a WHERE clause, it can also be used for a list of select, an ORDER by clause, and other parts of a statement. It should be noted that table aliases are used only in query execution. Unlike column aliases, table aliases are not returned to the client.

16.2 using different types of junctions

So far, we have used only simple joins called internal joins or equivalent junctions (equijoin). Now look at 3 other junctions, which are self-joins, natural joins, and outer joins.

16.2.1 Self-coupling

As mentioned earlier, one of the main reasons for using table aliases is that you can reference the same table more than once in a single SELECT statement. Let's give an example. If you find a problem with an item (whose ID is dtntr), you want to know if any of the other items produced by the supplier that produced the item also have these problems. This query requires that the vendor of the item with the production ID dtntr be found first, and then the other items produced by this supplier are identified. Here's one way to solve this problem:

This is the first solution that uses subqueries. The internal SELECT statement makes a simple search, returning the vend_id of the item supplier with the production ID dtntr. The ID is used in the WHERE clause of the outer query to retrieve all the items produced by the vendor (the 14th chapter teaches all the contents of the subquery.) For more information, see this chapter). Now look at the same query that uses the junction:

The two tables that are required in this query are actually the same tables, so the Products table appears two times in the FROM clause. While this is perfectly legal, the reference to the products is ambiguous because MySQL does not know which instance of the Products table you are referencing. To resolve this issue, table aliases are used. The first occurrence of products is alias P1, and the second appears as Alias P2. You can now use these aliases as table names. For example, the SELECT statement uses the P1 prefix to explicitly give the full name of the desired column. If this is not the case, MySQL will return an error because there are two columns named prod_id and Prod_name, respectively. MySQL doesn't know which column to want, even if they are in fact the same column. WHERE (by matching vend_id in vend_id and P2 in p1), first join two tables and then filter the data by prod_id in the second table to return the data you want.

Using a self-junction instead of a subquery self-junction is typically used as an external statement to replace a subquery that is used to retrieve data from the same table. Although the final result is the same, sometimes processing a junction is much faster than processing a subquery. You should try two methods to determine which performance is better.

16.2.2 Natural Coupling

Whenever a table is joined, there should be at least one list that is now in more than one table (the joined column). The standard junction (the internal junction described in the previous chapter) returns all data, even the same column, multiple times. Natural join exclusions occur multiple times so that each column is returned only once. How do you get the job done? The answer is that the system does not complete this work, and you do it by yourself. Natural joins are such a connection in which you can only select those columns that are unique. This is typically done by using a wildcard character (SELECT *) on the table, using a clear subset of all other table columns. Here's an example:

In this example, the wildcard character is used only for the first table. All other columns are explicitly listed, so no duplicate columns are retrieved. In fact, every internal connection we've built so far has been natural, and it's possible that we'll never use an inner coupling that's not natural.

16.2.3 External coupling

Many junctions associate rows in one table with rows in another table. Sometimes, however, you need rows that contain no associated rows. For example, you might need to use a junction to do the following:

• Count the number of orders placed on each customer, including those who have not yet placed an order;
• List all products and order quantities, including products that are not ordered by people;
• Calculates the average sales size, including those customers who have not yet placed an order.
  In the above example, the junction contains rows that have no associated rows in the related table. This type of junction is called an external junction.
  The following SELECT statement gives a simple internal junction. It retrieves all customers and their orders:
  

The outer join syntax is similar. In order to retrieve all customers, including those without orders, the following can be done:

Similar to the inner join seen in the previous chapter, this SELECT statement uses the keyword outer join to specify the type of the junction (rather than the one specified in the WHERE clause). However, unlike a row in an inner junction that is associated with two tables, the outer join also includes rows that have no associated rows. When you use the outer join syntax, you must use the right or left keyword to specify a table that includes all of its rows (that is, the table to the left of the outer join that is pointing to the table on the outer join). The above example uses the left OUTER join to select all rows from the table (Customers table) on the right side of the FROM clause. To select all rows from the table on the right, you should use the OUTER JOIN, as shown in the following example:

No *= operator MySQL does not support the use of simplified characters characters *= and =*, both of which are popular in other DBMS.

The type of outer junction has two basic forms of outer junction: left Outer Junction and right Outer junction. The only difference between them is the order of the tables that are associated with each other. In other words, a left outer join can be converted to a right outer junction by reversing the order of the tables in the From or WHERE clause. Therefore, two types of external coupling can be used interchangeably, and which is purely based on convenience.

16.3 using a junction with aggregation functions

As described in chapter 12th, aggregation functions are used to summarize data. Although all the examples of aggregate functions so far are simply summarizing data from a single table, these functions can also be used with joins. To illustrate this point, take a look at an example. If you want to retrieve the number of orders that are placed by all customers and each customer, the following code with the count () function can do the work:

This SELECT statement uses the inner join to correlate the customers and Orders tables with each other. The GROUP BY clause groups data by customer, so the function calls count (Orders.order_num) for each customer's order count, returning it as Num_ord. Aggregation functions can also be easily used with other junctions. Take a look at the following example:

This example uses a LEFT outer join to contain all the customers, even those with no orders. The results show also includes the Customer Mouse house, which has 0 orders.

16.4 using junctions and junction conditions

Before concluding the two chapters on the link, it is necessary to summarize some of the points relating to the connection and its use.

• Note the type of junction you are using. In general we use internal joins, but the use of external junctions is also valid.
• Ensure that the correct join condition is used, otherwise incorrect data will be returned.
• The coupling condition should always be provided, otherwise the Cartesian product will be obtained.
• You can include more than one table in a junction, or even a different junction type for each junction. While this is legal and generally useful, you should test each junction separately before testing them together. This will make troubleshooting easier.

16.5 Summary

This chapter is the continuation of the previous chapter on connection. This chapter begins with teaching how and why to use aliases, and then discusses the different types of joins and the various syntactic forms used for each type of junction. We also show you how to use aggregation functions with joins, and some of the issues that you should be aware of when using joins.

MySQL must know-16th chapter-Create an advanced junction