sql to return 0 when no row found

I like the following select to return 0 when no row found.  Thanks.
select emp_id from emp where emp_id =30;

emp_id  
10
20
ewang1205Asked:
Who is Participating?
 
LowfatspreadConnect With a Mentor Commented:
what are you actually trying to do?

Select coalesce(emp_id,0) as empid
 from sysibm.sysduymmy1 as x
 left outer join (Select 'Y' as Y,emp_id from emp where emp_id=30) as Y
   on X.Y=Y.Y

will return 0 when the emp_id doesn't exist...

are you really looking for the WHERE EXISTS / NOT EXISTS syntax?


0
 
cyberstalkerConnect With a Mentor Commented:
If you want to only ever return one row, you can do it like this:

Using MAX will return the maximum value in a set (which will be the same as your original query, or null if no rows are found. IFNULL returns the first non-null value. Which is the result of MAX, or the 0 passed as second parameter.
SELECT IFNULL(MAX(emp_id), 0) AS emp_id FROM emp WHERE emp_id =30;

Open in new window

0
All Courses

From novice to tech pro — start learning today.