how to reduce the number of sub queries in SQL-CodePudding

I want to optimize/reduce the sub query:

select cash from INFO where cid IN (
    select tid from activity where point='aaa' and stats='bbb' and tid IN (
        select rid from request where stats in ('vvv','ddd') and TS >= to date ('2022-05-04','YYYY-DD-MM')))

CodePudding user response：

You do not need to do anything as the query is optimised as it is and sub-queries are not always things to get rid of.

If you change the query to use JOINs then it is likely that you will get different results as the JOINed query can then return duplicate rows where previously you would have only got unique rows.

For example, if you have the data:

CREATE TABLE info (cash, cid PRIMARY KEY) AS
SELECT 100, 1 FROM DUAL;

CREATE TABLE activity (id PRIMARY KEY, tid, point, stats) AS
SELECT 2, 1, 'aaa', 'bbb' FROM DUAL;

CREATE TABLE request (id PRIMARY KEY, rid, ts, stats) AS
SELECT 3, 1, DATE '2022-04-05', 'vvv' FROM DUAL UNION ALL
SELECT 4, 1, DATE '2022-04-06', 'ddd' FROM DUAL;

Then your query:

SELECT cash
FROM   INFO
WHERE  cid IN (
         SELECT tid
         FROM   activity
         WHERE  point='aaa'
         AND    stats='bbb'
         AND    tid IN (
                  SELECT rid
                  FROM   request
                  WHERE  stats in ('vvv','ddd')
                  AND    TS >= DATE '2022-04-05'
                )
       );

Outputs:

CASH

100

CASH
100

But if you use JOINs:

SELECT i.cash
FROM   info i
       JOIN activity a ON a.tid = i.cid
       JOIN request r ON r.rid = a.tid
WHERE  a.point = 'aaa'
AND    a.stats = 'bbb'
AND    r.stats IN ('vvv', 'ddd')
AND    r.ts >= DATE '2022-04-05'

Then the output is:

CASH

100

100

CASH
100
100

As one row from cash was joined to one row from activity which was joined to two rows from request and both rows are returned.

You may have got rid of the sub-queries but you get a different output!

db<>fiddle here

CodePudding user response：

Joins, I guess.

SELECT i.cash
  FROM info i
       JOIN activity a ON a.tid = i.cid
       JOIN request r ON r.rid = a.tid
 WHERE     a.point = 'aaa'
       AND a.stats = 'bbb'
       AND r.stats IN ('vvv', 'ddd')
       AND r.ts >= DATE '2022-04-05'