SQL is required. Note: Chapter 1 Join a table. SQL is required.

SQL is required. Note: Chapter 1 Join a table. SQL is required.
12.1 connections

One of the most powerful functions of SQL is to join a table during execution of data queries.

12.1.1 relational table

The appearance of the same data multiple times is by no means a good thing. This factor is the basis of the relational database design. A relational table is designed to ensure that information is divided into multiple tables, one type of data and one table. Each table is associated with each other through some common values (relational in relational design.
Scalability: it can adapt to the increasing workload without failure. Well-designed databases or applications are called good scalability ).

12.1.2 why use join

If data is stored in multiple tables, how can I retrieve data using a single SELECT statement?
The answer is to use connections. In short, join is a mechanism used to associate a table in a SELECT statement. Therefore, join is called join.

12.2 create a connection

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

12.2.1importance of where clause

Cartesian product: The result returned by the table relation without the link condition is cartesian product. The number of rows detected is the number of rows in the first table multiplied by the number of rows in the second table.
Do not forget the WHERE clause: ensure that all joins have a WHERE clause; otherwise, the DBMS will return much more data than the desired data.

12.2.2 internal connections

The join used so far is called equijoin, which is based on the equality test between two tables. This kind of join is also called an internal join.

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

12.2.3 join multiple tables

SQL has no limit on the number of tables that can be joined in a SELECT statement. The basic rules for creating connections 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 an order numbered 20007.
Performance Considerations: DBMS associates a specified table at runtime to process the join. This kind of processing may be very resource-consuming, so be careful not to join unnecessary tables. The more joined tables, the worse the performance.
Subqueries are not always the most effective method for performing complex SELECT operations. They can also be used to perform the same query using 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';