| 6/5/2002
-- Someone once said that when it comes to change, you either need to grab on
to it or fight it -- in either case, it will be a bumpy ride. Luckily, this
is not always true in our industry, but change is a constant.
Generally, I have found change to be a good thing and usually am one to embrace
it and see where it can take me. Some of my clients and students, on the other
hand, aren't too crazy about it. Granted there is something to be said for the
"If it ain't broke don't fix it" mentality, but what if it was broke to begin
with? In this last category is where I believe SQL join syntax falls -- it was
already broken and needed fixing. Oracle 9i finally allows the developer to
make use of ANSI SQL 1999 join syntax when it comes to joins in a SELECT statement.
While the standard was adopted some time ago and already embraced by other vendors'
products, I suspect that Oracle wanted to be sure that there wouldn't be any changes
to the standard before adopting it (plus, this gave them more time to
make sure their code worked).
So, why the big hullabaloo about SQL 99 join syntax? Simple, it 's a standard!
Prior to the SQL 99 standard, there was no commonality in the way that anything
other than a simple equijoin or a cross join (i.e., Cartesian product) was implemented
across all vendors' product lines. The only thing you could be sure of was that
these two SELECT statements would work in Oracle, DB2, Sybase, Microsoft SQL
Server, etc.:
SELECT CustomerID, Orders.OrderNum,
ProductId, ProductName, Qty, Price
FROM Orders, OrderDetails
WHERE Orders.OrderNum = OrderDetails.OrderNum
SELECT * FROM Customers, Orders
But what if you wanted to see all customers along with the orders they placed,
even if they placed no orders, (i.e., a simple outer join)? For this, you had
no commonality between relational database products. For example, the query would
be written as follows in Oracle:
SELECT Customers.CustomerId, Company, OrderID
FROM Customers C, Orders O
WHERE C.CustomerID = O.CustomerID (+)
However, the same query would be written like this in Microsoft SQL Server or
Sybase:
SELECT Customers.CustomerId, Company, OrderID
FROM Customers C, Orders O
WHERE C.CustomerID *= O.CustomerID
Now, thanks to ANSI SQL 99 syntax, the same query can be re-written to work in
both Oracle and SQL Server (as well as DB2 and others) like so:
SELECT Customers.CustomerId, Company, OrderID
FROM Customers C LEFT OUTER JOIN Orders O
ON (C.CustomerID = O.CustomerID)
ANSI SQL 99 join syntax in general also provides an additional benefit: readability.
Separating the join condition from the WHERE clause makes it really easy to determine
what the elements of the join are versus what conditions have been applied to
limit the data returned -- even for complex SQL statements. Take this example
using classic Oracle SQL syntax and new SQL 99 syntax:
SELECT c.CourseName, s.StartDate,
i.FirstName || ' ' ||i.LastName as Instructor, l.City
FROM ScheduledClasses s, Instructors i, Courses c, Locations l
WHERE s.InstructorID = i.InstructorID
AND s.CourseNumber = c.CourseNumber
AND s.LocationId = l.LocationID
AND l.Country = ‘USA'
SELECT c.CourseName, s.StartDate,
i.FirstName || ' ' ||i.LastName as Instructor, l.City
FROM Instructors i
JOIN ScheduledClasses s ON (i.InstructorID = s.InstructorID)
JOIN Courses c, ON (s.CourseNumber = c.CourseNumber)
JOIN Locations l ON (s.LocationId = l.LocationID)
WHERE l.Country = ‘USA'
In the second code example, using SQL 99 syntax, the join elements are clearly
identified and separate from the WHERE clause. This may not seem like a big
deal, but I've written some SQL statements that went on and on, and even had
join conditions among selectivity criteria that were hard to figure out two
weeks later -- even with comments! Being able to separate join criteria from
selectivity criteria is a useful thing. Now, if you're still not convinced,
here are a few more reasons to use ANSI join syntax in your code instead of
the classic Oracle syntax:
1) You don't need to specify the join condition in a natural join --
it is automatically built by the RDBMS. All you need to write is:
SELECT Customers.CustomerId, Company, OrderID
FROM Customers NATURAL JOIN Orders
Oracle will take that statement and determine what the common columns are in both
tables (by matching column names) and create a join condition using all common
columns on the fly and then return the result set. This is nice, but I would not
recommend it. What if you add a column to either table with a name that exists
in the other? In this case, Oracle will join all columns whose names match and
make them part of the join condition -- this can be somewhat unpredictable. File
this one under "nice to have but no thanks" and always use the ON clause of the
join syntax. I should mention that the NATURAL JOIN does allow you to specify
a USING clause that tells Oracle which column to use for the NATURAL JOIN, but
if you are going to do that, why not just use the ON clause? Following is an example
of the USING clause (notice that you cannot qualify the column since it must exist
in both tables making up the join):
SELECT Customers.CustomerId, Company, OrderID
FROM Customers NATURAL JOIN Orders USING (CustomerID)
2) You can do more with ANSI SQL 99 join syntax than with classic Oracle syntax.
For example, in ANSI syntax you can have a LEFT OUTER JOIN, RIGHT OUTER JOIN
or FULL OUTER JOIN, which retrieves rows from both sides of the join condition,
whether or not they match. You cannot do a FULL OUTER JOIN in classic Oracle
syntax because this statement would return an error:
SELECT Customers.CustomerId, Company, OrderID
FROM Customers C, Orders O
WHERE C.CustomerID (+) = O.CustomerID (+)
However, this is perfectly fine:
SELECT Customers.CustomerId, Company, OrderID
FROM Customers C FULL OUTER JOIN Orders O
ON (C.CustomerID = O.CustomerID)
Now, it's not that you can't do a FULL OUTER JOIN in classic Oracle syntax, but
it is a bit more work and requires the UNION operator, as seen here:
SELECT Customers.CustomerId, Company, OrderID
FROM Customers C, Orders O
WHERE C.CustomerID (+) = O.CustomerID
UNION
SELECT Customers.CustomerId, Company, OrderID
FROM Customers C, Orders O
WHERE C.CustomerID = O.CustomerID (+)
3) There is no performance penalty. The use of ANSI SQL 99 syntax does
not slow your database down (or speed it up).
4) In the long run you will see more of it, and less of the older syntax.
Getting comfortable with it now is a good thing.
5) It's portable. I know I mentioned this one already, but if you work
with more than one database or develop applications that make use of more than
one database, it's a big time saver).
6) It's easier to understand what is happening (or did I mention that
already?).
So join me in creating portable SQL joins that are easy to read, do more than
you can with Oracle syntax, and don't slow you down. 
|
