CODEWITHSUNDEEP
sqloracle-database
Nidheesh
Nidheesh

Reputation: 4562

Return default row without NOT EXISTS

I have a query which returns a single row 

SELECT
  'abcde' type,
  NVL(SUM(something),0) value
FROM
  tableA a,
  tableB b
WHERE
  a.id=b.id;

In this case it returns the following if the condition satisfies,

TYPE   VALUE
-------------
abcde   100

If the condition not satisfied, it won't returns any row.I need to return a default row in this case, like,

TYPE   VALUE
-------------
abcde   0

I tried using WHERE NOT EXISTS but in this case I have to include the large query in WHERE NOT EXISTS(my_large_query).

SELECT
  'abcde' type,
  NVL(SUM(something),0) value
FROM
  tableA a,
  tableB b
WHERE
  a.id=b.id
GROUP BY
  'abcde'
UNION
SELECT
  'abcde' type,
  0 value
FROM
  dual
WHERE
  NOT EXISTS
  (
    SELECT
      'abcde' type,
      NVL(SUM(something),0) value
    FROM
      tableA a,
      tableB b
    WHERE
      a.id=b.id
  );

Is there any other way I can use instead? If there any performance issue occures if I use NOT EXISTS

Upvotes: 1

Views: 231

Answers (4)

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

You want to select one record with 'abcde', so select this from dual. You get the sum in a subquery.

select 
  'abcde' type,
  nvl(
  (
    SELECT SUM(something)
    FROM tableA a
    JOIN tableB ON a.id=b.id
  ), 0) as value
from dual;

UPDATE: If you want more aggregates from the tables, you could do this with three subqueries, but it would be ineffective to do about the same query thrice. So you could use an outer join instead. As your tables' aggregates are totally unrelated with the dual table, however, this looks a bit strange, but well, the query is strange, consisting of an outer-cross join :-)

select 
  'abcde' as type, 
  nvl(sum_current, 0) as sum_curr,
  nvl(sum_previous, 0) as sum_prev
from dual
left join
(
  select 
    sum(data_current) as sum_current,
    sum(data_prev) as sum_previous
  from tablea a
  join tableb on a.id = b.id
) on 1 = 1; -- cross-outer joining

And here is the same without the 1 = 1 dummy ON clause. We select the type 'abcde' along with the aggregations and use this for the join.

select 
  'abcde' as type, 
  nvl(sum_current, 0) as sum_curr,
  nvl(sum_previous, 0) as sum_prev
from (select 'abcde' as type from dual)
left join
(
  select 
    'abcde' as type,
    sum(data_current) as sum_current,
    avg(data_prev) as sum_previous
  from tablea a
  join tableb on a.id = b.id
) using (type);

Choose whichever query you like better.

Upvotes: 1

Lalit Kumar B
Lalit Kumar B

Reputation: 49082

You could use an [LEFT|RIGHT]OUTER JOIN.

For example, using standard EMP and DEPT table in SCOTT schema:

SQL> SELECT d.deptno,
  2    NVL(e.empno, 0) empno
  3  FROM emp e
  4  RIGHT OUTER JOIN dept d
  5  ON e.deptno       = d.deptno
  6  AND d.deptno     IN (30,40);

    DEPTNO      EMPNO
---------- ----------
        10          0
        20          0
        30       7499
        30       7521
        30       7654
        30       7698
        30       7844
        30       7900
        40          0

9 rows selected.

In above output, you can see that there is an additional row for DEPT = 40 using RIGHT OUTER JOIN.

Try this query:

SELECT 'abcde' type,
  NVL(SUM(something),0) value
FROM tableA a
LEFT OUTER JOIN tableB b
ON a.id=b.id
GROUP BY type;

Upvotes: 0

diziaq
diziaq

Reputation: 7795

Eeepi, hi!

Seems to me we have here a XY problem. And you may try to solve this using PL/SQL since you've tagged the question as Oracle related.

But, responding strictly to your question: 1. yes we can solve you issue using pure SQL; 2. yes the query with not exists will gain some performance loss, because there's nothing free.

We need to consider that impact on the performance in this case is insignificant. For example, using of UNION keyword (in your example) will be a 1000 times more expensive - so, please use UNION ALL always if you don't want Oracle engine to sort and compare all rows in the query.

The query you need is:

WITH
 t AS
 (SELECT 'abcde' TYPE
        ,nvl(SUM(something), 0) VALUE
    FROM tableA a
        ,tableB b
   WHERE a.id = b.id)
SELECT *
  FROM (
        SELECT *
          FROM t

        UNION ALL

        SELECT 'abcde',  0 
          FROM dual
         WHERE NOT EXISTS (SELECT * FROM t)
       )

Upvotes: 0

Mattia Caputo
Mattia Caputo

Reputation: 969

you need to use a FULL OUTER JOIN, if i understand the question

SELECT
  'abcde' type,
  NVL(SUM(something),0) value
FROM tableA a
FULL OUTER JOIN tableB b ON a.id=b.id
GROUP BY 'abcde'

Upvotes: 0

Related Questions