-1

What is the difference between:

select e.first_name,
       e.last_name,
       e.id,
       d.dept_name
from employees e, departments d
where e.department_id = d.department_id;

and

select e.first_name,
       e.last_name,
       e.id,
       (select d.dept_name frpm departments d where d.department_id = e.department_id) dept_name    
from employees e;

I am talking about differences in performance, query time, CPU utilization etc. Which one is better to use?

Sathyajith Bhat
  • 21,321
  • 22
  • 95
  • 134
A Nice Guy
  • 2,676
  • 4
  • 30
  • 54
  • 1
    Why don't you tell us? Just test it and we can discuss our facts. No offense. – Oğuz Sezer Aug 25 '14 at 10:51
  • 1
    There has always been a Worst Case, Best Case scenario for both the queries. You have to explore them by your own under varying conditions in database records. – Bhumin Vadalia Aug 25 '14 at 10:53
  • The bottom one gives you the number of records in employee and an error if employee : department is not 1:1. The top one might give you more or less than the number of employees depending on how the match works. If you have your FK's set up this isn't a worry but be aware it's possible they don't give you the same results at all. – Nick.Mc Aug 25 '14 at 11:58

1 Answers1

0

The first one works as inner join; the returned rows are rows with matched ID in both tables.

While the second one will get you NULL in case there is no shared department ID in the sub select, but it will still give you the results of your main select

sameh.q
  • 1,691
  • 2
  • 23
  • 48