CODEWITHSUNDEEP
sqlpostgresql
gudge
gudge

Reputation: 1083

Check if a value form one is in the column of another table

I have the following piece of code. 

CREATE TABLE "s"."t1"
(
    "c1" BigSerial PRIMARY KEY,
    "c2" BigInt NOT NULL
)
WITH (OIDS=FALSE);

INSERT INTO s.t1 (c2) VALUES (100);
INSERT INTO s.t1 (c2) VALUES (200);

CREATE TABLE "s"."t2"
(
    "c1" BigSerial PRIMARY KEY,
    "c2" BigInt NOT NULL,
    "c3" BigInt NOT NULL,
    CONSTRAINT "fk1" FOREIGN KEY (c2) REFERENCES s.t1 (c1) ON DELETE SET NULL ON UPDATE CASCADE
)
WITH (OIDS=FALSE);

INSERT INTO s.t2 (c2, c3) VALUES (1, 2000);
INSERT INTO s.t2 (c2, c3) VALUES (1, 3000);

SELECT t1.c1
FROM s.t1 t1 
WHERE t1.c1 NOT IN 
(SELECT DISTINCT t1.c1 FROM s.t1 t1
 INNER JOIN s.t2 AS t2 on t2.c2 = t1.c1);

The output is 2.

I am trying to look at column of t1 (c1) and check if there are any values in c1 which are not part of t2(c2).

Is there a better way to do it with count or outer join ?

Upvotes: 0

Views: 56

Answers (2)

potashin
potashin

Reputation: 44601

This problem is called an anti-join pattern, you can try with left join and is null check, for example:

SELECT t1.c1
FROM s.t1 t1 
LEFT JOIN s.t2 t2 ON t2.c2 = t1.c1
WHERE t2.c2 IS NULL

SQLFiddle

Upvotes: 0

klin
klin

Reputation: 121834

Use except:

select c1 from t1
except
select c2 from t2;

Upvotes: 1

Related Questions