"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

Gail Shaw wrote:
> "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)
> 


So, using JOIN is more explicit - it requires a condition for the JOIN 
where just using a where clause does not?


Tom

Post a reply to this message

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

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

On MS SQL Server, they will be identical.

> 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?

Not offhand. I could probably write an explaination for you (tomorrow) that
might be clear.
What are you confused about?

Post a reply to this message

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

>
> So, using JOIN is more explicit - it requires a condition for the JOIN
> where just using a where clause does not?

Yup

This is valid
SELECT tbl1.Col1, tbl2.col2 from tbl1, tbl2 WHERE tbl1.Col1 = tbl2.Col1

This is also value, but will likely kill your server if the 2 tables have a
reasonably large number of rows
SELECT tbl1.Col1, tbl2.col2 from tbl1, tbl2

This is valid (and equivalent to the first)

SELECT tbl1.Col1, tbl2.col2 from tbl1 INNER JOIN tbl2 ON  tbl1.Col1 =
tbl2.Col1

This is not valid and will throw an error (on MS SQL 2005, a rather
misleading one)
SELECT tbl1.Col1, tbl2.col2 from tbl1 INNER JOIN tbl2

It's clear and easy to see on 2 tables. On more, can get messy. Especially
if there are joins on multiple columns, it's quite easy to have a table that
looks like it's joined, but isn't. See below mess.

Select T1.Col1, T2.Col1, T3.Col2 T4.Col3, T4.Col3 FROM Table1 T1, Table2 T2,
Table3 T3, Table4 T4, Table4 T5
WHERE T1.Col1 = T2.Col1 AND T2.Col2 = T3.Col2 AND T1.Col7 = 'abc' AND
T2.Col1 = T3.Col1 AND T4.Col3 = T5.Col3 AND T4.Col2>5

Post a reply to this message

Tom Austin wrote:
> This *works*, but somehow I feel that this is where the JOIN should come 
> in.

That *is* a join. Just different syntax.

-- 
   Darren New / San Diego, CA, USA (PST)
     On what day did God create the body thetans?

Post a reply to this message

Dan Byers wrote:
> BTW we're talking about a telco billing
> application with over 300 tables ...

> They do use a large number of stored procs for
> the real heavy-lifting (I counted almost 700).

Is that all? The PREMIS system (which the "baby bells" use in the USA) 
has 100 million lines of SQL code and 300+ terabytes of data.

-- 
   Darren New / San Diego, CA, USA (PST)
     On what day did God create the body thetans?

Post a reply to this message

"Dan Byers" <goofygraffix_at_geemail_dot_com> wrote in message
news:web.47b1d8a8e388923da8d0b25a0@news.povray.org...
> "Gail Shaw" <initialsurname@sentech sa dot com> wrote:

> 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.

No defense. SQL code embedded in the app means that changes to the DB
structure are near-impossible, and if the code isn't performing properly, a
DBA can't do anything

> BTW we're talking about a telco billing
> application with over 300 tables

> They do use a large number of stored procs for
> the real heavy-lifting (I counted almost 700).

Is that all? I count in one of my DBs and I've got close on 1000 tables and
3000 procs. Couple hundred views as well. This is for an app with fully
customisable searches, screens where the users can customise what columns
they see, from what tables, if they don't like the default

Post a reply to this message

Gail Shaw wrote:

> "Tom Austin" <taustin> wrote in message news:47b203ce$1@news.povray.org...
> 
>> My SQL statement is likely less efficient than using JOIN.
>> depending on the DB being used
> 
> On MS SQL Server, they will be identical.
> 
>> 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?
> 
> Not offhand. I could probably write an explaination for you (tomorrow) that
> might be clear.
> What are you confused about?
> 

Actually you have already provided some good examples in your other posts.

So, what I am really picking up is:

My SQL statement is perfectly valid, but is not robust.  A 'join' might 
be left out which can cause a very costly query - tho on a small DB this 
might not matter except for causing a 'bug'.


Using JOIN provides for a more robust way of querying multiple tables. 
You are required to provide the match condition.


Using my example SQL method is perfectly valid, but requires more caution.



Thanks Gail, I really do appreciate your responses.


Tom

Post a reply to this message