May 23, 2000
Are you confused by all this "inner join" style syntax that is becoming more and more prominent lately?
It's happening because Microsoft is moving towards using ANSI syntax. The old syntax still works, but it is strongly recommended you learn the new syntax, Microsoft have stated that they will not support the old syntax indefinitely.
It's fairly easy to translate from the old to the new syntax, and correctly translated results will usually produce identical results, but there are exceptions that you should be aware of.
Inner or natural joins
Inner joins return rows where data matching exists in the tables you are joining. This is the simplest type of join, and moving between the old and new syntax should present no problems. In general, this:
Is equivalent to this:
Note that the components of the
Incidentally, what the query above does is to display table names and index names in the current database where the table has a Clustered Index. The sysindexes.indid column is always set to one for clustered indexes.
I will use similar examples later on, and if you are not too clear on these system tables, then now would be a good time for you to review the documentation on sysobjects and sysindexes in Books Online.
Update and Delete
These statements also support the ANSI join syntax, although it's not very well documented in Books Online for some versions of SQL Server
When two tables are joined with an inner join, data will only be returned if matching data exists in both tables. An outer join is like saying "and also include the rows from one table if there are no matching rows in the other one."
With an outer join the columns from the table where data is "missing" are returned as NULL values.
Outer joins come in two basic flavours, called Left and Right. Left outer joins mean that the data must be contained in the table defined to the left side of the equivalence, but not necessarily the right hand side. Right outer joins, of course, work the other way around.
To illustrate this, cut and paste the code below into a Query Analyser window and try running it. I have used the newer ANSI syntax here, and the older equivalents are included but commented out using the "--" comment notation. Comment them back in if you want to try them.
Outer Join Gotchas
Unlike natural joins, outer joins have a few traps you can fall into if you translate your query from the old syntax to the new and expect it to produce the same results in both formats.
Returning to our sysobjects and sysindexes examples, the following two queries should produce the same results
However, if we qualify the select criteria on sysindexes as follows, in order to look for details on clustered indexes, the two queries should return different results.
The reason this happens is that the SQL Server reacts differently to the "Where" clause when the outer joined table does not have any data.
When no data is found in the sysindexes table that matches the
Full outer joins
Full outer joins effectively combine the left and right outer joins so that data will be returned if it matches in both tables, or if it exist in either one.
The old join syntax has no direct equivalent of the full outer join.
Try out the SQL below to illustrate the full outer join.
Cross Joins create a Cartesian Product, rather like when you forget to include a "where" clause to join two tables. There are not many cases where you would want to use a cross join, but this article discusses one possible use for them.
Prior to ANSI SQL, if you wanted to find records in one table that did not match a record in another, the recommended solution was to use a "NOT IN" or "NOT EXISTS" based query. It is now recommended that you use outer joins instead. Here is a simple example using all three possibilities-they should all produce the same results:
Beware of falling into the trap we discussed earlier. Looking at our previous "Clustered Index" example, either of these two following old-style queries would produce a list of system tables that do not have clustered indices:
Here is a simple translation of the above queries into the new "left outer join" style. Try it out and see what happens.
No rows are returned.
The query now fails because the
To get around this, the following example uses a "derived
query" which forces a select on sysindexes to return a subset of rows with
In effect, the derived query--
creates a notional table called "i", which contains a copy of all the sysindexes rows with an indid of 1. It is this notional table that is then fed to the rest of the query. The final results should now match the NOT EXISTS and NOT IN examples shown above.
About the author
Neil Boyle is an independant SQL Server consultant working out of London, England. Neil's free SQL Server guide is available on-line at http://www.impetus-sql.co.uk.