One of the most powerful features of joining SQL is the ability to join tables in the execution of data retrieval queries. The junction table is based on the relational table. Understanding the relationship table is as follows, if a variety of goods produced by the same supplier, in the database need to store product information, and in the product information on this line does not store vendor information, because these products are the same supplier, so in the product information this line to record a unique supplier ID can be, Vendor information is stored separately for one table, and is also logged as an ID for one. In this way, the product table and the Vendor table are connected by a common vendor ID. As shown in the following table:
In the Vendors table, vend_id is called the primary key, and the vend_id in the Products table is a foreign key.
Why use junction tables, decomposition of data into multiple tables can be more efficient storage, more convenient processing, and has greater scalability. But these benefits are at a price. If the data is stored in more than one table, how do you retrieve the data with a single SELECT statement? The answer is to use a junction. Simply put, a junction is a mechanism used to correlate a table in a SELECT statement, so called a junction.
The main point of the following example is vendors.vend_id = products.vend_id, which, when compared, takes each row from the vendors table, each of which is compared to all rows in the Products table. Filters out each row in the Products table that satisfies the criteria.
In addition the junction table can also be used INNER JOIN. On to indicate that the statement has the same effect as the example above:
To join multiple tables, you can understand the following example, where the three conditions, the first condition, each product in the product table will have a vend_id, all this condition will not filter out some products; the second condition, the product on the order, should be part of all products; the third condition, Product with order number 20005;
MySQL junction table
