Hey

I'm new to oracle, but it happens to be THE database for my db course. (THE database, that spends 15 min. importing a 5-table model from oracle WTF apps btw).

So the question is - is oracle that good at optimizing, that you can write such a crazy thing, or is this query an enormous WTF?

I

SELECT ename, sal
FROM emp e
WHERE 3 >

(SELECT count (*)
FROM emp
WHERE e.sal < sal);

(from course materials...)

My first guess would rather be:
SELECT * FROM (SELECT ename, sal FROM emp ORDER BY sal) WHERE ROWNUM<=3

That looks suspiciously like a correlated subquery to me.

I hate those things.

No, I that query is different from yours. Looks like it's getting all employees who have more than three others earning more than them. Your query just gets the three lowest paid employees. (edit) doh, make that "less than three"... it's late in the day...

it IS a correlated subquery as someone else said... Fetching only those employees who are out-earned by less than 3 people.

I avoid them as much as possible myself - I've found that other databases might not like them (interbase, for example..)

However, Oracle's optimiser is actually pretty good at this sort of thing.  It seems to be very forgiving for sloppy query design....

Personally I'd do something more like: (Firebird syntax, what I use at the moment)

--- 

select e.ename,  e.sal

from emp e

inner join emp e2 on (e2.sal > e.sal)

group by e.ename, e.sal 

having (count(e2.ename)  < 3)

---

... but thats just me..

 

That's a bad example IMO.  inconsistent use of aliases, somewhat backwards use of logic (ok, that might be personal preference, but "3 > (some variable expression)" just seems weird to me)...

I found it a bit confusing to figure out what it's doing in its current format. 

 
So it is technically valid, but it's confusingly designed and is only any good because oracle is quite good at optimising nonsense.

 

All just IMO of course, and my brain can sometimes be a big WTF in itself.... 

SELECT name, sal FROM emp ORDER BY sal DESC LIMIT 3, shirley?

mentor:

SELECT name, sal FROM emp ORDER BY sal DESC LIMIT 3, shirley?

MySQL, mary?