Writing efficient queries is a very broad topic. If you're concerned about Oracle, I'd suggest to read Oracle's Performance guides:
Oracle® Database Performance Tuning GuideOracle® Database 2 Day + Performance Tuning Guide
If you haven't read it by now, start with the
Concepts guide. You'll need it to be able to follow the other guides.
One thing that stands out in this particular case is the lack of constraints. Constraints, such as primary key, unique and foreign key constraints, give the database additional information about the data and allow better optimization of the queries.
Generally speaking, the query which uses less joins is probably more efficient. Oracle does a lot of optimizations, though, and it is just possible that the two queries would perform about the same. There might be a difference, however, if the department name isn't unique, in which case the two queries would give different results! So, in your case, the query with two joins might also be incorrect. (I would prefer the department ID over the name for comparison in this query. ID just feels better for identification, which is what you need here. Name is just a name, and there might be situations in which names become non-unique, this is much less probable with IDs.)
(Also note that your sample data contains too few rows for any performance issues to actually come up. Incidentally, a promotion for GenRocket, a
test data generator, has just ended in our
Testing forum; this tool would help in generating the amount of data you'd need. I'm mentioning this just so that you're aware of it, I don't want you to actually post scripts that would insert thousands of rows.
)
Mahtab Alam wrote:One more question , can you tell me one query that have outer query and one correlated subquery and that correlated subquery uses group by clause .
So group by clause in inner subquery uses outer query`s column as grouping column.
I've though we've already discussed this thoroughly
here. It's exactly the same question. If it's still unclear to you, please let's continue the discussion in the other
thread.