More JOIN Fun (Or is it?)

[Good Man]

Hi there

Yes, I've read about JOINs, albeit after coding for a couple of years
already using queries like the following:

"SELECT m.LastName, m.FirstName, o.Address FROM members m, offices o WHERE
o.City='$vCity' AND m.Status<>'Reti red' AND m.Status<>'Susp ended' AND
o.MemberID=m.Me mberID ORDER BY LastName,FirstN ame"

($vCity comes from a drop-down list of city names)
Now... everything works just fine, and has for a long time.... but reading
this NG, and some online articles about JOINs has be wondering/perplexed...

What I am hoping for is someone who knows MySQL and is really bored to
perhaps explain why the above query is NOT a 'real' join (which i don't
think it is), and why that's necessarily BAD.

How can I take that query and turn it into a 'real' join, and more
importantly, why should I? What is 'wrong' with queries like the one
above?

Thanks - not interested in flames, etc, just someone who can take a bit of
time to explain....

[Bill Karwin]

Good Man wrote:

"SELECT m.LastName, m.FirstName, o.Address FROM members m, offices o WHERE
o.City='$vCity' AND m.Status<>'Reti red' AND m.Status<>'Susp ended' AND
o.MemberID=m.Me mberID ORDER BY LastName,FirstN ame"

...perhaps explain why the above query is NOT a 'real' join (which i don't
think it is), and why that's necessarily BAD.
That looks like a real join to me, according to the SQL-89 language
specification. Prior to 1992, all joins followed that format.

Then people realized that this syntax didn't allow for more complex
joins. The most common of which was outer joins, where you want
everything from table1 even if there are no matching records in table2
(or vice versa). So Oracle and Microsoft and some others came up with
their own syntax extensions to SQL to handle these cases.

For example, you might see "(+)" used in Oracle expressions, to extend
comparison operators to mean, "field X must equal the following
expression, or field X must be absent (that is, NULL) because the outer
join matches no records from that field X's table".

The ANSI SQL committee that managed the SQL language defined more
standard syntax to support outer joins in their revision of the SQL
standard published in 1992. This might be what you're thinking of as a
"real" join. Of course the SQL-89 style joins are still valid for
backward compatibility, though they still can't represent outer joins
except through vendors' proprietary syntax.

The SQL-89 syntax is not necessarily bad. If inner joins are all you
need, then you can use either SQL-89 syntax or SQL-92 syntax with equal
results. Virtually all RDBMS vendors sooner or later implemented
support for the SQL-92 syntax. For this reason, you should be able to
mix SQL-89 style and SQL-92 (even in a single query, in some RDBMS
implementations ).
How can I take that query and turn it into a 'real' join, and more
importantly, why should I? What is 'wrong' with queries like the one
above?

Those queries are only "wrong" if you need to enhance the query to
support outer joins, and you want to comply with the ANSI/ISO SQL
standard instead of using proprietary syntax for the brand of RDBMS
you're using.

If you want to follow the SQL-92 syntax for joins, I'd rewrite the query
as follows:

SELECT m.LastName, m.FirstName, o.Address
FROM members AS m INNER JOIN offices AS o
ON m.MemberID = o.MemberID
WHERE o.City='$vCity' AND m.Status NOT IN ('Retired', 'Suspended')
ORDER BY m.LastName, m.FirstName

(Okay, even though it has nothing to do with rewriting the join syntax,
I couldn't help myself from using NOT IN. :-)

From that, it's a pretty simple alteration to make the query include
offices that have no members:

SELECT m.LastName, m.FirstName, o.Address
FROM members AS m RIGHT OUTER JOIN offices AS o
ON m.MemberID = o.MemberID
WHERE o.City='$vCity' AND m.Status NOT IN ('Retired', 'Suspended')
ORDER BY m.LastName, m.FirstName

Regards,
Bill K.

[Good Man]

Bill Karwin <bi**@karwin.co m> wrote in
news:d1******** *@enews3.newsgu y.com:

Then people realized that this syntax didn't allow for more complex
joins. The most common of which was outer joins, where you want
everything from table1 even if there are no matching records in table2
(or vice versa).

I've come across this situation before and scratched my head.... hooray,
now I know the solution!

Thanks for your detailed explanation Bill.... much appreciated.

Regards,
GM