I have two tables.

Table 1:

A   B 
1   'A'
2   'B'
3   'C'

Table 2:

   A   C
   2   15
   3   20

I'm using the following query:

SELECT table1.A, table1.B, table2.C
FROM table1
LEFT JOIN table2
ON table1.A = table2.A

The problem arises in the output which is:

    A   B   C
    1   'A'  null
    2   'B'  15
    3   'C'  20

Is there a way to get the null as 0, as I have to do an order by on table2.C. Any help is appreciated.

My expected output is:

    A   B   C
    3  'C'  20
    2  'B'  15
    1  'A'  0 

ps: I tried with IFNULL(table2.C, 0). Didn't work.

  • 2
    I don't know which DBMS you use, but there is COALESCE(table2.C, 0) on PostgreSQL – TurboGraphxBeige Apr 23 '19 at 16:07
  • Oh that worked. Thank you so much!! But the row shows up at the beginning. Do you have a suggestion, to put it at the bottom. Am i doing something wrong. – Ron Apr 23 '19 at 16:12
  • 1
    Check out ORDER BY – TurboGraphxBeige Apr 23 '19 at 16:16
  • I did order by table2.c but it works for the other columns not this one. – Ron Apr 23 '19 at 16:25
  • Ok I got it, I renamed the column table2.C after the COALESCE and then did order by on that. That worked. Thank you so much for the help. – Ron Apr 23 '19 at 16:37

This may be the most efficient solution:

SELECT A, t1.B, t2.C
FROM   table1 t1
LEFT   JOIN table2 t2 USING (A)

NULL values are sorted last by default. You can reverse that with NULLS FIRST. See:

| improve this answer | |

Not the answer you're looking for? Browse other questions tagged or ask your own question.