Will ANSI JOIN vs. non-ANSI JOIN queries perform differently?

by

The two queries are the same, except the second is ANSI-92 SQL syntax and the first is the older SQL syntax which didn’t incorporate the join clause. They should produce exactly the same internal query plan, although you may like to check.

You should use the ANSI-92 syntax for several of reasons

  • The use of the JOIN clause separates
    the relationship logic from the
    filter logic (the WHERE) and is thus
    cleaner and easier to understand.
  • It doesn’t matter with this particular query, but there are a few circumstances where the older outer join syntax (using + ) is ambiguous and the query results are hence implementation dependent – or the query cannot be resolved at all. These do not occur with ANSI-92
  • It’s good practice as most developers and dba’s will use ANSI-92 nowadays and you should follow the standard. Certainly all modern query tools will generate ANSI-92.
  • As pointed out by @gbn, it does tend to avoid accidental cross joins.

Myself I resisted ANSI-92 for some time as there is a slight conceptual advantage to the old syntax as it’s a easier to envisage the SQL as a mass Cartesian join of all tables used followed by a filtering operation – a mental technique that can be useful for grasping what a SQL query is doing. However I decided a few years ago that I needed to move with the times and after a relatively short adjustment period I now strongly prefer it – predominantly because of the first reason given above. The only place that one should depart from the ANSI-92 syntax, or rather not use the option, is with natural joins which are implicitly dangerous.

More Related Contents:

  1. Update a table using JOIN in SQL Server?
  2. Combine multiple results in a subquery into a single comma-separated value
  3. Joining multiple tables returns NULL value
  4. Which join syntax is better?
  5. Removing duplicate rows (based on values from multiple columns) from SQL table
  6. SQL server query to output the data shown below
  7. Find a value anywhere in a database
  8. DateTime2 vs DateTime in SQL Server
  9. How to check if a column exists in a SQL Server table
  10. How to select all records from one table that do not exist in another table?
  11. Select columns from result set of stored procedure
  12. SET versus SELECT when assigning variables?
  13. Generate Dates between date ranges
  14. Select group of rows that match all items in a list
  15. Pass Dictionary to Stored Procedure T-SQL
  16. How to print VARCHAR(MAX) using Print Statement?
  17. Query error with ambiguous column name in SQL
  18. Why (and how) to split column using master..spt_values?
  19. Splitting delimited values in a SQL column into multiple rows
  20. What does =* mean?
  21. Date conversion and culture: Difference between DATE and DATETIME
  22. SQL: parse the first, middle and last name from a fullname field
  23. How to remove accents and all chars a..z in sql-server?
  24. SQL Select Upcoming Birthdays
  25. How can I do something like: USE @databaseName
  26. How can I include null values in a MIN or MAX?
  27. How to split string and insert values into table in SQL Server
  28. Avg of float inconsistency
  29. Get the last day of the month in SQL
  30. SQL take just the numeric values from a varchar

Leave a Comment