June 27, 2015 at 6:04 am
can please help me to write better than this query but i want same output ...
select a. emp_id,a.uid,a.LAST_NAME,a.FIRST_NAME,a.MIDDLE_INIT,a.L_ID ,a.MANAGER_ID,b.l_id,b.loc_desc,c.office_phone,c.home_phone,c.cell_phone from employee a, emp_location b ,emp_contact c where a.emp_id =c.emp_id and a.l_id =b.l_id
June 27, 2015 at 7:52 am
better than what? I've reformatted it so that it's more readable and use the IFCode Shortcuts in the post screen of this website to highlight it as SQL.
SELECT a.emp_id,
a.uid,
a.LAST_NAME,
a.FIRST_NAME,
a.MIDDLE_INIT,
a.L_ID,
a.MANAGER_ID,
b.l_id,
b.loc_desc,
c.office_phone,
c.home_phone,
c.cell_phone
FROM HumanResources.Employee a,
emp_location b,
emp_contact c
WHERE a.emp_id = c.emp_id
AND a.l_id = b.l_id;
So, what you have are ANSI 89 joins instead of the common ANSI 92. I'd modify the FROM clause like this:
...FROM HumanResources.Employee AS e
JOIN emp_location AS el
ON e.l_id = el.l_id
JOIN emp_contact AS ec
ON e.l_id = c.emp_id;
I changed the aliases to make it more readable. Consitent use of common aliases from one query to the next rather than just a, b, c, for the first three tables, will make your code much easier to understand over time. If you're trying to improve performance of this query, there isn't any way currently. You don't have any filtering criteria, so it's just going to run as fast as your disks can scan the data into memory. Adding filtering criteria will give you tuning opportunities.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply