SQL Server table connection (INNER join,left join,right join,full join,cross join,cross apply,outer APPLY)

1 common table connections (inner join,left join,right join,full Join,cross join)

if object_id (N‘table1 ‘, N‘U‘) is not null
    drop table table1
if object_id (N‘table2 ‘, N‘U‘) is not null
    drop table table2
create table table1 (id int, name varchar (20))
insert into table1
select 1, ‘Xiao Ming’ union all
select 2, ‘li’ ‘union all
select 3, ‘Little Chen’ union all
select 4, ‘Little Wu’

create table table2 (id int, age int)
insert into table2
select 1,23 union all
select 2,24 union all
select 3,25 union all
select 5,26
--Connection example
-,
select a. *, b. * from table1 a, table2 b where b.id = a.id
--cross join Cross join (Note: the condition after cross join can only be used where, not on)
select * from table1 a cross join table2 b where b.id = a.id
--inner join
select * from table1 a inner join table2 b on b.id = a.id
--left join left outer join
select * from table1 a left join table2 b on b.id = a.id
--right join right outer join
select * from table1 a right join table2 b on b.id = a.id
--full join full outer join
select * from table1 a full join table2 b on b.id = a.id

--The following two statements have the same effect
select * from table1 cross join table2
select * from table1, table2

--The following three statements have the same effect
select * from table1 a, table2 b where b.id = a.id
select * from table1 a cross join table2 b where b.id = a.id
select * from table1 a inner join table2 b on b.id = a.id 

2 Cross Apply,outer apply

(Cross Apply,outer apply is new in SQL Server 2005)

There is a cross join in SQL Server 2000 that is used for crossover connections.

Adding cross apply and outer apply is used for the crosstab join table-valued function.

Two forms of apply: cross apply and OUTER apply.

Cross APPLY returns only the rows in the external table that generated the result set through the table-valued function.

OUTER APPLY returns both the row that generated the result set and the row that does not produce the result set, where the value in the column generated by the table-valued function is NULL.

--Create table-valued function FN_TableValue
IF object_id (N‘FN_TableValue ’, N‘TF‘) IS NOT NULL
DROP FUNCTION FN_TableValue
GO
create function FN_TableValue (@id varchar (100))
returns @a TABLE (
     id int,
     name varchar (10) NULL
)
AS begin
insert into @a
select * from table2 where id = @id
return
end
go

--cross apply
select *
from table1 t1
cross apply (SELECT 1 id, ‘test1’ name UNION ALL SELECT 2, ‘test2’) t2
where t1.id = t2.id

select * from table1
select *
from table1 T1
cross apply FN_TableValue (T1.id)

--outer apply
select *
from table1 T1
outer apply FN_TableValue (T1.id) 

SQL Server table connection (INNER join,left join,right join,full join,cross join,cross apply,outer APPLY)