"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

"Tom Austin" <taustin> wrote in message news:47b1ff01$1@news.povray.org...

> will it work if you remove the '*'?
>
> SELECT tbl1.Col1, tbl2.col2 from tbl1, tbl2 WHERE tbl1.Col1 = tbl2.Col1
>

Sure. There's nothing in the syntax to say that's a join, not a where
condition. It's the older outer join syntax that was deprecated and now
doesn't work at all.

It's still recommended that joins be done in the from using the JOIN syntax.
Makes it harder to do an accidental cross join. (2 or more tables 'joined'
without a condition. Results in a cartesian product)

Post a reply to this message

Tom Austin wrote:
> 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
> 
> 
> 
> Now, I would normally write a SQL statement like this:
> 
> select L.Type from Person P, Limbs L where L.PersonIndex = P.Index and 
> P.Name = 'Bones'
> 
> This *works*, but somehow I feel that this is where the JOIN should come 
> in.
> 
> 
> Should a JOIN be used here?
> Does it matter?
> 
> 
> Thanky..... Tom
> 
> 


So to make sure I understand correctly....

My SQL statement is OK, but not necessarily ANSI standard.

My SQL statement is likely less efficient than using JOIN.
	depending on the DB being used

My SQL statement may not be supported on all DB engines.  JOIN *should* 
be supported on all DB engines.



I've tried learning JOINS before, but it never stuck.
I guess I learned it all wrong int he first place.

Does someone have some simple and clear examples on JOINS and where they 
work?

Yes, there's plenty on the net, but I've tried it, only to not have it 
stick.



Thanky....  Tom

Post a reply to this message

Invisible wrote:
> 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.
> 

To be honest, my mind was able to wrap itself around not using JOIN for 
that very reason, it made more sense to me.



Tom

Post a reply to this message