Selecting any one from a relation

Question

I'm trying to write a SQL query to achieve the following (The actual schema in my problem is different but the idea is the same)

Say I have two tables

Employee table

------- ---- --- -------
empl_id name age dept_id
------- ---- --- -------
1       a    30  1
2       b    35  1
3       c    45  2
4       d    30  2
5       e    50  3
6       f    50  3

Department table

------- ---- 
dept_id name
------- ----
1       x
2       y
3       z

What I want to achieve is two fold

a) select the departments that have at least one employee whose age is less than 40 (say).
b) return the emp_id of any employee who belongs to that criteria in the same result set

So running the query on the above tables should return

dept_id emp_id
------- ------
1        1 <-- I don't care if emp_id returned is 1 or 2. Both satisfy the filter
2        4

I know I can achieve objective a) with this query

select
    dept_id
from
    Department d
where
    exists (
        select
            1
        from
            Employee e
        where
            e.age < 40
        and e.dept_id = d.dept_id
    )

But I can't think of a way to achieve b). Can somebody shed some light?

Thanks

By the way, what RDBMS are you using? – neutrino Dec 04 '13 at 11:33 — neutrino, Dec 04 '13 at 11:33

neutrino · Answer 1 · 2013-12-04T10:58:20.243

1

SELECT Department.dept_id, empl_id
FROM Department JOIN Employee ON Employee.dept_id = Department.dept_id
AND Employee.age < 40
GROUP BY Department.dept_id

SQL Fiddle here.

If you use a RDBMS that requires an aggregate function to use GROUP BY, as, for example, MS SQL Server, you can do something like:

select Department.dept_id, MIN(empl_id)
from Department JOIN Employee ON Employee.dept_id = Department.dept_id
AND Employee.age < 40
GROUP BY Department.dept_id

edited Dec 04 '13 at 10:58

answered Dec 04 '13 at 10:43

neutrino

2,297
4
20
28

Excellent! This gives the result I expect. But I always thought there should be an aggregate function with a group by statement. Is it not the case? Can you add a bit more explanation? – ubi Dec 04 '13 at 10:50
Well, this has been tested on MySQL. A detailed explanation can be found [here](http://stackoverflow.com/questions/1225144/why-does-mysql-allow-group-by-queries-without-aggregate-functions). If you use other RDBMS, let me know. – neutrino Dec 04 '13 at 10:55
I would also prefer not to use `group by` and go with an `exist` filter if possible. Is there a way to do that? As I understand `exist` will fetch only one row from the subquery whereas the `group by` will have to fetch all rows matching the filter so in terms of performance using `exist` is better (I think). – ubi Dec 04 '13 at 11:01
But does not exist involve a subquery? That's worse than a simple aggregate function, I guess... – neutrino Dec 04 '13 at 11:03
Correct me if I'm wrong. Here's how I think aggregation is performed by the db engine in simple terms. Taking the above example, it will iterate all rows of `Department` and for each `dept_id` will fetch all rows from `Employee` that have the same `dept_id` and satisfy the filter and perform the aggregate operation (`min()`/`max()` etc). When there's the exists filter, it will still iterate all rows of `Department` but will fetch only one row from Employee that has the same `dept_id` and satisfy the filter. So performance-wise I think using `exist` is better (if possible in this case) – ubi Dec 04 '13 at 11:15
You're right, but the exist involves a scan over all the employees satisfying the condition. The deal here is that the performance impact is in the scans, more than in the fetches. But, in this case, maybe you're right... So why don't you test both approaches, and see what's better? – neutrino Dec 04 '13 at 11:19

score 1 · Answer 2 · answered Dec 04 '13 at 10:46

1

Try this statement.

select t.dept_id, min(t.empl_id) as empl_id

from
(
    select dept_id, empl_id
    from
    employee
    where
    age < 40
) t 

group by t.dept_id

answered Dec 04 '13 at 10:46

peter.petrov

38,363
16
94
159

score 0 · Answer 3 · answered Dec 04 '13 at 10:57

0

try this,

;WITH T(DEPTID,EMPID,CNT_OF_EMP)
AS
(
SELECT D.DEPT_ID,E.EMPL_ID,COUNT(E.ID)
FROM DEPARTMENT D INNER JOIN EMPLOYEE E ON E.DEPT_ID = D.DEPT_ID AND E.age>40
GROUP BY E.EMPL_ID,D.DEPT_ID
)
SELECT EMPID FROM T

answered Dec 04 '13 at 10:57

RobertKing

1,853
8
30
54

score 0 · Answer 4 · answered Dec 04 '13 at 11:24

0

This is even simpler, and I think is even faster than my previous answer, and the existapproach:

select dept_id, MIN(empl_id)
from Employee 
WHERE Employee.age < 40
GROUP BY dept_id

answered Dec 04 '13 at 11:24

neutrino

2,297
4
20
28

Selecting any one from a relation

4 Answers4