SQL连接:如何选择何时值不在一组值中 [英] SQL join: how to select when value not in a set of values

问题描述

我有两个表，table1和table2.表1与表2具有一对多关系(表1中的一行映射到表2中的许多行). table2中有一个称为代码的字段.如果table2中的代码值均不等于某些值集(例如1、2和3)，那么我想选择table1中的值.我不确定如何编写这种联接.
假设table1中的主键称为id，而它映射到的外键称为.您能告诉我如何编写这种联接吗?

I have two tables, table1 and table2. Table1 is in a one to many relationship with table2 (one row in table1 maps to many rows in table2). There is a field in table2 called code. If none of the values of code in table2 equal some set of values (say 1, 2, and 3), I want to select the value in table1. I'm not sure how to write this kind of join.
Assume the primary key in table1 is called id and the foreign key to which it maps is called did. Can you tell me how to write this kind of join?

推荐答案

这称为最简单的实现是:

SELECT * FROM table1
WHERE NOT EXISTS (SELECT 1 FROM table2
                  WHERE  table2.did = table1.id
                    AND  table2.code in (1,2,3))

或者，使用外部联接(我不是100％肯定会行得通，因为我总是自己对反联接使用NOT EXIST语法):

or, using outer join (I'm not 100% sure this will work, as I always use NOT EXIST syntax myself for antijoins):

SELECT  table1.*
FROM    table1
LEFT OUTER JOIN
        table2
ON      table1.id = table2.did
  AND  table2.code in (1,2,3)
WHERE   table2.did is NULL

这篇关于SQL连接:如何选择何时值不在一组值中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！