[thelist] sql statement

[Ken Schaefer]

INNER JOIN requires a match on both sides.

OUTER JOIN does not require a match, and where there is no match, a NULL will
be substituted. Depending on whether you use a LEFT OUTER JOIN, RIGHT OURTER
JOIN, or FULL OUTER JOIN, you will get NULLs on the right, left, or both
sides.

-- always require a match in table1 with table2
INNER JOIN
	Table2
ON
	Table1.ID = Table2.ID

-- if no match in table2, then select the fields from 
-- table1, and put in NULLs for the table2 fields
LEFT OUTER JOIN
	Table2
ON
	Table1.ID = Table2.ID

-- if no match in table1, then select the fields from 
-- table2, and put in NULLs for the table1 fields
RIGHT OUTER JOIN
	Table2
ON
	Table1.ID = Table2.ID

-- select all records, and if no match
-- substitute a NULL on either the left or right
FULL OUTER JOIN
	Table2
ON
	Table1.ID = Table2.ID

Cheers
Ken

--
www.adOpenStatic.com/cs/blogs/ken/ 

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: From: thelist-bounces at lists.evolt.org [mailto:thelist-
: bounces at lists.evolt.org] On Behalf Of Donnie Blaker
: Subject: Re: [thelist] sql statement
: 
: if you dont mind me asking, whats the difference between an inner and an
: outer
: 
: >From: Ed McCarroll <Ed at ComSimplicity.com>
: >Reply-To: "thelist at lists.evolt.org" <thelist at lists.evolt.org>
: >To: "thelist at lists.evolt.org" <thelist at lists.evolt.org>
: >Subject: Re: [thelist] sql statement
: >Date: Tue, 29 Mar 2005 13:52:24 -0800
: >
: >To list all size names for product 213:
: >
: >   SELECT size.name
: >   FROM size S
: >   INNER JOIN prod_size PS
: >     ON S.sid = PS.sid
: >   WHERE PS.prodid = 213
: >
: >or equivalent...
: >
: >--
: >Ed McCarroll                                  (310) 838-4330
: >Ed at ComSimplicity.com            http://www.ComSimplicity.com
: >
: >
: >Quoting Donnie Blaker <filterhead80 at hotmail.com>:
: >
: > > I have two tables.  sizes and prod_size.
: > >
: > > size has sid, name, abrv
: > > prod_size has sid, prodid
: > >
: > > when I do this look up I would like in one query get the all the
: > > sizes for
: > > that product.  normally I would do two queries but I dont think
: > > that is the
: > > correct way.
: > >
: > > does someone know what I would use?  like left join or join or
: > > something
: > > like that
: > >
: > > thanks