SQL&&LINQ:左（外）连接，右（外）连接，内连接，完全连接，交叉连接，多对多连接

SQL:

外连接和内连接：

左连接或左外连接：包含左边的表的所有行，如果右边表中的某行没有匹配，该行内容为空（NULL）

--outer jion:left join or left outer join

select * from dbo.Project left join dbo.Voice on (dbo.Project.voiceID=dbo.Voice.ID)

 

 

右连接或右外连接：包含右边表的所有行，如果左边表中的某行没有匹配，该行内容为空（NULL）

--outer jion:right join or right outer join

select * from dbo.Project right join dbo.Voice on(dbo.Project.voiceID=dbo.Voice.ID)

 

 

完全连接或完全外连接：涵盖左连接与右连接，包含两张表中的所有行，不匹配的内容为空（NULL）

--outer join:full join or full outer join

select * from dbo.Project full join dbo.Voice on (dbo.Project.voiceID=dbo.Voice.ID)

 

 

内连接：

--join or inner join

select * from dbo.Project inner join dbo.Voice on (dbo.Project.voiceID=dbo.Voice.ID)

select * from dbo.Project,dbo.Voice where (dbo.Project.voiceID=dbo.Voice.ID)

select p.ID,p.Name,p.bak,v.NAME from dbo.Project p join dbo.Voice v on(p.voiceID=v.ID)

 

 

交叉连接：（1）分为没有where子句的交叉连接将产生连接所涉及的表的笛卡尔积。第一个表的行数乘以第二个表的行数等于笛卡尔积结果集的大小。

--cross join

select * from dbo.Project cross join dbo.Voice

select * from dbo.Project cross join dbo.Voice where (dbo.Project.voiceID=dbo.Voice.ID)

 

 

--两表关系为一对多，多对一，多对多的连接语句

select p.Name,v.NAME from dbo.Project_Voice as pv left join dbo.Voice as v on v.ID=pv.voiceID left join dbo.Project as p on p.ID=pv.projectID

 

 

 

select p.Name,v.NAME from dbo.Project_Voice as pv left join dbo.Voice as v on v.ID=pv.voiceID left join dbo.Project as p on p.ID=pv.projectID

where p.voiceID=v.ID

 

 

 

LINQ:（概念和结果参考sql语句的）

左连接：

var userInfo = from s in db.Project

                           join c in db.Voice on s.voiceID equals c.ID into ProjectV

                           from pv in ProjectV.DefaultIfEmpty()

                           select new { s.Name,s.bak,pv.NAME,s.ID};

右连接：

var userInfo = from c in db.Voice

                           join s in db.Project on c.ID equals s.voiceID into PV

                           from pv in PV.DefaultIfEmpty()

                           select new { pv.Name,pv.bak,pv.ID,c.NAME};

内连接：

var userInfo = from s in db.Project

                           join c in db.Voice on s.voiceID equals c.ID

                           select new { s.Name, s.bak, c.NAME, s.ID };