"MySQL must learn Notes": Advanced Junction

Create an advanced junction

The last blog post simply introduces the use of the next link, and this post explains how to create an advanced junction.

Using Table aliases

In the previous study, we know how to give a list of aliases, and now look back, as follows:

select cust_name,upper(cust_name) as upper_name from customers;

Alternatively, use the CONCAT function to group the customer's name and phone into a new message, and take the new message with an alias, as follows:

The above is to give the column alias, you can also give the calculated field alias. In addition, SQL allows you to alias table names. There are two main reasons for doing so, as follows:

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

Here's an example of how to alias a table. As follows:

The alias is c/o/items by the As for Customers/orders/ordersitems, so that in subsequent column references, the alias is used directly instead of the full name, which can be a little simpler to write.

Use different types of junctions

In the previous blog post, an internal junction (that is, an equivalent junction) was used. First look at the following: The internal junction is done by table_name INNER JOIN innerjoin_table_name ON clause, the actual example is as follows:

The above is a simple internal coupling, because just learn to give the table alias, it comes with the knowledge point added.

In addition to the internal coupling, there are 3 other forms, namely:

• Self-coupling
• Natural coupling
• External coupling

Here's a description.

Self-coupling

Self-coupling, from the literal meaning can be understood, is that there is only one table, and their own connection.

Here's an example: Want to see the Student score table in the math scores of 100 of People's English score is how many points?

You might say that this needs to be done with a self-coupling, and it should be OK to filter directly with where.

Indeed, for this example, it can be done directly with the WHERE clause, as follows:

In addition to using the where filter above can be done, you can also use a subquery to complete, as follows:

In addition to using the two methods described above, you can also use a self-junction to complete, as follows:

This is done in a self-junction way, the table in this query is actually the same table, is student2, because we need to reference a table two times, so we need to introduce the table Student2 introduce two different aliases, otherwise the Student2 reference is ambiguous. Therefore, MySQL does not know which instance of the Student2 table you are referencing.

When an alias is introduced, all access to the column needs to be specified with an alias, otherwise the error is due to ambiguity.

The following is an error because no instance of the Student2 table is specified:

The above describes the 3 method to complete such an operation, in the actual process, exactly which method, we should all try to determine which kind of performance is better.

Natural coupling

Because it is necessary to join multiple tables, at least one column should appear in more than one table. A standard junction (that is, an internal junction) returns all data (containing duplicate columns). The natural join exclusion occurs more than once, so that each column is returned only once.

Let's look at the standard junction first, and when we use (SELECT *) in the process of joining, the result is to return all the column data.

And the natural coupling is how to do exclude multiple occurrences, so that each column only return once??

The answer is: The system does not complete the work, we do it by ourselves. Natural coupling is such a connection, in which we 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.

To put it simply: A natural connection is the content of the output we specify, not all of the content (because the repeating column data is not what we want).

External coupling

Tomorrow, it's too late, April 3, 2016 01:45:33

"MySQL must learn Notes": Advanced Junction