conditional join with input value in postgreslq function

Question

I have three tables:

create table id_table (
    id integer
);

insert into id_table values (1),(2),(3);

create table alphabet_table (
    id integer,
    letter text
);

insert into alphabet_table values (1,'a'),(2,'b'),(3,'c');

create table greek_table (
    id integer,
    letter text
);

insert into greek_table values (1,'alpha'),(2,'beta');

I like to create a function that join id_table with either alphabet_table or greek_table on id. The choice of the table depends on an input value specified in the function. I wrote:

CREATE OR REPLACE FUNCTION choose_letters(letter_type text)
  RETURNS table (id integer,letter text) AS $$
BEGIN
  RETURN QUERY
  select t1.id,
         case when letter_type = 'alphabet' then t2.letter else t3.letter end as letter
  from id_table t1,
       alphabet_table t2 ,
       greek_table t3 
  where t1.id = t2.id and t1.id = t3.id;

END;
$$LANGUAGE plpgsql;

I ran select choose_letter('alphabet'). The problem with this code is that when id_table joins with alphabet_table, it does not pick up id, No 3. It seems that inner joins are done for both alphabet_table and greek_table (so it only picks up the common ids, 1 and 2). To avoid this problem, I wrote:

CREATE OR REPLACE FUNCTION choose_letters(letter_type text)
  RETURNS table (id integer, letter text) AS $$
BEGIN
  RETURN QUERY
  select t1.id,
         case when letter_type = 'alphabet' then t2.letter else t3.letter end as letter
  from id_table t1
  left join alphabet_table t2 on t1.id=t2.id
  left join greek_table t3 on t1.id=t3.id
  where t2.letter is not null or t3.letter is not null;

END;
$$LANGUAGE plpgsql;

Now it pick up all the 3 ids when id_table and alphabet_table join. However, When I ran select choose_letter('greek'). The id no. 3 appears with null value in letter column despite the fact that I specified t3.letter is not null in where clause.

What I'm looking for is that when I ran select choose_letters('alphabet'), the output needs to be (1,'a'), (2,'b'),(3,'c'). select choose_letters('greek') should produce (1,'alpha'),(2,'beta). No missing values nor null. How can I accomplish this?

lospejos · Accepted Answer · 2023-07-03 14:05:54Z

15

Learn to use proper, explicit JOIN syntax. Simple rule: Never use commas in the FROM clause.

You can do what you want with LEFT JOIN and some other logic:

select i.id,
       coalesce(a.letter, g.letter) as letter
from id_table i
left join alphabet_table a
     on i.id = a.id and letter_type = 'alphabet'
left join greek_table g
     on i.id = g.id and letter_type <> 'alphabet'
where a.id is not null or g.id is not null;

The condition using letter_type needs to be in the on clauses. Otherwise, alphabet_table will always have a match.

edited Jul 3, 2023 at 14:05

lospejos

1,9864 gold badges19 silver badges38 bronze badges

answered May 9, 2017 at 1:29

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

6 Comments

midtownguru Over a year ago

This is almost the same as the second code in the question. when letter_type <> 'alphabet', it shows null value in id no.3

Gordon Linoff Over a year ago

@midtownguru . . . There is an important difference. But thank you for pointing out that I had left out the where clause.

midtownguru Over a year ago

I got the left join logic. You can see my last code in the question. Interestingly to me, coalesce and letter_type being on condition solved the issue.

midtownguru Over a year ago

I know it's a bad code from my colleague. I need to develop mine on top of it, like it or not. So I put that ugly code on purpose. Anyway, thanks for your answer.

user2703728 Over a year ago

@GordonLinoff I am trying to understand something similar as I am working with big data, but for a personal learning journey. If I were to query with a join, and a secondary condition on the join relating only to the first table, will it avoid looking at the table at all if the secondary condition is false. For example, 'Select * from tabl1 t1 full outer join tabl2 t2 on tb1.id = tb2.tb1_id and t1.con = true' If tabl1.con = false then tb2 will not be looked at whatsoever?

|

Davos · Accepted Answer · 2018-09-28 07:01:59Z

Gordon Linoff's answer above is certainly correct, but here is an alternative way to write the code. It may or may not be better from a performance perspective, but it is logically equivalent. If performance is a concern you'd need to run EXPLAIN ANALYZE on the query an inspect the plan, and do other profiling.

Some good parts about this are the inner join makes the join clause and where clause simpler and easier to reason about. It's also more straight forward for the execution engine to parallelize the query.

On the downside it looks like code duplication, however, the DRY principle is often misapplied to SQL. Repeating code is less important than repeating data reads. What you're aiming to do is not scan the same data multiple times.

If there is no index on the fields you are joining or the letter_type then this could end up doing a full table scan twice, and be worse. If you do have the indexes then it can do it with index range scans nicely.

SELECT 
    i.id,
    a.letter
FROM id_table i
INNER JOIN alphabet_table a
    ON i.id = a.id
WHERE letter_type = 'alphabet'
UNION ALL
SELECT 
    i.id,
    g.letter
FROM id_table i 
INNER JOIN greek_table g
    ON i.id = g.id
WHERE letter_type <> 'alphabet'

Kiran Kumar · Accepted Answer · 2017-05-09 01:35:08Z

0

The first problem is your tables or not structured properly, You would have created single table like char_table (id int, letter text, type text) type will specify whether it is alphabet or Greek.

Another solution is you can write two SQL queries one in if condition other one is in else part

answered May 9, 2017 at 1:35

Kiran Kumar

1,0517 silver badges20 bronze badges

Collectives™ on Stack Overflow

conditional join with input value in postgreslq function

3 Answers 3

6 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related