Reduce number of subqueries-CodePudding

I've 2 tables emp and expenditure.

Emp:

ID, NAME

Expenditure:

ID, EMP_ID, AMOUNT

Each emp has a limit of 100 that he/she can spend. We want to check which emp has expenditure > 100.

Output attributes needed: Emp name, exp id, amount

My query:

SELECT E.NAME,
    EXP.ID,
    EXP.AMOUNT
FROM EMP E
INNER JOIN expenditure EXP ON E.ID = EXP.EMP_ID
WHERE E.ID in
        (SELECT EMP_ID
            FROM
                (SELECT EMP_ID,
                        SUM(AMOUNT) AS TOTAL
                    FROM expenditure
                    GROUP BY EMP_ID
                    HAVING SUM(AMOUNT) > 100.00
                    ORDER BY TOTAL DESC) SUBQ)
ORDER BY EXP.AMOUNT desc;

Is it possible to optimize this?

CodePudding user response：

Just like code, SQL queries can be written in many different ways. Run an Execution Plan on your SQL. Check here and here

Below is more "conciser" version although it may not be any more optimised than your current code. Use Execution Plans to analyse performance.

SELECT E.NAME,
    E.ID, -- THIS IS EMPLOYEE ID NOT EXPENDITURE ID
    EXP.EMP_SPENT
FROM EMP E
JOIN (SELECT EMP_ID, sum(AMOUNT) as EMP_SPENT FROM expenditure GROUP BY EMP_ID) EXP 
    ON E.ID = EXP.EMP_ID
WHERE EXP.EMP_SPENT > 100;

CodePudding user response：

You can use a simple aggregation with HAVING clause such as

SELECT e.name, e.id, SUM(amount) AS total
  FROM emp e
  JOIN expenditure ep
    ON e.id = ep.emp_id
 GROUP BY e.name, e.id
HAVING SUM(amount) > 100

but it's not logical to have a non-aggregated column along with aggregated ones within the result