Mark Rittman
What Are ANSI Joins, And
Why Should I Use Them?
If you've
ever had to port a Microsoft SQL Server or Access database over to
Oracle, you've probably come across the different way joins are put
together for these databases. For example, you might come across a
query looking like this;
SELECT pub_name, titles
FROM publishers
INNER JOIN titles ON publishers.pub_id = titles.pub_id
At first you
might think that this is some new dialect of SQL that Microsoft have
introduced for their databases; in fact, what's actually being used
here is the ANSI SQL/92 syntax for joins, which SQL Server and Access
have always used as their 'default' join syntax. Historically, Oracle
supported the SQL/86 standard and included their own proprietary
syntax to handle areas such as outer joins.
Starting with
Oracle 9i however, Oracle have now included support for many ANSI
SQL/99 features including ANSI compliant joins, and there are several
advantages in using this new syntax, one of which is the separation of
the join condition from the WHERE clause. For example, in this
example given by Damir Bersinic, the first example uses our
traditional way of creating joins, whilst the second uses the new ANSI
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'
As you can
see, the conditions for joining the tables are now separated from
those used to limit down the query.
Jim
Czuprynski has put together a
good explanation of how ANSI compliant joins work, including
step-by-step examples of the various types of natural, equijoins,
left, right and full outer joins are put together; in addition,
cartesian joins now have to be explicitely defined, which makes them
harder to create by mistake.
Jonathon
Gennick recently
wrote a piece for OTN on ANSI joins and, in his view, one of the
key benefits of the new syntax is the support for full outer joins.
Previously, full outer joins had to be simulated through using UNION
clauses; now they can be explicitely defined in the statement, leading
to faster execution and easier to read queries.
One thing to
be aware of though is the NATURAL JOIN facility, which allows you to
miss off the join condition completely if the tables concerned share
common column names on which you want to join. For example, if
your STUDENT and CLASS table share a common STUDENT_NO column, you
could NATURAL JOIN them thus;
SELECT student_name, class_name
FROM student NATURAL JOIN class However, it's
ambiguous at best and leaves you open to problems if columns get added
or renamed, no more than two tables can be joined using this method,
and it gives you little control over the specifics of a join if
columns join across the tables in an unusual way. Tom's advice with
NATURAL JOINs- forget that they exist.
So which
should I use - ANSI joins, or 'traditional' joins? Well, it's down to
you in the end. There's no performance benefit or hit by using ANSI
joins rather than traditional joins, but by using ANSI joins, your
queries are more portable between DBMS platforms, and they're a bit
easier to read. In the end, though, it's down to personal preference
and whilst there's advantages to the ANSI standard, there's no need to
switch if you don't want to.
For more
details check out
this OTN article, together with this article by
Sanjay Mishra for O'Reilly Network on
Full Outer Joins in Oracle 9i.
|