Differences and connections between various connections in a multiple-table connection

Difference
Knowledge Points:
1, the definition and use of various connections
Internal connections:
Equal connection, non-equal connection (often not spoken)
Natural connection, you find the same field, do not have to give two tables between the columns to compare. So-called nature (not commonly used)
Cross connection, after selection, the product of the Cartesian card. This is useful if you want to list all the combinations. (not commonly used)
Outer joins:
Left and right outer joins (often not spoken)
Full join (not commonly used) is fully externally connected.
Merging joins (generally not) is the selection of mismatched records equal to the total connection minus the set of inner joins.
2, the key is to find out the difference between them.
1 What is the difference between full OUTER join and cross join?
A: cross-linking is a special connection within the inner join. There is no conditional connection at all, it should be said to be the widest connection. It is
Cartesian product. All outer joins are first connected and then all the records that are not matched are selected, but not the Cartesian product of the mismatch.
Just add in. And cross joins are going to be the product of the mismatch.
Related Topics:
You are are a database developer for a automobile dealership. are designing a database to support a
Web site that is used for purchasing automobiles. A purchasing A Automobile from the Web
Site would be able to customize he or her order by selecting the model and color.
The manufacturer makes four different models of automobiles. The models can is ordered in any one of
Five colors. A default color is assigned to each model.
The models are stored in a table named models, and the colors are, stored in a table named colors. These
Tables are shown in the exhibit.
You are need to create a list of all possible model and color combinations. Which script should use?


(Analysis, here is to list all combinations of models and colors, without any conditions at all,)
A SELECT M.modelname, C.colorname
From Colors as C full OUTER JOIN Models as M
On c.colorid = M.colorid
Order by M.modelname, c.colorname (analysis, this is a full outer join, because the model table has only one color ID (default), then here is only 8 (4+4) records selected.

B SELECT M.modelname, C.colorname
From Colors as C CROSS JOIN Models as M
Order by M.modelname, C.colorname (the answer here, the cross link is to list all the possible combinations of 20 (5*4) records, no conditions at all)

C SELECT M.modelname, C.colorname
From Colors as M INNER JOIN Colors as C
On m.colorid = C.colorid
Order by M.modelname, c.colorname (analysis, internal connection, only 4 records can be detected by default)

D SELECT M.modelname, C.colorname
From Colors as OUTER join Models as M (analysis, left outer JOIN, only 5 records detected by default)
On c.colorid = M.colorid
UNION
SELECT M.modelname, C.colorname
From Colors as OUTER join Models as M (analysis, right outer join, only 4 records can be investigated by default)
On c.colorid = M.colorid
Order by M.modelname, C.colorname

E SELECT M.modelname
From Models as M
UNION
SELECT C.colorname (parsing, this is a merge join, is a row that does not match the query.) should be 8-4 = 4 records, that is, the model does not exist in the color of the list
From Colors as C
ORDER BY M.modelname