Concatenation and Correlated Subqueries - Performance Tuning

Concatenation allows to retrieve data correlated to the MIN/MAX function in a single pass

Example:

1. Employees with highest salary in each department

This can be written as using correlated subquery

SELECT D_No, Sal, L_Na, F_Na
FROM test.employee ee
WHERE Sal IN
(SELECT MAX (Sal)
FROM test.employee em
WHERE ee.D_No=em.D_No
GROUP BY D_No)
ORDER BY D_No;

Answer Set:

Dept_No	Salary_Amount	Last_Name	First_Name
10	70000	pitt	arun
20	75000	lucas	frank
30	60000	jones	indiana
40	65000	phips	carla
50	60000	stifler	tom
50	60000	young	nikie
60	60000	penn	arun
70	70000	paul	ak
80	70000	nelson	julie
90	75000	white	sam
100	45000	pitt	mark

It can also be written as

SELECT D_No,
MAX(Sal || ' ' || L_Na || ',' ||F_Na)
FROM test.employee
GROUP BY D_No
ORDER BY D_No ;

Answer Set:

Dept_No	Maximum(((((Salary_Amount||' ')||Last_Name)||',')||First_Nam
10	70000 pitt           ,arun
20	75000 lucas          ,frank
30	60000 jones          ,indiana
40	65000 phips          ,carla
50	60000 young          ,nikie
60	60000 penn           ,arun
70	70000 paul           ,ak
80	70000 nelson         ,julie
90	75000 white          ,sam
100	45000 pitt           ,mark

Did you observe the two answer sets? Can you tell the difference and how to overcome it? Please leave your answers in comments.

Related Posts