MySQL Crash Course #07 # Chapter 15 relational database. INNER JOIN. Vs. Nested subquery

Index

• Understand the related tables. FOREIGN key
• JOIN and maintain referential integrity
• For some suggestions on joins, subquery VS. Linked table Query

I found that there is a tutorial in the MySQL documentation, but the concept does not seem to mention, just tell you how (statement), did not tell you the cause and consequences (principle). Want to see a book with vivid explanations, such as the one that will give the various joins the VN diagram, and do not know how to find it (as if you should search for SQL tutorial instead of MySQL Tutorial).

SQL Tutorial-w3schools

The SQL Tutorial for Data analysis | SQL Tutorial-mode Analytics

Understanding Relational Tables

The key here's that has multiple occurrences of the same data is never a good thing, and that principle is th E basis for relational database design. Relational tables was designed so information was split into multiple tables, one for each data type. The tables is related to a through common values (and thus the relational in relational design).

The book gives an example of a product table and a supplier table, a supplier can correspond to a lot of products, do not put the supplier's information on each line of product reasons are as follows:

1. Suppliers of multiple products are consistent, and repeating the same information is a waste of space
2. If the supplier's information changes, you have to update each of the vendor-related product records
3. A large probability of inconsistent data

Therefore, the products and suppliers should be divided into two forms, the two tables should have primary key, the Supplier table is dedicated to store the information of the supplier, and the product table is dedicated to the information of the product, each product record in addition to a supplier ID attribute should not contain any other information of the vendor, this property corresponds to the field called Foreign key (and the Vendor table's primary key relationship) have the following benefits:

1. Save time and space with no data duplication
2. Need to modify the supplier information only need to modify one place.
3. Because the data is not duplicated, the data consistency is well guaranteed.

Why use Joins?

As just explained, breaking data into multiple tables enables more efficient storage (efficient storage), easier manipulation (easy to operate), and Greater scalability (very high scalability). But these benefits come and a price.

If data is stored in multiple tables, how can I retrieve that data with a single SELECT statement?

The answer is to use a join.

It is important to understand that a join was not a physical entity in other words, it does not exist in the Actua L Database tables. A join is created by MySQL as needed, and it persists for the duration of the query execution.

-Maintaining referential integrity is that MySQL only allows valid data (the value of foreign key to exist in the primary table) is inserted into the relational table.

Creating a Join

SELECT Vend_name, Prod_name, Prod_price  from Vendors, products ORDER  by Vend_name, Prod_name;

SELECT Vend_name, Prod_name, Prod_price  from INNER JOIN  on= products.vend_id;

1. Although the default is inner join (that is, the one that creates the X * x table temporarily), it is best to use the INNER join on statement so that you never forget the type of join.
2. Joins are made temporarily at run time, and the more associated tables consume resources, so it's not necessary to don't mess with tables

It Pays to experiment As you can see, there are often more than one-to-perform any given SQL operation. And there is rarely a definitive right or wrong. Performance can affected by the type of operation, the amount of data in the tables, whether indexes and keys is Prese NT, and a whole slew of other criteria. Therefore, it's often worth experimenting with different selection mechanisms to find the one of that works best for you.

The fast or sub-query of the table depends on the situation, so you can test it when necessary ... The problem is. How to test?? --> Pending update

MySQL Crash Course #07 # Chapter 15 relational database. INNER JOIN. Vs. Nested subquery