![]() 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. 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. There were restrictions to this syntax, even when they were supported. Conversely, for the right outer join operator, =*, the second table became the “outer member”, from which all rows were selected that met the criteria. 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. 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. 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. 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. 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. The ANSI-89 table citation list (FROM tableA, tableB) is still ISO standard for INNER JOINs only. ![]() Queries that use this syntax will fail when the database engine level is 10 (SQL Server 2008) or later (compatibility level 100). The “old style” Microsoft/Sybase JOIN style for SQL, which uses the =* and *= syntax, has been deprecated and is no longer used. ![]() 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. He is a regular contributor to Simple Talk and SQLServerCentral. Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 30 years of experience with database-intensive applications.ĭespite 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. ![]()
0 Comments
Leave a Reply. |