Postgres Select from a Table Based On Query Result

Question

I have two tables with identical columns, in an identical order. I have a desire to join across one of the two tables, depending on a subquery condition. For example, assume I have the following schema:

CREATE TABLE b (
    bid SERIAL PRIMARY KEY,
    cid INT NOT NULL
);

CREATE TABLE a1 (
    aid SERIAL PRIMARY KEY,
    bid INT NOT NULL REFERENCES b
);

CREATE TABLE a2 (
    aid SERIAL PRIMARY KEY,
    bid INT NOT NULL REFERENCES b
);

I would like a query, that performs a join across either a1 or a2 based on some condition. Something like:

WITH z AS (
  SELECT cid, someCondition FROM someTable
)
SELECT *
FROM CASE z.someCondition THEN a1 ELSE a2 END
JOIN b USING (bid)
WHERE cid = (SELECT cid FROM z);

However, the above doesn't work. Is there some way to conditionally join across a1 or a2, depending on some boolean condition stored in table z?

wildplasser · Accepted Answer · 2015-07-15 14:14:36Z

5

If the conditions are exclusive (I expect they are): just do both queries and UNION ALL them, with the smart union construct:

WITH z AS (
  SELECT cid
        , (cid %3) AS some_condition -- Fake ... 
        FROM  b
  )
SELECT *
  FROM a1
  JOIN b USING (bid)
 WHERE EXISTS( SELECT * FROM z
        WHERE some_condition = 1 AND cid = b.cid )
UNION ALL
SELECT *
  FROM a2
  JOIN b USING (bid)
 WHERE EXISTS( SELECT * FROM z
        WHERE some_condition = 2 AND cid = b.cid )
        ;

A somewhat different syntax to do the same:

WITH z AS (
  SELECT cid
        , (cid %3) AS some_condition 
        FROM  b
)
SELECT *
  FROM a1
  JOIN b ON a1.bid = b.bid
        AND EXISTS( SELECT * FROM z
        WHERE some_condition = 1 AND cid = b.cid )
UNION ALL
SELECT *
  FROM a2
  JOIN b ON a2.bid = b.bid
        AND EXISTS( SELECT * FROM z
        WHERE some_condition = 2 AND cid = b.cid )
        ;

answered Jul 15, 2015 at 14:14

wildplasser

44.5k9 gold badges72 silver badges116 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Jeff G Over a year ago

That is an interesting idea. In truth, table a1 is a superset of table a2, and the real question I am trying to ask is: IF EXISTS in *a2* OR (condition AND EXISTS in *a1*), which seems like a great fit for your proposal. I may even move the UNION ALL into another subquery, so I can perform the join only once.

wildplasser Over a year ago

Performing the JOIN only once is generally a bad idea: In my example the two JOINs can make of indexes. After your UNION the indexes will not be of any use. (BTW: joins are cheap, it is not 1985 anymore) Note: there is no IF in SQL. You don't need it: just move the condition to the WHERE clause.

Jeff G Over a year ago

I don't think the indexes argument matters to me... in the real tables, there will either be zero or one matches from table a1 or a2, and I am taking that one result and joining up a chain of 4 tables. I don't want to duplicate the same logic multiple times. EXAMPLE:

WITH z AS (...), y AS (SELECT bid FROM a1 WHERE aid = 7 AND some_condition UNION ALL SELECT bid FROM a2 WHERE aid = 7 AND NOT some_condition) SELECT COUNT(1)::INT::BOOLEAN FROM b WHERE bid = (SELECT * FROM y) AND cid = (SELECT cid FROM z)

klin · Accepted Answer · 2015-07-15 00:54:31Z

3

SQL syntax does not allow conditional joins. Probably the simplest way to achieve a similar effect is to use a dynamic query in a plpgsql function, which may look like this:

create function conditional_select(acid int, some_condition boolean)
returns table (aid int, bid int, cid int)
language plpgsql as $$
declare
    tname text;
begin
    if some_condition then tname = 'a1';
    else tname = 'a2';
    end if;

    return query execute format ($fmt$
        select a.aid, b.bid, b.cid 
        from %s a
        join b using(bid)
        where cid = %s;
        $fmt$, tname, acid);
end $$;


select * from conditional_select(1, true)

answered Jul 15, 2015 at 0:54

klin

123k15 gold badges241 silver badges263 bronze badges

1 Comment

Jeff G Over a year ago

I personally prefer @wildplasser's answer, simply because it doesn't require a custom database function. However, this is still a great approach.

Patrick · Accepted Answer · 2015-07-15 01:25:10Z

1

If, like in your example, you have only a few columns that you want to output, you can use the CASE statement for every column:

SELECT CASE z.someCondition THEN a1.aid ELSE a2.aid END AS aid,
       CASE z.someCondition THEN a1.bid ELSE a2.bid END AS bid
FROM b
JOIN a1 ON a1.bid = b.bid
JOIN a2 ON a2.bid = b.bid
JOIN someTable z USING (cid);

Depending on the size of tables a1 and a2 and how many columns you have to output, this may or my not be faster than Klin's solution with a function, which is inherently slower than plain SQL and even more so because of the dynamic query. Given that z.someCondition is a boolean value already, the CASE evaluation will be very fast. Small tables + few columns = this solution; large tables + many columns = Klin's solution.

edited Jul 15, 2015 at 1:25

answered Jul 15, 2015 at 1:09

Patrick

33k7 gold badges73 silver badges102 bronze badges

Collectives™ on Stack Overflow

Postgres Select from a Table Based On Query Result

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related