转载

T-SQL学习中--内联接，外连接，交叉连接

交叉连接可以表A和表B是同一张表取得笛卡尔乘积。比如说下面这种写法：

SQLSELECT D.n AS theday, S.n AS shiftno   FROM dbo.Nums AS D   cross JOIN dbo.Nums AS S WHERE D.n <= 7   AND S.N <= 3 ORDER BY theday, shiftno;

当然也可以表A和表B是两张不同的表，取得笛卡尔乘积。

SQLSELECT D.n AS theday, S.empid AS shiftno   FROM dbo.Nums AS D   cross JOIN [HR].[Employees] AS S WHERE D.n <= 7   AND S.empid <= 3 ORDER BY theday, shiftno;

但是 CROSS JOIN 不能用 ON 条件，只能用 WHERE 条件。下面这句与上面的语句查询结果相同。

SQLSELECT D.n AS theday, S.empid AS shiftno   FROM dbo.Nums AS D   inner JOIN [HR].[Employees] AS S on D.n <= 7   AND S.empid <= 3 ORDER BY theday, shiftno;

内联接查询，表A和表B中的数据必须紧密对应，不可以是Null。下面的查询中， Production.Products 表中没有商品记录的的日本供货商不会被列出来。 INNER 这个关键词是可以舍去的，如果只写 JOIN 就表示 INNER JOIN 。

SQLSELECT   S.companyname AS supplier, S.country,   P.productid, P.productname, P.unitprice FROM Production.Suppliers AS S   INNER JOIN Production.Products AS P     ON S.supplierid = P.supplierid WHERE S.country = N'Japan';

外连接查询有三种情况：左外连接，右外连接，全外连接。下面这个查询与上面这个查询写法只差一点点（WHERE变成了AND），但是结果就有区别：

SQLSELECT   S.companyname AS supplier, S.country,   P.productid, P.productname, P.unitprice FROM Production.Suppliers AS S   INNER JOIN Production.Products AS P     ON S.supplierid = P.supplierid     AND S.country = N'Japan';

而且 Production.Products 表中没有商品记录的的日本供货商同样也会被列出来，但是相关的P.productid, P.productname, P.unitprice都会被记为NULL。

下面这句：

SQLSELECT E.empid,   E.firstname + N' ' + E.lastname AS emp,   M.firstname + N' ' + M.lastname AS mgr FROM HR.Employees AS E   INNER JOIN HR.Employees AS M     ON E.mgrid = M.empid;

用了内联接，则最高主管（CEO）不会被列出来，因为最高主管没有更高的主管了。而改用左外连接

SQLSELECT E.empid,   E.firstname + N' ' + E.lastname AS emp,   M.firstname + N' ' + M.lastname AS mgr FROM HR.Employees AS E   LEFT OUTER JOIN HR.Employees AS M     ON E.mgrid = M.empid;

则CEO也会被列出来，CEO对应的mgr会被记为NULL。套用内联接的左外连接：

SQLSELECT   S.companyname AS supplier, S.country,   P.productid, P.productname, P.unitprice,   C.categoryname FROM Production.Suppliers AS S   LEFT OUTER JOIN Production.Products AS P     ON S.supplierid = P.supplierid   INNER JOIN Production.Categories AS C     ON C.categoryid = P.categoryid WHERE S.country = N'Japan';

查询出日本供货商的所有的产品以及产品类别名。而且 Production.Products 表中没有商品记录的的日本供货商同样也会被列出来，但是相关的P.productid, P.productname, P.unitprice, C.categoryname都会被记为NULL。

上面的语句与下面带括号的语句等同：

SQLSELECT   S.companyname AS supplier, S.country,   P.productid, P.productname, P.unitprice,   C.categoryname FROM Production.Suppliers AS S   LEFT OUTER JOIN      (Production.Products AS P        INNER JOIN Production.Categories AS C          ON C.categoryid = P.categoryid)     ON S.supplierid = P.supplierid WHERE S.country = N'Japan';

RIGHT OUTER JOIN 则与 LEFT OUTER JOIN 相反，根据ON条件和WHERE条件查询表A和表B，查询结果可以表A中数据为NULL。

FULL OUTER JOIN 则只要表A和表B中任一表中有数据，结果都会被显示出来。无论是表A为NULL，还是表B为NULL。

OUTER 也是可以被省略的。 LEFT JOIN 就是 LEFT OUTER JOIN 的简写，相应的， RIGHT JOIN 是 RIGHT OUTER JOIN 的简写， FULL JOIN 是 FULL OUTER JOIN 的简写。

正文到此结束