20 December 2017

1 Comment

20 December 2017

1 Comment

Finding code smells using SQL Prompt: old-style join syntax (ST001)

Guest post

This is a guest post from Phil Factor. Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 30 years of experience with database-intensive applications.

Despite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career.

He is a regular contributor to Simple Talk and SQLServerCentral.

SQL Prompt implements a static code analysis rule, ST001, which will check code automatically, during development and testing work, for occurrences of non-ANSI standard JOIN syntax.

The “old style” Microsoft/Sybase JOIN style for SQL, which uses the =* and *= syntax, has been deprecated and is no longer used. Queries that use this syntax will fail when the database engine level is 10 (SQL Server 2008) or later (compatibility level 100). The ANSI-89 table citation list (FROM tableA, tableB) is still ISO standard for INNER JOINs only. Neither of these styles are worth using. It is always better to specify the type of join you require, INNER, LEFT OUTER, RIGHT OUTER, FULL OUTER and CROSS, which has been standard since ANSI SQL-92 was published. While you can choose any supported JOIN style, without affecting the query plan used by SQL Server, using the ANSI-standard syntax will make your code easier to understand, more consistent, and portable to other relational database systems.

Old-style outer joins are obsolete

When SQL Server forked from Sybase, it inherited its old non-standard Transact-SQL syntax for joins, which included the = and = syntax, for left and right outer joins, respectively.

The left outer join operator, =, selected from the first table (the “outer member” of the outer join) all rows that met the statement’s restrictions. The second table (“inner member”) generated values only if there is a match on the join condition for that row; otherwise it provided null values. Conversely, for the right outer join operator, =, the second table became the “outer member”, from which all rows were selected that met the criteria.

There were restrictions to this syntax, even when they were supported. You couldn’t include a Transact-SQL outer join in a HAVING clause, and you couldn’t do an additional INNER JOIN in the same expression as an old-style outer join. In addition, the outer join syntax (= or =) does not always give the correct results, sometimes using a cross join when an outer join is specified.

In any event, this syntax was deprecated in SQL Server 2005 onwards, and stopped working is SQL Server 2008. The intent of Listing 1, which queries the pubs database, is to return all titles that don’t have a corresponding author.

Listing1

However, it will fail in SQL Server 2008 or later unless you set compatibility level to 90. This setting was only possible up to SQL Server 2012:

Msg 102, Level 15, State 1, Line 6
Incorrect syntax near '*='.

If you still have queries that use this syntax, you’ll have to rewrite them to use the ANSI standard syntax, shown in Listing 2, before upgrading beyond SQL Server 2012, because the compatibility level 90 is no longer supported.

Listing 2

This gives the following result:

Old-style inner joins are supported but offer no advantages

This table citation syntax for inner joins is part of the ANSI standard and so still supported. Listing 3 uses it, and will return all authors that live in the same city as their publisher.

–(Old Syntax)authors that live in the same city as their publishers

Listing 3

Listing 4 shows the same code using the ANSI-92 standard, where we make the join types explicit.

Listing 4

Both give the same result, with identical execution plans.

However, it is broadly accepted that the old-style ‘citation list’ inner join syntax is much harder to read and understand, and therefore more error prone than the newer syntax.

In any case, there is no cause for regret at leaving behind this old style syntax, even though it is still supported. How, for example, would you determine the percentage of authors that live in different cities from their publishers, using the old style join syntax? It would be a complex-looking query using brackets and subqueries. With the newer syntax, it is simple to write, simple to understand its logic.

Listing 5

Conclusions

There are no advantages to leaving old-style join syntax in legacy code. If you spot this code smell, it will improve and simplify the code to rewrite the statements to use ANSI-standard join syntax.

Guest post

This is a guest post from Phil Factor. Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 30 years of experience with database-intensive applications.

Despite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career.

He is a regular contributor to Simple Talk and SQLServerCentral.

Share this post.

Share on FacebookShare on Google+Share on LinkedInTweet about this on Twitter

You may also like

  • Article

    Customizing the SQL Prompt built-in snippets: a better ALTER TABLE ADD (ata) snippet

    Snippets are a great feature of SQL Prompt. They save coding time, and introduce standards and consistency to the way you build code modules. They have multiple replacement points (placeholders) for parameters, and you can invoke them directly from an SSMS query pane. SQL Prompt also comes with many useful built-in snippets, but sometimes we

  • Article

    SQL Prompt code analysis: avoid non-standard column aliases (ST002 and DEP021)

    There was a time when column aliases in SQL Server and Sybase were only declared by an assignment expression, and some people are still fond of the old way of doing it, in much the same way as some of us still like line-dancing, or singing madrigals dressed in tights. Nowadays, the other relational database

  • Article

    Cleaning up common T-SQL coding issues with SQL Prompt

    Some SQL coding habits are just annoying. Commas in front of column names? No way! Others are actively harmful; they’ll make your code error prone, harder for others to read and understand, and even harder to edit without making mistakes. This article covers five harmful problems that I see regularly in T-SQL code, and shows

  • Article

    SQL Prompt Code Analysis: Table does not have clustered index (BP021)

    With a few exceptions, every table should have a clustered index. However, they are not always essential for performance. The value of a clustered index depends on the way a table is used, the typical pattern of queries, and how it the table is updated. More important for a table is that it should have

  • University

    Take the SQL Prompt course

    This Redgate University course takes you from installation all the way up to getting the most out of the advanced operations in SQL Prompt. As well as autocompleting your code, you’ll learn how SQL Prompt can help you with code formatting, object renaming, code analysis, and other useful tip and tricks.

  • Forums

    SQL Prompt Forum

    Write, format, and refactor SQL effortlessly