CODEWITHSUNDEEP
sqlpostgresqljoininner-join
Michael Linker
Michael Linker

Reputation: 337

Inner join of the same table without duplicates for certain values

CREATE TABLE my_table (
  id INTEGER PRIMARY KEY,
  key BOOLEAN NOT NULL
);

INSERT INTO my_table VALUES (10, true);
INSERT INTO my_table VALUES (11, true);
INSERT INTO my_table VALUES (12, true);
INSERT INTO my_table VALUES (20, false);
INSERT INTO my_table VALUES (21, false);

I wanted only values with id equal to 11 or 12:

SELECT my_table_1.id,
       my_table_1.key,
       my_table_2.id,
       my_table_2.key
FROM my_table AS my_table_1
  INNER JOIN my_table AS my_table_2 
    ON my_table_1.id < my_table_2.id
      AND my_table_1.key = my_table_2.key
      AND my_table_1.id IN (11, 12)

Instead of the full result:

my_table_1.id my_table_1.key my_table_2.id my_table_2.key
10 t 11 t
10 t 12 t
11 t 12 t

I got a wrong partial result:

my_table_1.id my_table_1.key my_table_2.id my_table_2.key
11 t 12 t

I figured out how to get the full result with double IN:

SELECT my_table_1.id,
       my_table_1.key,
       my_table_2.id,
       my_table_2.key
FROM my_table AS my_table_1
  INNER JOIN my_table AS my_table_2 
    ON my_table_1.id < my_table_2.id
      AND my_table_1.key = my_table_2.key
      AND (my_table_1.id IN (11, 12) OR my_table_2.id IN (11, 12))

Which gives me the correct result.

Another example with id IN (21):

SELECT my_table_1.id,
       my_table_1.key,
       my_table_2.id,
       my_table_2.key
FROM my_table AS my_table_1
  INNER JOIN my_table AS my_table_2 
    ON my_table_1.id < my_table_2.id
      AND my_table_1.key = my_table_2.key
      AND (my_table_1.id IN (21) OR my_table_2.id IN (21))

This also gives the correct result:

my_table_1.id my_table_1.key my_table_2.id my_table_2.key
20 f 21 f

Without the second IN, I would have got an empty result.

How can I improve this query?

I don't like using the IN twice.

On the small amount data there's no problem. A problem appears on the real data, when e.g. 10000 ids is queried, and instead of one monstrous list of ids, there are two monstrous lists of ids.

Upvotes: 0

Views: 120

Answers (1)

Harshini
Harshini

Reputation: 77

For Reduce multiple list if Ids Can use CTE like this


    WITH IDs AS (
        SELECT id FROM my_table WHERE id IN (11, 12) 
    )
    SELECT my_table_1.id,
           my_table_1.key,
           my_table_2.id,
           my_table_2.key
    FROM my_table AS my_table_1
      INNER JOIN my_table AS my_table_2
        ON my_table_1.id < my_table_2.id
          AND my_table_1.key = my_table_2.key
          AND (my_table_1.id IN (SELECT id FROM IDs)
               OR my_table_2.id IN (SELECT id FROM IDs))
    ORDER BY my_table_2.id

Upvotes: 1

Related Questions