Question 328406 in PostgreSQL Question posted in
The official documentation can be found here.

select exclude rows with same key using postgreSQL

I have 2 tables:

video_real

 device_id | video_definition 

         1 |              360
         1 |              480
(2 rows)

video_all

 device_id | video_definition 

         1 |              360
         1 |              480
         1 |              540
         1 |              720
         1 |             1080

I want to return

device_id | video_definition 

         1 |              540
         1 |              720
         1 |             1080

How do I do this?

Tags:

2

Answers


  1. 0

    This is pretty much the definition of an "anti-join". You can do:

    select a.*
from video_all a
left join video_real r on r.device_id = a.device_id
                      and r.video_definition = a.video_definition
where r.device_id is null
    Login or Signup to reply.
  2. 0

    You can use the EXCEPT operator. This compares the results from both queries and removes any matching values.

    A more thorough explanation can be found here.

    Schema (PostgreSQL v15)

    CREATE TABLE video_real (
  "device_id" INTEGER,
  "video_definition" INTEGER
);

INSERT INTO video_real
  ("device_id", "video_definition")
VALUES
  ('1', '360'),
  ('1', '480');

CREATE TABLE video_all (
  "device_id" INTEGER,
  "video_definition" INTEGER
);

INSERT INTO video_all
  ("device_id", "video_definition")
VALUES
  ('1', '360'),
  ('1', '480'),
  ('1', '540'),
  ('1', '720'),
  ('1', '1080');

    Query #1

    SELECT a.device_id, a.video_definition FROM video_all a
EXCEPT
SELECT b.device_id, b.video_definition FROM video_real b;
    device_id video_definition
    1 1080
    1 720
    1 540

    View on DB Fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search