left join 和 inner join 区别

SET NOCOUNT ON
DECLARE @u TABLE ( dbId BIGINT, uId BIGINT, dId BIGINT, uName NVARCHAR(50))
INSERT INTO @u (dbId, uId,dId, uName) VALUES(1,1,1,'小明')
INSERT INTO @u (dbId, uId,dId, uName) VALUES(1,2,1,'小明2')
INSERT INTO @u (dbId, uId,dId, uName) VALUES(1,3,3,'小明3')
INSERT INTO @u (dbId, uId,dId, uName) VALUES(1,4,4,'小明4')
INSERT INTO @u (dbId, uId,dId, uName) VALUES(2,1,1,'小宗')

DECLARE @d TABLE ( dbId BIGINT, dId BIGINT, dName NVARCHAR(50))
INSERT INTO @d (dbId, dId, dName) VALUES(1, 1, '行政')
INSERT INTO @d (dbId, dId, dName) VALUES(1, 2, '技术')
INSERT INTO @d (dbId, dId, dName) VALUES(2, 1, '管理')

--第一种情况：条件（等式两边）涉及到两个表
SELECT a.*,b.* FROM @u A LEFT JOIN @d B ON a.did=b.did WHERE a.dbid=b.dbId 

SELECT a.*,b.* FROM @u A LEFT JOIN @d B ON a.did=b.did AND a.dbid=b.dbId

SELECT a.*,b.* FROM @u A INNER JOIN @d B ON a.dbid=b.dbid AND a.did=b.did

--第二情况：条件（等式两边）涉及到左边表
SELECT a.*,b.* FROM @u A LEFT JOIN @d B ON a.did=b.did and a.dbid=b.dbId AND a.uName='小明' 

SELECT a.*,b.* FROM @u A LEFT JOIN @d B ON a.did=b.did and a.dbid=b.dbId WHERE a.uName='小明'

--第三情况：条件（等式两边）涉及到右边表
SELECT a.*,b.* FROM @u A LEFT JOIN @d B ON a.did=b.did and a.dbid=b.dbId AND b.dName='行政' 

SELECT a.*,b.* FROM @u A LEFT JOIN @d B ON a.did=b.did and a.dbid=b.dbId WHERE b.dName='行政'