MySQL must know-15th chapter-junction table

15th Chapter-junction table

This chapter describes what a junction is, why you use a junction, and how to write a SELECT statement that uses a junction.

15.1 coupling

One of the most powerful features of SQL is the ability to join tables in the execution of data retrieval queries. A junction is the most important operation that can be performed with a select of SQL, and a good understanding of joins and their syntax is an extremely important part of learning SQL. Before you can use joins effectively, you must understand some of the basics of relational tables and relational database design. The following is not the full knowledge of this content, but it is sufficient as a primer.

15.1.1 Relationship Table

The best way to understand a relational table is to look at a real-world example. If you have a database table that contains a product catalog, there is one row for each category of items. The information to be stored for each item includes the product description and price, as well as the supplier information that produced the product. Now, if you have a variety of items produced by the same supplier, where do you store supplier information (e.g., supplier name, address, contact method, etc.)? The reasons for storing this data separately from product information are as follows.

• Because vendor information is the same for each product produced by the same vendor, repeating this information for each product is a waste of time and storage space.
• If supplier information changes (for example, supplier move or phone number changes), only one change is required.
• If you have duplicate data (that is, each product stores vendor information), it is difficult to ensure that the data is entered the same way each time. Inconsistent data is difficult to use in reports.

The key is that the same data appearing multiple times is never a good thing, and this factor is the basis of relational database design. The design of a relational table is to ensure that the information is broken into multiple tables, a class of data a table. Tables are related to each other through some common values, that is, relationships in design relationships (relational).
In this example, you can create two tables, one to store vendor information, and another to store product information. The Vendors table contains all vendor information, one row per vendor, and each vendor has a unique identity. This identity is called the primary key (primary key) (first mentioned in the 1th chapter) and can be a vendor ID or any other unique value. The Products table stores only product information, which does not store other vendor information other than the store vendor ID (the primary key of the vendors table). The primary key of the vendors table is also called the foreign key of the products, it associates the vendors table with the Products table, and the supplier ID can be used to find out the vendor's details from the vendors table.
The foreign key (foreign key) foreign key is a column in a table that contains the primary key value of another table and defines the relationship between the two tables. The benefits of doing this are as follows:

• Supplier information is not duplicated, thus not wasting time and space;
• If the supplier information changes, you can only update the single record in the Vendors table, the data in the related table is not changed;
• With no duplication of data, it is clear that the data is consistent, making it easier to process data.
  In short, relational data can be efficiently stored and easily processed. Therefore, the scalability of relational databases is far better than that of non-relational databases.
  Scalability (scale) adapts to the increasing workload without failure. A well-designed database or application is called scalability (scale well).

15.1.2 Why to use a junction

As mentioned, decomposition data can be more efficiently stored for multiple tables, processed more easily, and has greater scalability. But these benefits are at a price. If the data is stored in more than one table, how can I retrieve the data with a single SELECT statement? The answer is to use junctions. Simply put, a junction is a mechanism used to correlate a table in a SELECT statement, so called a junction. With special syntax, you can join multiple tables to return a set of outputs that join the correct rows in the associated table at run time.

Maintaining referential integrity it is important to understand that a junction is not a physical entity. In other words, it does not exist in the actual database table. The junction is created by MySQL as needed, and it exists in the execution of the query. When working with relational tables, it is important to only insert legitimate data in a closed series. Returning to this example, if you insert a product from a vendor that has an illegal vendor ID (that is, not appearing in the vendors table) in the Products table, those products are inaccessible because they are not associated with a vendor. To prevent this from happening, you can instruct MySQL to allow only legitimate values (that is, vendors appearing in the Vendors table) to appear in the Vendor ID column of the Products table. This is the maintenance of referential integrity, which is achieved by specifying a primary key and a foreign key in the table's definition. (This will be introduced in Chapter 21st.) ）

15.2 Creating a junction

The creation of a junction is very simple, which specifies all the tables to be joined and how they relate. Take a look at the following example:

Let's take a look at this code. The SELECT statement specifies the columns to retrieve, as with all previous statements. The biggest difference here is that the two columns specified (Prod_name and prod_price) are in one table, and the other column (Vend_name) is in another table. Now look at the FROM clause. Unlike the previous SELECT statement, the FROM clause of this statement lists two tables, namely vendors and products. They are the names of the two tables that this SELECT statement joins. The two tables are correctly joined with the WHERE clause, which instructs MySQL to match the vend_id in the vend_id and products tables in the Vendors table. You can see that the two columns to match are specified with vendors.vend_id and products.vend_id. This is required for this fully qualified column name, because if only vend_id is given, MySQL does not know which one (they have two, one in each table).

Fully qualified column names when the referenced column may appear ambiguity, you must use the fully qualified column name (table and column names separated by a point). If you refer to a column name with ambiguity that is not restricted by the table name, MySQL returns an error.

15.2.1 importance of the WHERE clause

It may seem strange to use a WHERE clause to establish a junction relationship, but in fact there is a good reason. Keep in mind that when you join several tables in a SELECT statement, the corresponding relationship is constructed in the run. There is nothing in the definition of a database table that indicates how MySQL can connect to a table. You must do it yourself. When you join two tables, what you actually do is pair each row in the first table with each row in the second table. The WHERE clause is a filter condition that contains only those rows that match a given condition (this is the join condition). There is no WHERE clause, and each row in the first table is paired with each of the rows in the second table, regardless of whether they are logically matched together.

Cartesian product (Cartesian product) returns a Cartesian product from a table relationship without a junction condition. The number of rows retrieved will be the number of rows in the first table multiplied by the number of rows in the second table. To understand this, take a look at the following SELECT statement and its output:

As you can see from the output above, the corresponding Cartesian product is not what we want. The data returned here matches each product with each vendor, which includes the incorrect product from the supplier. In fact, some suppliers have no products at all.

Don't forget that the WHERE clause should ensure that all junctions have a WHERE clause, otherwise MySQL will return much more data than desired. Similarly, the WHERE clause should be guaranteed to be correct. Incorrect filtering conditions will cause MySQL to return incorrect data.

Fork coupling Sometimes we hear a join type that returns a Cartesian product called a cross join.

15.2.2 internal Coupling

The junction used so far is called the equivalent Junction (Equijoin), which is based on an equality test between two tables. This connection is also called an internal junction. In fact, for this kind of join you can use a slightly different syntax to explicitly specify the type of junction. The following SELECT statement returns exactly the same data as the previous example:

The select in this statement is the same as the previous SELECT statement, but differs from the FROM clause. Here, the relationship between the two tables is part of the FROM clause and is specified with a inner join. When using this syntax, the join condition is given with a specific on clause rather than a WHERE clause. The actual conditions passed to on are the same as those passed to where.

Which syntax is used for ANSI SQL specification preferred inner JOIN syntax. In addition, although it is quite simple to define a junction using a WHERE clause, using explicit JOIN syntax ensures that the join condition is not forgotten, and sometimes it can affect performance.

15.2.3 joins multiple tables

SQL has no limit on the number of tables that can be joined in a SELECT statement. The basic rules for creating junctions are the same. All tables are listed first, and then the relationships between the tables are defined. For example:

This example displays the items in the order numbered 20005. Order items are stored in the OrderItems table. Each product is stored according to its product ID, which refers to the product in the Products table. These products are linked to the appropriate vendor in the vendors table through the vendor ID, and the vendor ID is stored in the records for each product. The FROM clause here lists 3 tables, while the WHERE clause defines the two junction conditions, and the third join condition is used to filter out the items in order 20005.

Performance considers that MySQL associates each table specified at run time to handle the junction. This kind of processing can be very resource-intensive, so be careful not to join unnecessary tables. The more tables are joined, the worse the performance drops.
You can now review the examples in chapter 14th. The example below shows a SELECT statement that returns a list of customers ordering the product TNT2:

As described in the 14th chapter, subqueries are not always the most efficient way to perform complex select operations, and the following are the same queries that use joins:

As described in chapter 14th, 3 tables are used to return data in this query. But instead of using them in nested subqueries, we used two joins. There are 3 WHERE clause conditions. The table in the first two associated junctions, followed by a filter product TNT2 data.

Do more experiments as you can see, there is generally more than one way to perform any given SQL operation. There are few methods of absolute correctness or absolute error. Performance may be affected by the type of operation, the amount of data in the table, whether an index or key exists, and some other conditions. Therefore, it is necessary to experiment with different selection mechanisms to find out the best method for the specific situation.

15.3 Summary

Joins are the most important and powerful feature in SQL, and the efficient use of joins requires a basic understanding of relational database design. This chapter introduces some basic knowledge of relational database design with the introduction of junctions, including the most frequently used form of junction (also known as Internal Junction). The next chapter describes how to create other types of joins.

MySQL must know-15th chapter-junction table