Remember the multi-table query of the database

(Amway two well-spoken resources: Https://www.cnblogs.com/tilv37/p/5199139.html and https://jingyan.baidu.com/article/ 63acb44acfa95161fcc17e8a.html

A little bit about the background, now there is a medicinal table, base table, raw materials to the table, there is an association of raw materials to the table. This is where the raw material goes, the raw material of a certain medicinal plant in a certain base. In other words, the raw material is linked to the associated table and the base and medicinal herbs.

Medicine Table:

(There are no attributes to show later.)

Base table:

Raw materials to the table:

Raw material to the related table:

Want to do is, the home is medicinal herbs, click on a medicinal herbs into the details of the table (base table), and then the base table has a view of the source of the control, click can see:

Such a table, and then use the associated query for the table:

What is called an associative query? is actually two tables of things, through join (whether left or right or inner join) and some join relationship, the condition becomes a table (can understand the temporary table):

First of all, the inner connection :

is a direct join:

SELECT Tg.g_name, Tm.g_medicinalgone_sale, Tm.g_medicinalgone_proportion, Tm.g_medicinalgone_price from T_medicinal_ Productbase_medicinalgone as TM JOIN T_medicinal_gone as TG on tm.g_medicinalgone_id = tg.id

The result is that only the data rows that contain the join conditions are output:

Then there is the outer connection :

Outer joins are not filtering the data, that is, the output that satisfies the condition of the connection, which does not satisfy the connection condition, is output (the default null value is output if no data column or row), and the outer join simply joins the two tables into a single table.

Outer join has left connection: Right Join

The difference between the left and right is to use the table as the benchmark to stitch two tables from the T1 left JOIN t2--This is the T1 on the other side of the standard, if this is the true JOIN, it is the T2 of the side of the standard,

The following two examples can be very good to see the difference

Left join: (the left side is the raw material to the right of the table of raw materials to the table)

SELECT Tg.g_name, Tm.g_medicinalgone_sale, Tm.g_medicinalgone_proportion, Tm.g_medicinalgone_price from T_medicinal_ Productbase_medicinalgone as TM left JOIN T_medicinal_gone as TG on tm.g_medicinalgone_id = tg.id

Right connection: (left for raw material to the right of the table of raw materials to the table)

We can see that because the right side of the table as the benchmark, so the right row must be all shown, but some of the raw materials go to no data (which is not the base of which herbs), so there are non-qualified raw materials, but still to show all. The default null value is displayed.

Remember the multi-table query of the database