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, title 
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 =