Lohith Lohith - 7 months ago 11
SQL Question

Joining two tables and returning the bigger value of one column

Recently I came up with the scenario where I have two tables named emp1 and emp2 and it has following columns and table looks like below

table: emp1

dno sal
10 1000
20 2000
30 3000


table: emp 2

dno sal
10 4000
20 5000
30


and the output table is like

table: output
dno sal
10 4000
20 5000
30 3000

Answer

You need to join the two tables, then use the greatest() function to return the bigger of the two values.

As the salary can be null you need to take that into account using the coalesce() function:

select t1.dno, greatest(coalesce(t1.sal,0), coalesce(t2.sal,0)) as sal
from emp1 t1
  join emp2 t2 on t1.dno = t2.dno;

SQLFiddle example: http://sqlfiddle.com/#!15/bca1b/1