SQL must-Know notes Chapter 12th junction table

12.1 Coupling

One of the most powerful features of SQL is the join table in the execution of a data query.

12.1.1 Relationship Table

It is not a good thing to have the same data multiple times, which 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).
Scalability (scale): ability to adapt to increasing workloads without failure. A well-designed database or application is called scalability (scale well).

12.1.2 Why to use a junction

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.

12.2 Creating a junction

SELECT vend_name,prod_name,prod_priceFROM Vendors,ProductsWHERE Vendors.vend_id = Products.vend_id;

The importance of 12.2.1WHERE clauses

Cartesian product (Cartesian product): The result returned by a table relationship without a link condition is a Cartesian product. The number of rows detected will be the number of rows in the first table multiplied by the number of rows in the second table.
Don't forget the WHERE clause: you should ensure that all junctions have a WHERE clause, or the DBMS will return much more data than you want.

12.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.

SELECT vend_name,prod_name,prod_priceFROM Vendors INNER JOIN ProductsON Vendors.vend_id = Priducts.vend_id;

12.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.

SELECT prod_name,vend_name,prod_price,quantityFROM OrderItems,Products,VendorsWHERE Products.vend_id = Vendors.vend_id     AND OrderItems.prod_id = Products.prod_id     AND order_num = 20007;

Displays the items in the order numbered 20007.
Performance considerations: The DBMS 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.
Subqueries are not always the most efficient way to perform complex select operations, and the same queries can be made with joins.

SELECT cust_name,cust_contactFROM Customers,Orders,OrderItemsWHERE Customers.cust_id = Order.cust_id     AND OrderItems.order_num = Order.order_num     AND prod_id = ‘RGAN01‘;

SQL must-Know notes Chapter 12th junction table