SQL Server Fundamentals: Join

3.Join (hemp egg nonsense not much to say there is a picture of the truth)

Test Data Script

1 CREATE TABLEAtable2   ( 3s#INT, 4Snamenvarchar( +), 5SageINT, 6Sfromnvarchar(8) 7   ) 8 9 Ten  Insert  intoAtable One  Select 1N'Liu', -N'A' Union  All A  Select 2N'Money Two', +N'A' Union  All -  Select 3N'Zhang San', -N'A' Union  All -  Select 4N'John Doe', -N'A'  the  -   -  -  +  CREATE TABLEbtable -   (  +s#INT,  ASnamenvarchar( +),  atSageINT,  -Sfromnvarchar(8)  -   )  -  -  -  Insert  intobtable in  Select 1N'John Doe', -N'B' Union  All -  Select 2N'Tom', +N'B' Union  All to  Select 3N'Liu', -N'B' Union  All +  Select 4N'Jack', -N'B'

View Code

Data Sheet

1) Cross join: No connection conditions are required for the crossover connection. Two tables of data are directly performed by Cartesian product operations.

SELECT * from Atable as A cross  join btable as B ORDER by a.s#

2) INNER JOIN = = Join within joins is the ability to list the records associated with two tables, which must be associated records.

SELECT * from Atable as A  INNER joins btable as B on a.sname = B.sname

3) The LEFT outer join = = to a full set of table B, while the match in a table has a value, and no match is substituted with a null value.

SELECT * from Atable as A left outer joins btable as B on a.sname = B.sname

4) Right outer join = = Right Jion produces the full set of table A, while the match in B table has a value, and no match is substituted with a null value.

SELECT * from Atable as A right outer joins btable as B on a.sname = B.sname

5) Full outer join = = Full Join produces A and B's set. It is important to note, however, that for records that do not have a match, NULL is the value.

6) The Union and union all are different when the union is going to be the same, and the Union is not going to be heavy.

Select Sname from Atable  Union  Select Sname from  btable  SELECT Sname from Atable  UNION all SELECT Sna Me from  btable  

SQL Server Fundamentals: Join