The difference and usage of four kinds of connection (join)

Difference

• Link:

As a verb, it means combining the contents of two or more tables and producing a result set that merges the columns and rows of each table. Table joins generally use the data that they have in common. For example, you can join a titles table and a publishers table with a common pub_id column to produce a result set that contains the title information and publisher information.

As a noun, the process or result of joining a table, as in the term "inner join", which represents a special method of joining a table.

• Join condition (Join condition)

A comparison clause that specifies how the table is associated with their joined fields. The most common join conditions are equal (one join), and the values of the joined fields must be the same in the join. For example, you can join two tables by looking for matching values in the titles table and in the pub_id column of the publishers table. However, any comparison operator can be part of a comparison condition.

• Inner joins (inner join)

A join in which records from two tables are combined and added to a query result only if the values of the joined fields meet certain criteria. For example, in Query Designer view, the default join between tables is an inner join that selects records from two tables only if the values of the joined fields are equal.

• Outer joins (outer join)

A join that also includes records that are not related to records in the join table. You can create three variants of an outer join to specify which unmatched rows are included: Left outer joins, right outer joins, and full outer joins.

• Left OUTER join (outer join)

An outer join type in which all rows of the first named table (the table on the left, which appears at the far left of the join clause) are included. No matching rows in the right table do not appear. For example, you can create a left outer join between the titles table and the publishers table to include all the titles, regardless of the publisher's information.

• Right outer join (outer join)

An outer join in which all rows of the second named table (the table on the right, which appears at the far right of the join clause) are included. Does not include rows that do not match in the left table. For example, a right outer join between the titles table and the publishers table will include all publishers, even those that do not have titles in the titles table.

These are the definitions of links in MSDN. Now let's look at the different ways in which these four kinds of links are used to see what their results are.

Titles Table sh (ISBN)  ph (publisher number) 232342  0010432  00382478123 005

Publishers table
ph (publisher number) MC (publisher name)
001 Red Tiger
002 RMH
003 Hazl

The contents of the two tables are now synthesized into the following table structure:

Sh (ISBN)    ph (publisher number)        MC (publisher name)

Now look at how the results of the four-link method will be different. Let's talk about their orders:

INNER JOIN:
Sele TITLES.SH,PUBLISHERS.PH,PUBLISHERS.MC;
From the titles inner join publishers; The inner in the && inner join can be omitted.
On titles.ph=publishers.ph

Outer joins:
Sele TITLES.SH,PUBLISHERS.PH,PUBLISHERS.MC;
From the titles outer join publishers;
On titles.ph=publishers.ph

Left JOIN:
Sele TITLES.SH,PUBLISHERS.PH,PUBLISHERS.MC;
From the titles left join publishers;
On titles.ph=publishers.ph

Right join:
Sele TITLES.SH,PUBLISHERS.PH,PUBLISHERS.MC;
From the titles right join publishers;
On titles.ph=publishers.ph

As you may see, except for the keyword that precedes the join, the other places are exactly the same, and the link conditions (that is, on that part) are the same. Results:

Links Within:

232342  001 Red Tiger 0432  003 Hazl

Full Link:

232342  001 001 red tiger NULL NULL  002 rmh0432  003 003 hazl82478123 NULL NULL

Zolin Connection:

232342  001 001 Red Tiger 0432  003 003 hazl82478123 NULL NULL

Right Link:

232342  001 001 red tiger null  NULL 002 rmh0432  003 003 Hazl

So it's easy to remember:

1, Zolin: is to join the table to the left of the "main", to titles.ph=publishers.ph as the standard, regardless of the table on the right there is no corresponding record, to the left table records in the results, but the right table does not have a corresponding record that should put a number into it? The answer is to put a null, it means no. In the left link, a record on the right table, but not on the left table, it is not put in the results, because the left table is the "Lord", or to put it to decide: it has, it must be put in, it does not, do not.

2, right link: the same as the left link, but for the "main" side of the transfer, replaced by the right to do "master".

3, within the link: and the left and right links are different, it must be left and right on both sides of the record will be put into the results, if there is a record does not exist on either side, then this record will not appear in the results.

4. External links: With the internal join phase, reverse, the equivalent of the left and right link merge: In any case, as long as a record appears in the two tables, it will certainly appear in the results, and then like the left and right link processing method, with NULL to fill the field without corresponding values.

Note: The above said "there", "no", meaning that the titles.ph=publishers.ph to determine the criteria to decide. For example, the current titles table's ph is "002", and in publishers, not a record of the ph value is "002", so Say "002" This value in the titles have, in Publisher, so titles.ph for "002" Records will be selected and finally put in the results.

If you think about it, the effect of this on is similar to where and having, which is filtering: selecting records based on criteria, and according to the workflow of the command, this on is executed earlier than where, having, and its conditional expression is not necessarily the form of titles.ph=publishers.ph, can continue to expand, become a very complex conditional expression, so as to complete a very effective, where and have not implemented filter function. For a specific comparison, see the section on, where, and having differences.

In the example given just now, the ph in the table is not repeated. Now, for example, an inline join is an example of a duplicate of the content in the Judgment field:

TEMP1  temp2aa  aa1  2

Sele Temp1.aa,temp2.aa;  From Temp1 join Temp2;    On TEMP1.AA=TEMP2.AA

The results of the operation are:

1 11 12 22 22 2

Obviously, some of the records are repeated several times. TEMP1.AA, although only 1 2, but TEMP2.AA have 3 2, so the result will be 1*3=3 2. If Temp1.aa and 2 2, then the result will be 2*3=6 2.

Knowing this is useful when you are doing multiple table-link queries. Do you want to consider the results of the first to second link when you are connected to the third table, will this happen? If so, is that what you want? If so, how do we deal with it? Some friends say that some of the results of this command are several times larger than the correct result, and it is necessary to see if there is such a duplication.

Learn the link, before you start, to say a very important question: In View Designer to see the joins of multiple tables, the links between them are connected by a line, looks like a string of gourd. If a table joins three tables at the same time, it looks like a forked branch, and that is not the case. People may not understand what I'm saying, and I'll give you an example and you'll see.

There is a product table, a list of incoming goods, a schedule of shipments, the requirements are now required product table all the products into, out of the situation, that is, the three appearance join command to synthesize a table, if there is no corresponding to enter, out of the record, but also listed but do not dispute the value of NULL. A friend who has just begun to learn is very likely to do this:

1, add these three tables in the designer;

2, and then use the Product table in the product number and the other two tables left link, so the product table has two links (that is, two lines);

3, then the three table fields are done as output fields.

But what about the results? Wrong. Only one table record appears in the results, even if you try the four types of links, the results are incorrect.

Why, then? I estimate the following reasons: if the product table is only linked to the purchase table, the system according to the product table and the join relationship, to the product table for the left table, and the purchase of this right table composed of a temporary result, and then to the temporary table as the left table, and then to find the right table of the purchase table. and the stock table on the right there is no table, then the system stops the link, to where to filter. But now the product table with two table left JOIN, the system will automatically select one of the first link, the link results come out, this temporary result on the right side of the table, the system will stop the link action. The remaining shipping table, return form is not linked, so that table is useless.

The solution is: the purchase table with the purchase table product number of the full Link product table, then the Product table and product list with the product number of the full link shipping table, into, the order of the list can be transferred, but the product table must be in the middle, and two link types are full links, otherwise the results are not correct. Such links, in the designer by the link in the table in the left and right order, very intuitive: is a bunch! No bifurcation. The process of implementing this method is:

Inbound table full Link product table, even if a product is not in stock, but the results are the same as the record, but its purchase content is null value. And then this temporary results with the full link to the shipment, the results of the previous step is similar, there are shipments of the record number of shipments, otherwise is a null value. Because there is no fork, so all the tables are linked in, the result is right (of course, if the link type is wrong, the result is also wrong).

After seeing that question, there is one more question to say. In that example, if a product number in the product table is duplicated, the number of n records is the same, and the number of records in the inbound sheet appears in M, the result is a little different. First in the Purchase table and Product table full link results, this number will appear n*m times, not once. Then this temporary table to the full link with the shipment, even if the number in the shipping table appears once, but in the final link results, this number will appear n*m times, it's shipping records also repeated n*m times. If you now want sum () shipping records, then the number of shipments will be magnified n*m times, the purchase record is not allowed. So if the number in the product list is duplicated, the result is likely to be wrong.

But the number of the product table does not duplicate, the result will certainly be correct? is not necessarily. Let's try it, assuming that the stock table and the product table number "001" are only one occurrence, but the shipping table appears two times. The final result of the "001" or appeared two times, two times the product name, purchase quantity are the same, but the number of shipments is different. If sum () is at this point, the result is still incorrect.

So if you want to summarize operations such as SUM () after a multiple-table link, it's not possible to use the above method. The solution is to use union and use it to combine the aggregate of incoming goods with the total shipment, so as to avoid interfering with each other.

Note: Connection between 3 tables

Eg:select a.* b.field1,c.field2 from table a left outer jion table B on a,field1=b.field1 left OUTER join table C on A.field2=c.field2