The where is now only used for specifying selection criteria. 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. When two tables are joined with an inner join, data will only be returned if matching data exists in both tables.
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. Comment them back in if you want to try them. 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.