sql - 如果在另一个表中找不到其 ID，我如何返回一行？

Question

我在 MS SQL 2008 数据库中有 2 个表，Listings 和 ListingType，我想创建一个 select 语句，该语句将为我提供 Listing 中在 ListingType 表中没有 ListingID 的所有行。

我什至不知道如何开始这个声明。

示例 SQL 语句 - 比我解释的要多得多，但是您应该能够从中得到我要问的内容。

SELECT     Listing.Title, Listing.MLS, COALESCE (Pictures.PictureTH, '../default_th.jpg') AS PictureTH, COALESCE (Pictures.Picture, '../default.jpg') AS Picture, Listing.ID, 
                      Listing.Description, Listing.Lot_Size, Listing.Building_Size, Listing.Bathrooms, Listing.Bedrooms, Listing.Address1, Listing.Address2, 
                      Listing.City, Locations.Abbrev, Listing.Zip_Code, Listing.Price, Listing.Year_Built, ListingTypeMatrix.ListingTypeID
FROM         Listing INNER JOIN
                      Locations ON Listing.State = Locations.LocationID LEFT OUTER JOIN
                      ListingTypeMatrix ON Listing.ID = ListingTypeMatrix.ListingID LEFT OUTER JOIN
                      Pictures ON Listing.ID = Pictures.ListingID
WHERE     (ListingTypeMatrix.ListingTypeID = '4') AND 
          ((Pictures.ID IS NULL) OR (Pictures.ID =
                          (SELECT     MIN(ID)
                            FROM          Pictures
                            WHERE      (ListingID = Listing.ID))))

ListingTypeMatrix.ListingTypeID = '4' 是我不知道改成什么的部分，因为不会有记录。

Answer 1

使用不存在

SELECT t.*
  FROM LISTING t
 WHERE NOT EXISTS(SELECT NULL
                    FROM LISTINGTYPE lt
                   WHERE lt.listingid = t.listingid)

使用 NOT IN

SELECT t.*
  FROM LISTING t
 WHERE t.listingid NOT IN (SELECT lt.listingid
                             FROM LISTINGTYPE lt)

使用 LEFT JOIN/IS NULL

   SELECT t.*
     FROM LISTING t
LEFT JOIN LISTINGTYPE lt ON lt.listingid = t.listingid
    WHERE lt.listingid IS NULL

概括
Quote :

In SQL Server, NOT EXISTS and NOT IN predicates are the best way to search for missing values, as long as both columns in question are NOT NULL. They produce the safe efficient plans with some kind of an Anti Join.

LEFT JOIN / IS NULL is less efficient, since it makes no attempt to skip the already matched values in the right table, returning all results and filtering them out instead.