changing from implicit joins to explicit joins

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

changing from implicit joins to explicit joins

Author

Topic

anjali5
Posting Yak Master

121 Posts

Posted - 2014-01-06 : 12:39:46

Hi All,

I am changing from implicit joins to explicit joins. I have this query before I converted it to explicit join.


select num, pin, sn, stepnum, count(addon.type) 
from steps, addon
where 
addon.num =* steps.num and
addon.pin =* steps.pin and
addon.sn =* steps.sn and
step like 'pro%' and type ='X'
group by num, pin, sn, stepnum
having count(addon.type) =0

The data that I am getting with the above query is 4 rows. below I am displaying only two rows.



num        pin  sn   stepnum   NoColumnName
BAKER      02   045  99a		0
CROSS      00   087  24b		0
and after I converted it to explicit join


select num, pin, sn, stepnum, count(addon.type) 
from steps  RIGHT JOIN addon
  ON 
(addon.num = steps.num and
addon.pin = steps.pin and
addon.sn = steps.sn )
where 
step like 'pro%' and type ='X'
group by num, pin, sn, stepnum
having count(addon.type) =0

when I run the above changed query, I don't get any rows.

any help will be appreciated.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-01-06 : 13:01:10

Can you supply sample data to show the issue. It's also very hard to follow the query without table prefixes on the columns. It could be the join is backwards or that you need to move a predicate out of the where clause on to the join clause. But, sample data would really help.

anjali5
Posting Yak Master

121 Posts

Posted - 2014-01-06 : 13:48:59

Below is the sample data and the updated table prefix query


Steps Data


num        Pin        sn         stepNum    stepText
---------- ---------- ---------- ---------- --------------------------------------------------
BAKER      01         045        12a        test1
CROSS      02         098        14a        PROFFESION
TEST1      03         123        18a        PROGRESS
Test2      067        897        19a        PROGRAM


addon Data

Num        PIN        SN         Type
---------- ---------- ---------- --------------------------------------------------
BAKER      01         045        W
CROSS      02         098        X
TEST2      03         123        P

resulting data from the below query



select steps.num, steps.pin, steps.sn, stepnum, count(addon.type) 
from steps, addon
where 
addon.num =* steps.num and
addon.pin =* steps.pin and
addon.sn =* steps.sn and
stepText like 'pro%' and type ='X'
group by steps.num, steps.pin, steps.sn, steps.stepnum
having count(addon.type) =0


Data with above query

num        pin        sn         stepnum    
---------- ---------- ---------- ---------- -----------
TEST1      03         123        18a        0
Test2      067        897        19a        0
Warning: Null value is eliminated by an aggregate or other SET operation.

query after applying explicit join.




select steps.num, steps.pin, steps.sn, stepnum, count(addon.type) 
from steps  RIGHT JOIN addon
  ON (addon.num = steps.num and
addon.pin = steps.pin and
addon.sn = steps.sn)
where 

stepText like 'pro%' and type ='X'
group by steps.num, steps.pin, steps.sn, steps.stepnum
having count(addon.type) =0

I don't get any data with the above query.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-01-06 : 14:12:19

Thanks for the sample data. It also makes it easier for us to help you if you put the sample data in a consumable format, meaning with create tables, insert statements, etc..

But, here is a an update to your code that should work:

select 
	steps.num, 
	steps.pin, 
	steps.sn, 
	steps.stepnum, 
	count(addon.type) 
from 
	steps
LEFT JOIN 
	addon
	ON addon.num = steps.num 
	and addon.pin = steps.pin
	and addon.sn = steps.sn
	and addon.[type] ='X'
where
	steps.stepText like 'pro%' 
group by 
	steps.num, 
	steps.pin, 
	steps.sn, 
	steps.stepnum
having 
	count(addon.[type]) = 0

anjali5
Posting Yak Master

121 Posts

Posted - 2014-01-06 : 14:17:32

Thank you, but just out of curosity, when I started working on this project, I was told to convert all =* to right join and *= to left join, but you put the left join for =*. I am not sure why, but your query works.

Thanks again

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-01-06 : 15:59:20

It has to do with the which side of the operator the table is on. In this case if you had kept the tables in the same order as they appear in the comparison (=*), then you would have been closer. for example, in your sample data you had "addon.num =* steps.num" If you wrote the query like:


...from 
	addon
right join
	steps
	ON addon.num = steps.num 
	and addon.pin = steps.pin
	and addon.sn = steps.sn
	and addon.[type] ='X'
	...

Then that would work.

I think just the advice to covert all =* to right and all *= to left is bad advice. I think the trend is to avoid RIGHT OUTER JOINS if possible. But, moreover, you need to understand witch table is the dominant (steps) and which is the subordinate (addon) and write the query accordingly.

anjali5
Posting Yak Master

121 Posts

Posted - 2014-01-06 : 18:16:11

Thank you!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources