'NOT IN'和'IN'子句返回相同的？ [英] 'NOT IN' and 'IN' clause return the same?

问题描述

大家好，我是SQL新手，我很难理解为什么我的IN和NOT IN子句在我写查询时会返回相同的结果。



我有三个表：TableA，TableB和TableC。第四张表有我们的植物表。我试图从TableC返回所有未链接到有效Plants.ID的数据

Hi Everyone, I'm very new to SQL and am having difficulty understanding why my IN and NOT IN clause is returning the same results when I write my query.

I have three tables: TableA,TableB, and TableC. A fourth table has a table of our plants. I am trying to return all the data from TableC that isn't linked to a valid Plants.ID

SELECT TableC.* FROM TablePlants
  JOIN TableA ON TablePlants.ID = TableA.Plant_ID
  JOIN TableB ON TableA.ID = TableB.TableA_ID
  JOIN TableC ON TableB.ID = TableC.TableB_ID
   WHERE EXISTS( select * FROM TableA 
    WHERE TableA.Plant_ID NOT IN (select TablePlants.ID FROM TablePlants))

无论我是'NOT IN'还是'IN'，它都只返回那些在TablePlants中的条目。



我缺少什么？



非常感谢您的帮助！

Whether I have 'NOT IN' or just 'IN' it returns only those entries which are IN TablePlants.

What am I missing?

Thanks a ton for any help!

推荐答案

SELECT
  TableC.ID
 --,TableC.SomeColumn1
 --,TableC.SomeColumn2
FROM TableC LEFT JOIN TableB ON TableB.ID = TableC.TableB_ID
 LEFT JOIN TableA ON TableA.ID = TableB.TableA_ID
WHERE TableA.Plant_ID NOT IN (SELECT ID FROM TablePlants)
 --AND TableA.Plant_ID IS NOT NULL --eventually

我鼓励你禁止 SELECT * 来自你的词汇，因为这是一个非常糟糕的做法:)



如果你对你的外键施加一些约束强制执行，这种情况就不会再发生了。



例如

I encourage you to ban SELECT * from your vocabulary, as it is a quite bad practice :)

If you put some constraint enforcements on your foreign keys, this situation would not happen anymore.

For example

ALTER TABLE TableA
 WITH CHECK ADD CONSTRAINT FK_TableA_TablePlants
 FOREIGN KEY (Plant_ID)
 REFERENCES TablePlants (ID)
 ON UPDATE CASCADE
 ON DELETE CASCADE --SET DEFAULT could be an option here
GO

这篇关于'NOT IN'和'IN'子句返回相同的？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！