gudge gudge - 5 months ago 8
SQL Question

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 ?

Answer

Use except:

select c1 from t1
except
select c2 from t2;
Comments