MySQL knowledge learning-the fifteenth day: join tables


On the fifteenth day, the goal is over halfway, come on


One of the most powerful features of SQL is the ability to join tables in the execution of data retrieval queries.

Relational tables

The design of relational tables is to ensure that the information is decomposed into multiple tables, one table for one type of data
. The tables are related to each other through some commonly used values ā€‹ā€‹(relational in relational design)

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.

How to understand foreign keys?
For example, if there is a column value in table A that is equal to the primary key value in table B, then we call this column in table A the foreign key of table A.

Why use join

Simply put, a join is a mechanism for associating tables in a SELECT statement, so it is called a join.

Insert picture description here

Create connection

The creation of the join is very simple, specify all the tables to be joined and how they are related.
Look at the following example

Table student

Insert picture description here

table teacher

Insert picture description here

so that we connect the two tables together based on a certain relationship (class equal). The

Insert picture description here

usage is not much different from the previous one. The main reason is that the first two columns are in the student table and the latter is in the teacher table. The from statement is also slightly different from the previous usage. Before, it used to take one table, but now it takes two tables.

Also, here we are using fully qualified column names

Insert picture description here

The importance of the WHERE clause

It may seem strange to use the WHERE clause to establish a connection relationship, but in fact, there is a very good reason. Remember, **When joining several tables in a SELECT statement, the corresponding relationship is constructed on the fly. **There is nothing in the definition of a database table that can instruct MySQL how to join the table. You must do this yourself. When joining two tables, what you actually do is pair every row in the first table with every row in the second table. The WHERE clause serves as a filter condition, and it only contains rows that match the given condition (here, the join condition). Without the WHERE clause, every row in the first table will be paired with every row in the second table, regardless of whether they can be logically matched together.

Cartesian product (cartesian product) Cartesian product of the result returned by a table relationship without join conditions. The number of rows retrieved will be the number of rows in the first table multiplied by the number of rows in the second table

Look at the following example.

Insert picture description here

Because there is no join condition, each row in the first table will be joined with each row in the second table, and 6*3=18 records will be returned.

Insert picture description here

Internal connection

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

For example, the

Insert picture description here

above statement can achieve the previous effect. The SELECT in this statement is the same as the previous SELECT statement, but the FROM clause is different. Here, the relationship between the two tables is an integral part of the FROM clause, specified by INNER JOIN. When using this grammar, the connection condition is given with a specific ON clause instead of a WHERE clause. The actual conditions passed to ON are the same as those passed to WHERE.

Insert picture description here

Join multiple tables

Since two tables can be joined, can three or more tables be joined? Of course it is possible.

SQL does not limit the number of tables that can be joined in a SELECT statement. The basic rules for creating connections are also the same. First list all the tables, and then define the relationships between the tables.

Insert picture description here

I'm here today, and it started raining again recently~