ONJava.com -- The Independent Source for Enterprise Java
oreilly.comSafari Books Online.Conferences.


AddThis Social Bookmark Button
  The Effective Use of Joins in Select Statements
Subject:   Clear as mud
Date:   2004-01-17 22:37:12
From:   christianmlong

This has got to be the most obfuscating articles I have ever read on SQL. Never mind the goofy asides and the non-linear progression. The SQL is also poorly designed and, in some places, dead wrong! Now, how is someone supposed to learn anything about SQL like that? I'm an educator, teaching VB.Net, C++ and SQL, and I have to undo the confusion wrought by bad SQL articles like this one.

Errors: in this code snippet, the author uses no less that three different names for his composite table!

Select *
>From t1
left outer join
(select *
from t2,t3
where (any where clause involving t1, t2, t3)
) t2_t3_composite_table on t1_t2_composite_table.f1 = t1.f1
Where (any clause involving t1, t2_te_composite_table, t4, t5, t6)

In addition, the author mixes ANSI 92 SQL Join syntax and old style syntax in the same statement. Anyone learning SQL using this guide is guaranteed to get Cartesian products for tables 4, 5 and 6 following his example, because he does not point out the capital importance of joining all tables by their keys with ON clauses (unless you specifically want a Cartesian product). I can hear the calls to the DB admins now. "Uhh, this query is hung up." Well, yeah, you're returning 10^12 rows.

Compare this article to http://www.oreillynet.com/pub/a/network/2002/04/23/fulljoin.html, and you will see how muck easier SQL is to explain when we proceed stepwise, with clear, simple, logical examples.

1 to 1 of 1
  1. Author's Explanation
    2004-01-20 08:13:35  anonymous2 [View]

1 to 1 of 1