CODEWITHSUNDEEP
sql
erli
erli

Reputation: 408

SQL select rows with unique ID's

I have two separte tables that have the same columns but some different data:

Table 1

ContextID | Property1 | Property2
1         | Hello     | Kitty
2         | Power     | Rangers
3         | Foo       | Bar

Table 2

ContextID | Property1 | Property2
4         | Scooby    | Doo
2         | Power     | NotRangers
3         | Foo       | NotBar

I want to select rows from table 1 where the context ID does not exist in table 2, regardless of the fact that the rest of table 2 might not match table 1. For ex, I want my query to return:

ContextID | Property1 | Property2
1         | Hello     | Kitty

This is because ContextID 2 and 3 exist in Table 2. How can I write a query that can do this? I was able to write this:

SELECT ContextID from Table1 EXCEPT SELECT ContextID from Table2;

However, this only returns the ContextID column. How can I grab the entire row (eg. ContextID along with Property1 and Property2?)

Upvotes: 0

Views: 1348

Answers (3)

The Impaler
The Impaler

Reputation: 48770

You can use an anti-join:

select a.*
from table_1 a
left join table_2 b on b.ContextID = a.ContextID
where b.ContextID is null

Result:

contextid  property1  property2 
---------- ---------- --------- 
1          Hello      Kitty

See running example at DB Fiddle.

Upvotes: 2

VLRoyrenn
VLRoyrenn

Reputation: 786

You would want to use a anti-semijoin, with an "NOT EXISTS" clause in your WHERE.

SELECT * FROM t1 
WHERE NOT EXISTS (
    SELECT 1 from t2 WHERE t1.ContextID = t2.ContextID
)

It makes it so any row that matches the t1.ContextID = t2.ContextID clause gets omitted, but only the data from t1 is retrieved.

Upvotes: 0

Efthymios Kalyviotis
Efthymios Kalyviotis

Reputation: 969

What I would do is:

SELECT t1.ContextID, t1.Property1, t1.Property2
FROM Table1 as t1 
WHERE t1.ContextID NOT IN (SELECT ContextID From Table2);

Upvotes: 1

Related Questions