outer join shortcut?

wey97 · #1 (**permalink**) 11-20-07, 15:11

Is this a shortcut for an outer join and if so, why can't I find any info on it?

Code:

select id, name
from table1, table2
where id *= id_tbl2

instead of

Code:

select tb1.id, tb1.name
from table1 as tb1
outer join table2 as tb2
where tb1.id = tb2.id_tbl2

and what happens in the first example if you try to join more tables?

amthomas · #2 (**permalink**) 11-20-07, 15:25

http://en.wikipedia.org/wiki/Join_(SQL)

the first is an inner join.

the 2nd would show all result (left outer join) for table1 and only combine the result from table2 that matched. Of course I could be wrong on this if it is something MS SQL specific. Does it allow for just 'outer join'?

wey97 · #3 (**permalink**) 11-20-07, 15:31

Quote:

Originally Posted by amthomas

http://en.wikipedia.org/wiki/Join_(SQL)

the first is an inner join.

the 2nd would show all result (left outer join) for table1 and only combine the result from table2 that matched. Of course I could be wrong on this if it is something MS SQL specific. Does it allow for just 'outer join'?

according to sql server, the first is an outer join.

you can verify that by attempting

Code:

select id
from table1
where id *= 1

and the error message is

Code:

Msg 150, Level 15, State 1, Line 5
Both terms of an outer join must contain columns.

tomh53 · #4 (**permalink**) 11-20-07, 15:35

*= is indeed a left outer join, just as =* is a right outer join. It is a T_SQL extension and was valid up thru S2K. It is no longer available in S2K5.

It is not in the ANSI Standard, so if you want your code to run in SQL Server later than 2000, do not use it!

amthomas · #5 (**permalink**) 11-20-07, 15:38

oops, I am sorry. that was something ms sql specific. I should not have answered that. I just didn't see the little * :/

sorry about that. well.. at least I learned something

wey97 · #6 (**permalink**) 11-20-07, 15:41

Quote:

Originally Posted by tomh53

*= is indeed a left outer join, just as =* is a right outer join. It is a T_SQL extension and was valid up thru S2K. It is no longer available in S2K5.

It is not in the ANSI Standard, so if you want your code to run in SQL Server later than 2000, do not use it!

Code I inherited.

I don't think the db admin knew exactly what he was doing, other than taking a shortcut to write less code.

blindman · #7 (**permalink**) 11-20-07, 16:05

Its not a shortcut. Its an "old cut". It used to be the standard coding syntax, so far back in the mists of time that only wise men such as Pat Phelan know the origins.

sdtacoma · #8 (**permalink**) 10-07-11, 17:13

Hello,

I realize this post is a little old but...

I have been using the 'old cut' for a long time now without issue. I have recently been trying to convert to the 'ANSI Standard' with mixed results. I am sure it is user-error but I often have problems pulling the correct rows with the 'ANSI Standard' and can get exactly what I need with the 'old cut' way.

Maybe somebody can see what I am doing wrong here. I have tried changing my joins to left outer/inner with no luck.

'Old cut' way, returns the needed 589 records:
select amt.family_id, u.user_type_id, ui.lastname + ', ' + ui.firstname as StuName, sf.sf_file_name
from advisor_student_matches amt, user_info ui, users u, student_files sf
where amt.edcom_id = ui.edcom_id
and ui.edcom_id = u.edcom_id
and ui.edcom_id *= sf.sf_edcom_id
and amt.delete_date is null
and sf.sf_file_type = 'pdf'
order by amt.family_id, u.user_type_id

'ANSI Standard' way, returns 142 records:
select amt.family_id, u.user_type_id, ui.lastname + ', ' + ui.firstname as StuName, sf.sf_file_name
from advisor_student_matches amt
left join user_info ui on amt.edcom_id = ui.edcom_id
left join users u on ui.edcom_id = u.edcom_id
left outer join student_files sf on ui.edcom_id = sf.sf_edcom_id
where amt.delete_date is null
and sf.sf_file_type = 'pdf'
order by amt.family_id, u.user_type_id

Any help would be appreciated.

Thanks

r937 · #9 (**permalink**) 10-07-11, 22:55

change this --

Code:

left outer join student_files sf on ui.edcom_id = sf.sf_edcom_id 
where amt.delete_date is null
and sf.sf_file_type = 'pdf'

to this --

Code:

left outer join student_files sf on ui.edcom_id = sf.sf_edcom_id 
and sf.sf_file_type = 'pdf'
where amt.delete_date is null

you will be surprised at the difference in results

jassi.singh · #10 (**permalink**) 10-10-11, 06:14

Hello,

Its better to use outer join instead of shortcut for performance gain

cindyaz · #11 (**permalink**) 10-10-11, 11:28

Quote:

Originally Posted by jassi.singh

Hello,

Its better to use outer join instead of shortcut for performance gain

Though join shortcuts have been depracated now, can you elaborate how will this help in performance?

blindman · #12 (**permalink**) 10-10-11, 14:02

When you include a column from an outer-joined dataset in the WHERE clause, it effectively turns it into an inner join. So, as Rudy showed, you need to include such filters in the outer join itself:

Code:

select	amt.family_id,
	u.user_type_id,
	ui.lastname + ', ' + ui.firstname as StuName,
	sf.sf_file_name
from	advisor_student_matches amt
	left outer join user_info ui on amt.edcom_id = ui.edcom_id
	left outer join users u on ui.edcom_id = u.edcom_id
	left outer join student_files sf
		on ui.edcom_id = sf.sf_edcom_id
		and sf.sf_file_type = 'pdf'
where	amt.delete_date is null
order by amt.family_id,
	u.user_type_id

jassi.singh · #13 (**permalink**) 10-11-11, 04:31

Quote:

Originally Posted by cindyaz

Though join shortcuts have been depracated now, can you elaborate how will this help in performance?

YOu can write both the quries in ssms and then clcik on estimated execution plan button to see the total cost and resource usage

sdtacoma · #14 (**permalink**) 10-13-11, 15:09

Sorry, I didn't mean to abandon my posting. I got tasked onto something else here at work.

Your suggestions worked like a charm; I get the records I need. Thank you for the help and teaching me something new.

r937 · #15 (**permalink**) 10-13-11, 15:44

Quote:

Originally Posted by sdtacoma

Your suggestions worked like a charm;

you are of course referring to post #9

thanks very much

Internet Services

Web Development

Digital Marketing

Consumer Tech