SQL 如何有效地使用 where something != something？

Question

所以我有3张 table ，

Companies (company_id, company_name)
Stock_Exchanges(stock_exchange_id, stock_exchange_name, stock_exchange_manager)
Stock_Exchanges_Companies(stock_exchange_id, company_id)

我正在尝试选择不在某个 stock_exchange 上的所有公司.

到目前为止我有这个:

SELECT COMPANY_NAME
  FROM COMPANIES c
  LEFT OUTER JOIN STOCK_EXCHANGES_COMPANIES sec 
    ON c.COMPANY_ID = sec.COMPANY_ID
  LEFT OUTER JOIN STOCK_EXCHANGES se 
    ON sec.STOCK_EXCHANGE_ID = se.STOCK_EXCHANGE_ID
 WHERE sec.COMPANY_ID != se.COMPANY_ID;

当我使用 WHERE子句但是它返回零行。任何帮助将不胜感激!

Answer 1

您根据匹配的 stockExchangeID 加入表，然后过滤到没有匹配的 stockExchangeID 的行，这就是为什么没有行的原因。是的，它们是左连接，因此如果没有匹配，您会从连接的右侧部分获得空值，但像这样设置它似乎违反直觉。

假设您想要所有没有您的特定股票交易所的公司(包括没有证券交易所的公司)，失去第一个 where 条件并执行以下操作:

SELECT COMPANY_NAME
FROM COMPANIES c
LEFT OUTER JOIN STOCK_EXCHANGES_COMPANIES sec 
    ON c.COMPANY_ID = sec.COMPANY_ID
LEFT OUTER JOIN STOCK_EXCHANGES se 
    ON sec.STOCK_EXCHANGE_ID = se.STOCK_EXCHANGE_ID
WHERE se.STOCK_EXCHANGE_ID != [idhere];

要选择有证券交易所但不是特定证券交易所的公司，请添加一个条件以检查它是否已加入 STOCK_EXCHANGES

SELECT COMPANY_NAME
FROM COMPANIES c
LEFT OUTER JOIN STOCK_EXCHANGES_COMPANIES sec 
    ON c.COMPANY_ID = sec.COMPANY_ID
LEFT OUTER JOIN STOCK_EXCHANGES se 
    ON sec.STOCK_EXCHANGE_ID = se.STOCK_EXCHANGE_ID
WHERE se.STOCK_EXCHANGE_ID != [idhere]
    AND se.STOCK_EXCHANGE_ID IS NOT NULL;