"Dan Byers" <goofygraffix_at_geemail_dot_com> wrote in message
news:web.47b1b9a1e388923da8d0b25a0@news.povray.org...
> Mike Raiford <mra### [at] hotmailcom> wrote:

> > But, frowned upon in the general sense?
>
> Actually, it's frowned upon where outer joins are concerned.  This is from
the
> online manual (SQL Server 2000 - I haven't checked SQL Server 2005 yet):

The older outer join syntax doesn't work at all in SQL 2005. Dunno about the
other DB engines.
(Does Oracle know what a join is yet?) <g>

This works in SQL 2005

SELECT tbl1.Col1, tbl2.col2 from tbl1 LEFT OUTER JOIN tbl2 on tbl1.Col1 =
tbl2.Col1

This doesn't

SELECT tbl1.Col1, tbl2.col2 from tbl1, tbl2 WHERE tbl1.Col1 *= tbl2.Col1

Post a reply to this message

Gail Shaw wrote:

> The older outer join syntax doesn't work at all in SQL 2005. Dunno about the
> other DB engines.
> (Does Oracle know what a join is yet?) <g>

I should probably poke you for that. :-P

Considering that I was playing with the different join types in an old 
version of Oracle 6 years ago when I was still at uni, I think we can 
safely say it knows about joins...

-- 
http://blog.orphi.me.uk/
http://www.zazzle.com/MathematicalOrchid*

Post a reply to this message

Gail Shaw wrote:

> 
> This doesn't
> 
> SELECT tbl1.Col1, tbl2.col2 from tbl1, tbl2 WHERE tbl1.Col1 *= tbl2.Col1
> 
> 

Thank goodness they dropped the *= and =* operators.

Post a reply to this message

"Gail Shaw" <initialsurname@sentech sa dot com> wrote:
> This works in SQL 2005
>
> SELECT tbl1.Col1, tbl2.col2 from tbl1 LEFT OUTER JOIN tbl2 on tbl1.Col1 =
> tbl2.Col1
>
> This doesn't
>
> SELECT tbl1.Col1, tbl2.col2 from tbl1, tbl2 WHERE tbl1.Col1 *= tbl2.Col1

I remember the Delphi programmers here at work squawking about that.  They have
a lot of embedded SQL in their Delphi code that uses the old latter syntax, and
they had to go thru and fix it all.  I guess that's why they get paid the big
programmer bucks :)

--
Dan
GoofyGraffix.com

Post a reply to this message

Tom Austin wrote:

> Should a JOIN be used here?
> Does it matter?

As I understand it, on any half-decent DB engine there shouldn't be any 
performance difference. (The two are equivilent under the relational 
algebra.) As for readability... well that's a whole other matter. You 
might well argue that using the JOIN keyword makes the intent clearer.

It's a while since I've looked at this stuff seriously.

-- 
http://blog.orphi.me.uk/
http://www.zazzle.com/MathematicalOrchid*

Post a reply to this message

"Invisible" <voi### [at] devnull> wrote in message
news:47b1c445@news.povray.org...
> Gail Shaw wrote:
>
> > The older outer join syntax doesn't work at all in SQL 2005. Dunno about
the
> > other DB engines.
> > (Does Oracle know what a join is yet?) <g>
>
> I should probably poke you for that. :-P
>
> Considering that I was playing with the different join types in an old
> version of Oracle 6 years ago when I was still at uni, I think we can
> safely say it knows about joins...

Last time I used Oracle, it don't know what the JOIN keyword was. Sure, it
could do joins, but in the where clause.

A very quick google tells me that INNER JOIN/LEFT OUTER JOIN/RIGHT OUTER
JOIN became available in oracle in version 9i
In versions previous to that, joins had to be done in the where clause and
outer joins were specified using the *= or =* syntax.

Ref:
(http://www.kingtraining.com/confdownloads/downloads/Oracle9iJoin_paper.pdf)

Post a reply to this message

"Dan Byers" <goofygraffix_at_geemail_dot_com> wrote in message
news:web.47b1c8f8e388923da8d0b25a0@news.povray.org...

> I remember the Delphi programmers here at work squawking about that.  They
have
> a lot of embedded SQL in their Delphi code that uses the old latter
syntax, and
> they had to go thru and fix it all.  I guess that's why they get paid the
big
> programmer bucks :)

Shame. I feel for them (not)
That's what they get for not using stored procedures.

Post a reply to this message

"Gail Shaw" <initialsurname@sentech sa dot com> wrote:

> Shame. I feel for them (not)
> That's what they get for not using stored procedures.

In their defense, a lot of the code is for generating SQL on-the-fly for viewing
certain windows, screens, whatever, based on the user selecting certain options
in whichever screen they're using.  BTW we're talking about a telco billing
application with over 300 tables and a huge number of windows, screens,
dropdowns, etc., used to manage customers, billing, delinquencies, switch
activation, trouble tickets, external plant management (all the cables and
telephone poles -- the physical components that carry the phone/cable/internet
service to a house/business).  They do use a large number of stored procs for
the real heavy-lifting (I counted almost 700).

--
Dan
GoofyGraffix.com

Post a reply to this message

Gilles Tran wrote:

> 47b1a492$1@news.povray.org...
>> OK, I've heard a lot about inner joins and outer joins but don't really 
>> know them or how to use them.
>>
>> I've actually been using SQL for 10 years now, but never managed to 
>> completely figure this out.
>>
>>
>> Lets say I have (2) tables - (2) column each
>>
>> Person Limbs
>> Index Name PersonIndex Type
> 
> I'd do it a little differently ;)
> 
> Table:Persons
> Person_code
> Person_name
> 
> Table:Limbs
> Limb_code
> Limb_type
> 
> Table:Person_limbs
> Person_code
> Limb_code
> 
> The Person_limbs table make it possible to give a person a variable list of 
> limb types.
> 
> Now you want to list the names of persons who have limbs of the type "legs" 
> :
> SELECT Person.Person_name
> FROM (Limbs INNER JOIN Person_limbs ON Limbs.Limb_code = 
> Person_limbs.Limb_code) INNER JOIN Person ON Person_limbs.Person_code = 
> Person.Person_code
> WHERE (((Limbs.Limb_name)="legs"));
> 
> G.
> 

Yes, you are right.
My example was not thought out to that degree.
It was only to get information on the JOIN functionality.


Thanks

Tom

Post a reply to this message

Gail Shaw wrote:
> "Dan Byers" <goofygraffix_at_geemail_dot_com> wrote in message
> news:web.47b1b9a1e388923da8d0b25a0@news.povray.org...
>> Mike Raiford <mra### [at] hotmailcom> wrote:
> 
>>> But, frowned upon in the general sense?
>> Actually, it's frowned upon where outer joins are concerned.  This is from
> the
>> online manual (SQL Server 2000 - I haven't checked SQL Server 2005 yet):
> 
> The older outer join syntax doesn't work at all in SQL 2005. Dunno about the
> other DB engines.
> (Does Oracle know what a join is yet?) <g>
> 
> This works in SQL 2005
> 
> SELECT tbl1.Col1, tbl2.col2 from tbl1 LEFT OUTER JOIN tbl2 on tbl1.Col1 =
> tbl2.Col1
> 
> This doesn't
> 
> SELECT tbl1.Col1, tbl2.col2 from tbl1, tbl2 WHERE tbl1.Col1 *= tbl2.Col1
> 
> 

will it work if you remove the '*'?

SELECT tbl1.Col1, tbl2.col2 from tbl1, tbl2 WHERE tbl1.Col1 = tbl2.Col1


Tom

Post a reply to this message