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

SELECT Customers.CustomerId, Company, OrderID
FROM Customers C, Orders O
WHERE C.CustomerID = O.CustomerID (+)

SELECT Customers.CustomerId, Company, OrderID
FROM Customers C, Orders O
WHERE C.CustomerID *= O.CustomerID

SELECT Customers.CustomerId, Company, OrderID
FROM Customers C LEFT OUTER JOIN Orders O
ON (C.CustomerID = O.CustomerID)

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

SELECT Customers.CustomerId, Company, OrderID
FROM Customers NATURAL JOIN Orders USING (CustomerID)

SELECT Customers.CustomerId, Company, OrderID
FROM Customers C, Orders O
WHERE C.CustomerID (+) = O.CustomerID (+)

SELECT Customers.CustomerId, Company, OrderID
FROM Customers C FULL OUTER JOIN Orders O
ON (C.CustomerID = O.CustomerID)

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.