Combine Sub-Queries in Oracle PL/SQL
Combine Sub-Queries in Oracle PL/SQL
Why would you want to combine sub-queries in Oracle SQL? There is some performance gains that could help your query run faster and be more efficient.
Separate Sub-Queries
Many SQL developer will do the following when we need the most effective date with the most effective sequence. This is just an example of a sub-query in Oracle. Nothing wrong with this code, it is very readable and understandable. For the most part it is pretty straight forward.
1 2 3 4 | SELECT * FROM PS_SOMETABLE S1 WHERE EFFDT = (SELECT MAX(EFFDT) FROM PS_SOMETABLE S2 WHERE S1.EFFDT = S2.EFFDT) AND EFFSEQ = (SELECT MAX(EFFSEQ) FROM PS_SOMETABLE S2 WHERE S1.EFFSEQ = S2.EFFSEQ) |
Combined Sub-Queries
The following code is a little more elegant and takes a little bit to understand. This approach reduces the number of lookup operations the database has to do to one instead of the two in the previous example. This should bring up some thoughts on code you have written in the past.
1 2 3 | SELECT * FROM PS_SOMETABLE S1 WHERE (EFFDT, EFFSEQ) = (SELECT MAX(EFFDT), MAX(EFFSEQ) FROM PS_SOMETABLE S2 WHERE S1.QUALIFIER = S2.QUALIFIER) |