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?

Answer 1

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.