I need to know how to return a default row if no rows exist in a table. What would be the best way to do this? I’m only returning a single column from this particular table to get its value.
Edit: This would be SQL Server.
Advertisement
Answer
One approach for Oracle:
x
SELECT val
FROM myTable
UNION ALL
SELECT 'DEFAULT'
FROM dual
WHERE NOT EXISTS (SELECT * FROM myTable)
Or alternatively in Oracle:
SELECT NVL(MIN(val), 'DEFAULT')
FROM myTable
Or alternatively in SqlServer:
SELECT ISNULL(MIN(val), 'DEFAULT')
FROM myTable
These use the fact that MIN()
returns NULL
when there are no rows.