Sql server® 2012 t-sql fundamentals

Inner Join Safety

I strongly recommend that you stick to the ANSI SQL-92 join syntax because it is safer in several ways. 

Suppose you intend to write an inner join query, and by mistake you forget to specify the join condi-

tion. With the ANSI SQL-92 syntax, the query becomes invalid, and the parser generates an error. For 

example, try to run the following code.

SELECT E.empid, E.firstname, E.lastname, O.orderid 

FROM HR.Employees AS E 

  JOIN Sales.Orders AS O;

You get the following error:

Msg 102, Level 15, State 1, Line 3 

Incorrect syntax near ';'.

Even though it might not be immediately obvious that the error involves a missing join condition, 

you will figure it out eventually and fix the query. However, if you forget to specify the join condition 

when you are using the ANSI SQL-89 syntax, you get a valid query that performs a cross join.

SELECT E.empid, E.firstname, E.lastname, O.orderid 

FROM HR.Employees AS E, Sales.Orders AS O;

Because the query doesn’t fail, the logical error might go unnoticed for a while, and users of your 

application might end up relying on incorrect results. It is unlikely that a programmer would forget to 

specify the join condition with such short and simple queries; however, most production queries are 

much more complicated and have multiple tables, filters, and other query elements. In those cases, 

the likelihood of forgetting to specify a join condition increases. 

www.it-ebooks.info

---

106  

Microsoft SQL Server 2012 T-SQL Fundamentals

If I’ve convinced you that it is important to use the ANSI SQL-92 syntax for inner joins, you might 

wonder whether the recommendation holds for cross joins. Because no join condition is involved, you 

might think that both syntaxes are just as good for cross joins. However, I recommend staying with 

the ANSI SQL-92 syntax with cross joins for a couple of reasons—one being consistency. Also, suppose 

you do use the ANSI SQL-89 syntax. Even if you intended to write a cross join, when other developers 

need to review or maintain your code, how will they know whether you intended to write a cross join 

or intended to write an inner join and forgot to specify the join condition?