关于SQL组合查询问题的一个思考

这是她问我的问题，我当时理解的不对，告诉她怎么做，后来发现其实是不对的。现在就不再说具体当时是如何讨论的，将问题转化为类似的具体的问题。

问题描述：

以NorthWind数据库为例，现在dbo.Products表位主表，我现在要组合条件查询，例如查询出来

dbo.Products.ProductID,dbo.Products.ProductName,dbo.Products.QuantityPerUnit,dbo.Products.UnitPrice,

dbo.Products.UnitsInStock,dbo.Products.UnitsOnOrder,dbo.Products.ReorderLevel,dbo.Products.Discontinued,

dbo.Suppliers.CompanyName,dbo.Suppliers.ContactName,dbo.Suppliers.Country,dbo.Suppliers.Fax,

dbo.Suppliers.HomePage,dbo.Suppliers.Phone,dbo.Suppliers.PostalCode,

dbo.Categories.CategoryName,dbo.Categories.Description

牵扯到dbo.Products、INNER JOIN dbo.Suppliers 、INNER JOIN dbo.Categories三张表

按照dbo.Products.ProductName、 dbo.Suppliers.ContactName、dbo.Categories.CategoryName来查询。如果是一般的查询我想用下面的sql就完成了：

SELECT

dbo.Products.ProductName,dbo.Products.QuantityPerUnit,dbo.Products.UnitPrice,dbo.Products.UnitsInStock,

dbo.Products.UnitsOnOrder,dbo.Products.ReorderLevel,dbo.Products.Discontinued,

dbo.Suppliers.CompanyName,dbo.Suppliers.ContactName,dbo.Suppliers.Country,dbo.Suppliers.Fax,

dbo.Suppliers.HomePage,dbo.Suppliers.Phone,dbo.Suppliers.PostalCode,

dbo.Categories.CategoryName,dbo.Categories.Description

FROM

dbo.Products

INNER JOIN dbo.Suppliers ON dbo.Products.SupplierID = dbo.Suppliers.SupplierID

INNER JOIN dbo.Categories ON dbo.Categories.CategoryID=dbo.Products.CategoryID

WHERE

dbo.Products.ProductName=''

AND dbo.Suppliers.ContactName=''

AND dbo.Categories.CategoryName=''

基本的连表查询就可以了。而且像这样的查询一般会有三个组合框，只需要后台动态组合条件就行了，例如如果只选择了dbo.Products.ProductName和dbo.Suppliers.ContactName那么我们就可以动态组合成：

SELECT

dbo.Products.ProductName,dbo.Products.QuantityPerUnit,dbo.Products.UnitPrice,dbo.Products.UnitsInStock,

dbo.Products.UnitsOnOrder,dbo.Products.ReorderLevel,dbo.Products.Discontinued,

dbo.Suppliers.CompanyName,dbo.Suppliers.ContactName,dbo.Suppliers.Country,dbo.Suppliers.Fax,

dbo.Suppliers.HomePage,dbo.Suppliers.Phone,dbo.Suppliers.PostalCode,

dbo.Categories.CategoryName,dbo.Categories.Description

FROM

dbo.Products

INNER JOIN dbo.Suppliers ON dbo.Products.SupplierID = dbo.Suppliers.SupplierID

INNER JOIN dbo.Categories ON dbo.Categories.CategoryID=dbo.Products.CategoryID

WHERE

dbo.Products.ProductName=''

AND dbo.Suppliers.ContactName=''

但是问题不是这样的，现在商品表是主表，商品名称是必须选择的（开发的时候当然可以默认给一个选择值），而供货商和商品类型是可选可不选的。要求不管怎么查都要有数据都要显示数据，因为商品名称必选，所以如果查询条件没有供货商和商品类型结果只显示和商品相关的信息就可以了，其他的能显示则显示没有就显示为空就可以了。

问题就是这样的，我当时没有理解对，以为是这种情况：例如在商品表中有个供货商id，但是在供货商表中没有找到，那么用inner join连接肯定不显示任何信息，只要用left join以商品表为主表就可以了。当时我将她的问题理解成inner join和left join、right join的区别了，所有也没有正确的解答。现在想起了这个问题，不妨找出答案。

（在此说明一下：到这里有些朋友会问，做这样的事情没有意义，实际问题中有这样的问题吗。原因是这里用的库有些不太合适，原问题是牵扯到一个人的基本信息表，一个学历表，一个就业履历表等相关人员信息表，这个时候如果只单独看基本还是有意义的）

其实我们可以这样思考：显示结果是从三个表中组合的，其实就是组合列的问题（其实我们经常组合列，inner join和left join、right join可以起到组合列的作用，而组合行我们经常用union 、union all），既然如此我们不妨就按照left join思想将这个问题转化为left join的形式，我们将显示结果分为三类分别在三个表中就可以了，当然本来就是从三个表中查询出来的结果，只是三个表没有关联，我们何不先关联成虚表再用left join连接呢？

SELECT

A.ProductName,A.QuantityPerUnit,A.UnitPrice,A.UnitsInStock,

A.UnitsOnOrder,A.ReorderLevel,A.Discontinued ,

B.CompanyName,B.ContactName,B.Country,B.Fax,

B.HomePage,B.Phone,B.PostalCode,

C.CategoryName,C.Description

FROM

(

--商品

SELECT dbo.Products.ProductID,

dbo.Products.ProductName,dbo.Products.QuantityPerUnit,dbo.Products.UnitPrice,dbo.Products.UnitsInStock,

dbo.Products.UnitsOnOrder,dbo.Products.ReorderLevel,dbo.Products.Discontinued

FROM

dbo.Products

WHERE

dbo.Products.ProductName='Chai'

) AS A

LEFT JOIN

(

--供货商

SELECT dbo.Products.ProductID,

dbo.Products.ProductName,dbo.Products.QuantityPerUnit,dbo.Products.UnitPrice,dbo.Products.UnitsInStock,

dbo.Products.UnitsOnOrder,dbo.Products.ReorderLevel,dbo.Products.Discontinued,

dbo.Suppliers.CompanyName,dbo.Suppliers.ContactName,dbo.Suppliers.Country,dbo.Suppliers.Fax,

dbo.Suppliers.HomePage,dbo.Suppliers.Phone,dbo.Suppliers.PostalCode

FROM

dbo.Products

INNER JOIN dbo.Suppliers ON dbo.Products.SupplierID = dbo.Suppliers.SupplierID

WHERE

dbo.Products.ProductName='Chai'

AND dbo.Suppliers.ContactName='Charlotte Cooper'

)AS B

ON A.ProductID=B.ProductID

LEFT JOIN

(

--种类

SELECT dbo.Products.ProductID,

dbo.Products.ProductName,dbo.Products.QuantityPerUnit,dbo.Products.UnitPrice,dbo.Products.UnitsInStock,

dbo.Products.UnitsOnOrder,dbo.Products.ReorderLevel,dbo.Products.Discontinued,

dbo.Categories.CategoryName,dbo.Categories.Description

FROM

dbo.Products

INNER JOIN dbo.Categories ON dbo.Categories.CategoryID=dbo.Products.CategoryID

WHERE

dbo.Products.ProductName='Chai'

AND dbo.Categories.CategoryName='Beverages'

) AS C

ON A.ProductID=C.ProductID

这样，我们商品名称必选，另外两个条件任意组合就可以了。