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

Post a reply to this message

"Tom Austin" <taustin> a écrit dans le message de news: 
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.

-- 
*****************************
http://www.oyonale.com
*****************************
- Graphic experiments
- POV-Ray, Cinema 4D and Poser computer images
- Posters

Post a reply to this message

Tom Austin <taustin> 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

Inner Join equivalent:

SELECT  L.TYPE
FROM    PERSON P INNER JOIN LIMBS L ON
             P.INDEX = L.PERSONINDEX
WHERE   P.NAME = 'Bones'

Your example would be fine, although supposedly M$ frowns on that and is trying
to steer people away from it.

An Inner Join will only pull where there's a match in both tables.

The left outer join pulls all the records on the left side of the join and any
matching records from the right side table, or NULL if no matches are made.  A
right outer join works in the opposite direction (but I've never used it, so I
don't know for sure).

This is an outer join example:

SELECT  P.NAME, L.TYPE
FROM    PERSON P LEFT OUTER JOIN LIMBS L ON
             P.INDEX = L.PERSONINDEX

Here, I want to pull all the P.NAMES from PERSON, regardless if they have limbs
or not.


Hopethishelps...

--
Dan
GoofyGraffix.com

Post a reply to this message

Dan Byers wrote:

> Your example would be fine, although supposedly M$ frowns on that and is trying
> to steer people away from it.

isn't the join syntax ANSI SQL? I thought the simpler syntax was 
actually a feature of Transact SQL, (Though I think other RDBMS engines 
use it, too...)

Post a reply to this message

Mike Raiford escribió:
> Dan Byers wrote:
> 
>> Your example would be fine, although supposedly M$ frowns on that and 
>> is trying
>> to steer people away from it.
> 
> isn't the join syntax ANSI SQL? I thought the simpler syntax was 
> actually a feature of Transact SQL, (Though I think other RDBMS engines 
> use it, too...)

I think what Dan meant is that Tom's original example is frowned upon, 
and people should use JOINs instead.

Post a reply to this message

Nicolas Alvarez wrote:
> Mike Raiford escribió:
>> Dan Byers wrote:
>>
>>> Your example would be fine, although supposedly M$ frowns on that and 
>>> is trying
>>> to steer people away from it.
>>
>> isn't the join syntax ANSI SQL? I thought the simpler syntax was 
>> actually a feature of Transact SQL, (Though I think other RDBMS 
>> engines use it, too...)
> 
> I think what Dan meant is that Tom's original example is frowned upon, 
> and people should use JOINs instead.

But, frowned upon in the general sense?

Post a reply to this message

Mike Raiford <mra### [at] hotmailcom> wrote:
> Nicolas Alvarez wrote:
> > Mike Raiford escribió:
> >> Dan Byers wrote:
> >>
> >>> Your example would be fine, although supposedly M$ frowns on that and
> >>> is trying
> >>> to steer people away from it.
> >>
> >> isn't the join syntax ANSI SQL? I thought the simpler syntax was
> >> actually a feature of Transact SQL, (Though I think other RDBMS
> >> engines use it, too...)
> >
> > I think what Dan meant is that Tom's original example is frowned upon,
> > and people should use JOINs instead.
>
> 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 rows selected by a query are filtered first by the FROM clause join
conditions, then the WHERE clause search conditions, and then the HAVING clause
search conditions. Inner joins can be specified in either the FROM or WHERE
clause without affecting the final result.

Outer join conditions, however, may interact differently with the WHERE clause
search conditions, depending on whether the join conditions are in the FROM or
WHERE clause. Therefore, the ability to specify Transact-SQL outer joins in the
WHERE clause is not recommended, is no longer documented, and will be dropped in
a future release."

What this translates to is the following code produces vastly different results:

SELECT  P.NAME, L.TYPE
FROM    PERSON P LEFT OUTER JOIN LIMBS L ON
             P.INDEX = L.PERSONINDEX
             AND P.NAME = 'Bones'

SELECT  P.NAME, L.TYPE
FROM    PERSON P LEFT OUTER JOIN LIMBS L ON
             P.INDEX = L.PERSONINDEX
WHERE   P.NAME = 'Bones'

The first example pulls everyone in PERSON and any limbs they have, provided the
P.NAME = 'Bones'.  If there are no matches, L.TYPE will be null.  Exactly how
it's supposed to work.  In example number 2, the WHERE clause acts like a
global filter, so it will throw out any record that doesn't equal 'Bones' in
the P.NAME field, therefore acting like an INNER JOIN.

--
Dan
GoofyGraffix.com

Post a reply to this message

"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:
> "Tom Austin" <taustin> a écrit dans le message de news: 
> 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

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