Introduction to joins and relational tables in mysql

Join

One of the most powerful features of SQL is the ability to join tables during the execution of a data retrieval query. Joins are the most important operation you can perform using SQL's SELECT, and a good understanding of joins and their syntax is an extremely important part of learning SQL.

Before you can effectively use joins, you must understand some basic knowledge of relational tables and relational database design. The following introduction is not all knowledge about this content, but it is enough to get you started.

Relationship table

The best way to understand the relationship table is to look at a real-world example.

Suppose there is a database table containing a product catalog, with one row for each category of items. The information to be stored for each item includes product description and price, as well as information about the supplier that produces the product.

Now, if there are multiple items produced by the same supplier, where is the supplier information (such as supplier name, address, contact information, etc.) stored? The reasons for storing this data separately from product information are as follows.

1. Because the supplier information for each product produced by the same supplier is the same, repeating this information for each product is a waste of time and storage space.

2. If the supplier information changes (for example, the supplier moves or the phone number changes), it only needs to be changed once.

3. If there is duplicate data (that is, supplier information is stored for each product), it is difficult to ensure that the data is entered in the same way every time. Inconsistent data is difficult to exploit in reports.

The key point is that it is never a good thing for the same data to appear multiple times. This factor is the basis of relational database design. The design of relational tables is to ensure that information is decomposed into multiple tables, one table for each type of data. Tables are related to each other through some commonly used values ​​(ie relationships in relational design). In this example, two tables can be created, one to store supplier information and the other to store product information. The vendors table contains all vendor information, with each vendor occupying one row and each vendor having a unique identifier. This identification is called the primary key and can be the vendor ID or any other unique value.

The products table only stores product information. It does not store other supplier information except the supplier ID (the primary key of the vendors table). The primary key of the vendors table is also called the foreign key of products. It associates the vendors table with the products table. The supplier ID can be used to find out the detailed information of the corresponding supplier from the vendors table.

Foreign key (foreign key) A 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 this are as follows:

1. Supplier information is not repeated, so time and space are not wasted;

2. If the supplier information changes, you can just update it For a single record in the vendors table, the data in related tables does not need to be changed;

3. Since there is no duplication of data, it is obvious that the data is consistent, which makes processing the data easier. In summary, relational data can be stored efficiently and processed conveniently. Therefore, relational databases scale far better than non-relational databases.

Scalability (scale) The ability to adapt to increasing workloads without failure. A well-designed database or application is said to scale well.

[Related recommendations]

1. What is a mysql subquery? How to filter using subqueries?

2.mysql creates calculated fields using subqueries

3.Why use joins and how to create joins

4.The importance of the WHERE clause in MySQL and how to join multiple surface

The above is the detailed content of Introduction to joins and relational tables in mysql. For more information, please follow other related articles on the PHP Chinese website!