"Turn" SQL Nellian, outer connection (left join, left outer), simple understanding of cross-joins

From: http://www.cnblogs.com/kevinGaoblog/archive/2012/07/05/2577410.html

--Execute in Query Analyzer:
--Build Table Table1,table2:
CREATE TABLE table1 (ID int,name varchar (10))
CREATE TABLE table2 (ID int,score int)
INSERT INTO table1 Select 1, ' Lee '
INSERT INTO Table1 Select 2, ' Zhang '
INSERT INTO table1 Select 4, ' Wang '
Insert INTO table2 Select 1,90
Insert INTO table2 Select 2,100
Insert INTO table2 select 3,70
such as table
-------------------------------------------------
Table1 | Table2
-------------------------------------------------
ID Name | ID Score
1 Lee | 1 90
2 Zhang | 2 100
4 Wang | 3 70
-------------------------------------------------

The following are performed in Query Analyzer

One, outer connection
concepts: including left outer joins, right outer joins, or full outer joins

1. Left-side connection:outer JOIN
(1) The result set of the left outer join includes all rows of the left table specified in the OUTER clause, not just the rows that match the joined columns. If a row in the left table does not have a matching row in the right table, all select list columns in the right table in the associated result set row are null (NULL).
(2) SQL statement:
SELECT *

From table1 left join table2 on Table1.id=table2.id

-------------Results-------------
ID Name ID Score
------------------------------
1 Lee 1 90
2 Zhang 2 100
4 Wang Null NULL
------------------------------
NOTE: All clauses that contain table1, return table2 corresponding fields according to specified criteria, non-conforming null display

2. RightJoin
(1) A right outer join is a reverse join of a left outer join. All rows of the right table will be returned. If a row in the right table does not have a matching row in the left table, a null value will be returned for left table.
(2) SQL statement:
SELECT *

From table1 right join table2 on Table1.id=table2.id

-------------Results-------------
ID Name ID Score
------------------------------
1 Lee 1 90
2 Zhang 2 100
NULL NULL 3 70
------------------------------
NOTE: All clauses that contain table2, return table1 corresponding fields according to specified criteria, non-conforming null display

3. Complete outer join: Full JOIN or outer join
(1) A full outer join returns all rows from the left and right tables. When a row does not have a matching row in another table, the selection list column for the other table contains a null value. If there are matching rows between the tables, the entire result set row contains the data values of the base table.
(2) SQL statement:
SELECT *

From table1 full join table2 on Table1.id=table2.id

-------------Results-------------
ID Name ID Score
------------------------------
1 Lee 1 90
2 Zhang 2 100
4 Wang Null NULL
NULL NULL 3 70
------------------------------
Note: Returns the left and right connected and (see top

Second, internal connection
(1) Concept: an INNER join is a join that compares the values of a column to join with a comparison operator

(2) Inner connection:join or INNER JOIN

(3) SQL statement:
SELECT *

From table1 join table2 on Table1.id=table2.id

-------------Results-------------
ID Name ID Score
------------------------------
1 Lee 1 90
2 Zhang 2 100
------------------------------
Note: only table1 and table2 columns that match the criteria are returned

(4) equivalent (same as the following execution effect)

(1)
Select a.*,b.*

From table1 A,table2 b

where a.id=b.id

(2)
SELECT *

From table1 Cross join Table2

where Table1.id=table2.id (note: Cross join is conditional only in where, not on)

Three, cross-connect (full)

(1) Concept : A cross join without a WHERE clause will produce a Cartesian product of the table involved in the join. The number of rows in the first table multiplied by the number of rows in the second table equals the size of the Cartesian product result set. (Table1 and table2 cross-joins generate 3*3=9 Records)

(2) Cross-joins: crosses join (without conditions where ...)

(3) SQL statement:
SELECT *

From table1 Cross join Table2

-------------Results-------------
ID Name ID Score
------------------------------
1 Lee 1 90
2 Zhang 1 90
4 Wang 1 90
1 Lee 2 100
2 Zhang 2 100
4 Wang 2 100
1 Lee 3 70
2 Zhang 3 70
4 Wang 3 70
------------------------------
Note: Returns the 3*3=9 record, which is the Cartesian product

(4) equivalent (same as the following execution effect):
SELECT *

From Table1,table2