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

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

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

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

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