Getting values from column B that do not have a corresponding value in column A

I have a table with two columns in MSSQL.

column id1 and column id2 with any relation .

Example data:

id1   id2
12    13
13    14 
12    14 
13    15

Here 12 in id1 is has relation with 13 in id2, here 13 in id1 is has relation with 14 in id2 such way..

How can I write an SQL Query so that when I pass 12, I get the value(s) from column 开发者_JS百科id2 who does not have any relation with 12?

(In this case, the answer would be 15)

You can use a subquery in the where clause.

SELECT id2 FROM `test1`  
WHERE id2 NOT IN (SELECT id2 FROM `test1` WHERE id1 = 12)

If you any NULLs in id2, then NOT IN will always fail

You have to use EXISTS or EXCEPT for consistency and correct results

SELECT tr.id2
FROM MyTable tr
WHERE NOT EXISTS (SELECT * FROM MyTable tl
    WHERE tl.id1 = 12 AND tl.id2 = tr.id2)

Or

SELECT id2
FROM MyTable
EXCEPT
SELECT id2
FROM MyTable
WHERE id1 = 12

I think it's as simple as:

select distinct id2 
from table 
where id2 not in (select id2 from table where id1 = 12)

I.e. you want to find all id2 values, such that no row with that id2 value has an id1 of 12