CODEWITHSUNDEEP
sqloracle-databasequery-optimization
Mark Buffalo
Mark Buffalo

Reputation: 776

Oracle SQL: Optimizing LEFT OUTER JOIN of two similar select statements to be smaller and/or more efficient

So I have this Oracle SQL query:

SELECT man.Toilet_Type, NVL(man.manual_PORTA_POTTY, 0) MANUAL, NVL(reg.regular_PORTA_POTTY, 0) REGULAR FROM (
SELECT A.Visitor Toilet_Type, COUNT(A.Toilet_ID) MANUAL_PORTA_POTTY FROM 
    BORE.EnragedPotty A,
    BORE.SemiEnragedPotty B,
    BORE.ManualPotty C
WHERE B.SemiEnragedPotty_ID = C.SemiEnragedPotty_ID 
    AND B.Toilet_ID = A.Toilet_ID
GROUP BY Visitor
ORDER BY Visitor ASC) man

LEFT OUTER JOIN
    (SELECT A.Visitor Toilet_Type, COUNT(B.Toilet_ID) REGULAR_PORTA_POTTY FROM 
        BORE.EnragedPotty A,
        BORE.RegularPotty B
     WHERE B.Toilet_ID = A.Toilet_ID
     GROUP BY Visitor
     ORDER BY Visitor ASC) reg ON man.Toilet_Type = reg.Toilet_Type

This gives two table results. The first query, man, gives me the following output:

+===============+========+
| Toilet_Type   | Manual |
+===============+========+
| Portable      |  234   |
+---------------+--------+
| Home          |  10    |
+---------------+--------+
| Assassination |  2     | 
+---------------+--------+

The second query, reg, gives me the same output as above, but with REGULAR instead of MANUAL.

What I want to do is query the databases in a more efficient manner. I want the output to be formatted like so:

+===============+========+=========+
| Toilet_Type   | Manual | Regular |
+===============+========+=========+
| Portable      |  234   |    444  |
+---------------+--------+---------+
| Home          |  10    |    222  |
+---------------+--------+---------+
| Assassination |  2     |    111  | 
+---------------+--------+---------+

Surely this can be done in a single query without using a LEFT OUTER JOIN?

Upvotes: 3

Views: 74

Answers (2)

Mr. Llama
Mr. Llama

Reputation: 20899

If you need to pull from the same dataset twice, you should consider using subquery factoring.

WITH
some_result_you_dont_want_to_repeat AS (
    -- Chunk of SQL goes here
)
SELECT
    -- More SQL here
FROM some_result_you_dont_want_to_repeat once
JOIN some_result_you_dont_want_to_repeat twice
  ON ...

In your case, it appears that your A-B table join can be factored out.

Upvotes: 1

Hambone
Hambone

Reputation: 16397

This is untested, as I didn't have any sample data, but I think something similar to this might get it done in one query:

  SELECT
    E.Visitor Toilet_Type,
    SUM(case when SE.SemiEnragedPotty_ID is not null and
        M.Toilet_ID is not null then 1 else 0 end) MANUAL_PORTA_POTTY,
    SUM(case when R.Toilet_ID is not null then 1 else 0 end) REGULAR_PORTA_POTTY
  FROM 
    BORE.EnragedPotty E,
    BORE.SemiEnragedPotty SE,
    BORE.ManualPotty M,
    BORE.RegularPotty R
  WHERE
    E.SemiEnragedPotty_ID = SE.SemiEnragedPotty_ID (+) AND
    E.Toilet_ID = M.Toilet_ID (+)
    E.Toilet_ID = R.Toilet_ID (+)
  GROUP BY Visitor
  ORDER BY Visitor ASC

I may have some of the details off -- I had to rename your aliases to follow which table was which, so it wouldn't shock me if I misplaced one of them.

Upvotes: 1

Related Questions