Converting Deprecated Outer Join to ANSI Outer Join

Question

Hi all,

I am trying to convert some Ansi sql code to new version of sql  for compliance with SQL Server 2005 code.

(Ansi SQL uses (1) ' =* ' for Right outer join   (2) '  *= ' for left outer join   and   ' = ' sign for inner join)

I have tried converting some code to newer version . Please see below and do let me know, is this right way of doing (or) not.

Thanks.

Code Snippet

Ansi syntax ( valid on 80 compatible databases ( SQL Server 2000) using '=*' for Right outer join and '=' sign for inner join)

Select   < Column 1...........Column n >
 FROM    v_unit v,
         device d,
         romver r1,                            
         romver r2,                               
         romver r3,                                        
         romver r4,                                                                
        otap_profile op
                       
 WHERE   v.cid = @intcid
      and v.status < 4
      and r1.dsn=*v.dsn and r1.module_id=0      
      and r2.dsn=*v.dsn and r2.module_id=1001   
      and r3.dsn=*v.dsn and r3.module_id=1003   
      and r4.dsn=*v.dsn and r4.module_id=2000       
      and d.dsn=v.dsn
      and op.opid=*d.opid

MOdified script for SQL Server 2005:

Select   < Column 1...........Column n >
        FROM    romver r1
                RIGHT OUTER JOIN v_unit v ON r1.dsn = v.dsn
                LEFT OUTER JOIN romver r2 ON v.dsn = r2.dsn
                LEFT OUTER JOIN romver r3 ON v.dsn = r3.dsn
                LEFT OUTER JOIN romver r4 ON v.dsn = r4.dsn
                INNER JOIN device d ON v.dsn = d.dsn
                LEFT OUTER JOIN otap_profile op ON d.opid = op.opid

        WHERE   v.cid = @intcid
                AND v.status < 4
                AND r1.module_id = 0     
                AND r2.module_id = 1001   
                AND r3.module_id = 1003   
                AND r4.module_id = 2000   

Answer 1

The likelyhood is high that the database engine can help you out.  Follow the process below , and check if works for you.

What we are doing is engaging the view designer, which translates automatically from old syntax to new syntax.

USE adventureworks; 

-- Make the query into a view for the sake of translation 
CREATE VIEW [dbo].[vANSITranslateQuery1] 
AS 
  SELECT SubCategory=psc.name, 
         ProductName=p.name, 
         listprice, 
         color 
  FROM   production.product p, 
         production.productsubcategory psc 
  WHERE  p.productsubcategoryid = psc.productsubcategoryid 
         AND color IS NOT NULL 

go 

-- Right Click on View, select Design 
-- Instant translation to new syntax in bottom query window 
SELECT psc.name AS SubCategory, 
       p.name   AS ProductName, 
       p.listprice, 
       p.color 
FROM   production.product AS p 
       INNER JOIN production.productsubcategory AS psc 
               ON p.productsubcategoryid = psc.productsubcategoryid 
WHERE  ( p.color IS NOT NULL ) 

go  

Kalman Toth
New Book:  Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016

---

Answer 2

ISAL_F wrote:

I am trying to convert some Ansi sql code to new version of sql  for compliance with SQL Server 2005 code.   (Ansi SQL uses (1) ' =* ' for Right outer join   (2) '  *= ' for left outer join   and   ' = ' sign for inner join)

Just to point out that =* is not in the SQL92 specification (as far as I can see anyway!)

 

Here's an online copy: http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt

Answer 3

SQLUSA,

 

The logic you said it will work If the 'Select' in the view contains only OLD INNER JOIN ( = symbol), but it we changed the select query to use OLD OUTER JOIN ( *= Symbol )  , then sql server 2005 throws error back saying this is not allowed in sql server 2005.

The query uses non-ANSI outer join operators ("*=" or "=*"). To run this query without modification, please set the compatibility level for current database to 80 or lower, using stored procedure sp_dbcmptlevel. It is strongly recommended to rewrite the query using ANSI outer join operators (LEFT OUTER JOIN, RIGHT OUTER JOIN). In the future versions of SQL Server, non-ANSI join operators will not be supported even in backward-compatibility modes.

 

Can someone please me know

 

(1) Is there any utility where we can input our old sql code ( SQL 2000 ) and get new sql code ( for SQL 2005).

 

(2) can someone let me know , the way I changed the code, is that correct.

 

Thanks.

Answer 4

The old syntax is not as simple as replacing =* with "right outer" and = with "inner" joins.  This is part of the reason this syntax was abandoned.

To more appropriately emulate your old query you would have to do something like this:

Select   < Column 1...........Column n >
        FROM    v_unit v

                LEFT OUTER JOIN romver r1 ON r1.dsn = v.dsn AND r1.module_id = 0     
                LEFT OUTER JOIN romver r2 ON v.dsn = r2.dsn AND r2.module_id = 1001   
                LEFT OUTER JOIN romver r3 ON v.dsn = r3.dsn AND r3.module_id = 1003   
                LEFT OUTER JOIN romver r4 ON v.dsn = r4.dsn AND r4.module_id = 2000   
                INNER JOIN device d ON v.dsn = d.dsn
                LEFT OUTER JOIN otap_profile op ON d.opid = op.opid

        WHERE   v.cid = @intcid
                AND v.status < 4
  
               
               
There is no "simple" conversion from the old syntax to the new syntax.  There are some converters which I am told will convert them.             

Answer 5

ISAL_F wrote:

(1) Is there any utility where we can input our old sql code ( SQL 2000 ) and get new sql code ( for SQL 2005).

Can you try the View Designer in SQL Server 2000? Let us know if works.

Here is my take on conversion:

Select < Column 1...........Column n >

FROM v_unit v

left join romver r1

on r1.dsn=v.dsn

left join romver r2

on r2.dsn=v.dsn

left join romver r3

on r3.dsn=v.dsn

left join romver r4

on r4.dsn=v.dsn

inner join device d

on d.dsn=v.dsn

left join otap_profile op

on op.opid=d.opid

WHERE v.cid = @intcid

and v.status < 4

and r1.module_id=0

and r2.module_id=1001

and r3.module_id=1003

and r4.module_id=2000

Let us know if passes QA.

Answer 6

Tom,

 

Is it possible for us to keep all the join condition ( Inner & Outer )  and ON conditions in the FROM Clause and

all other conditions ( Like here         AND r1.module_id = 0 

                                                    AND r2.module_id = 1001  

                                                    AND r3.module_id = 1003 and so on ). in the WHERE clause.

 

Please let me know is this possible . Appreciate your response.

 

Like this :

 

Select   < Column 1...........Column n >
        FROM    v_unit v

                LEFT OUTER JOIN romver r1 ON r1.dsn = v.dsn     
                LEFT OUTER JOIN romver r2 ON v.dsn = r2.dsn    
                LEFT OUTER JOIN romver r3 ON v.dsn = r3.dsn    
                LEFT OUTER JOIN romver r4 ON v.dsn = r4.dsn   
                INNER JOIN device d ON v.dsn = d.dsn
                LEFT OUTER JOIN otap_profile op ON d.opid = op.opid

        WHERE   v.cid = @intcid

                      AND r1.module_id = 0  

                      AND r2.module_id = 1001

                      AND  r3.module_id = 1003 

                      AND r4.module_id = 2000   
                      AND v.status < 4

Answer 7

ISAL_F wrote:

Is it possible for us to keep all the join condition ( Inner & Outer )  and ON conditions in the FROM Clause and all other conditions ( Like here         AND r1.module_id = 0                                                      AND r2.module_id = 1001                                                       AND r3.module_id = 1003 and so on ). in the WHERE clause.

Affirmative. That's how it works. Nothing is lost, nothing is added, just rephrased and rearranged.

Kalman Toth
New Book:  Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016

---

Answer 8

SQLUSA,

 

I tried your techinique of encapsulating the SELECT statements in a query ( in that too sql server 2000) and it works.

Now I will start with the actual code.

 

Thanks .

 

Answer 9

That should work. However, the code I gave is how the query parser treats your query, in the ON.  Doing it in the ON part of the join will limit the results of the join and should increase preformance.

Answer 10

Hi All,

We are migrating MS SQL 2000 server to 2005.
we are getting the ERROR "com.microsoft.sqlserver.jdbc.SQLServerException: The query uses non-ANSI outer join operators ("*=" or "=*")."

AS per the mail communication,
it is recommended that we need to re-write the query using left,right,inner joins.

can you please tell us the converter or any other tools that can used to convert from 2000 to 2005 with step by step procedure.

we are facing issues in conversion  since query size is large.

thanks in advance.

kindly help on this.

regards,
Guna

Answer 11

The likelyhood is high that the database engine can help you out.  Follow the process below , and check if works for you.

 

What we are doing is engaging the view designer, which translates automatically from old syntax to new syntax.

 

 

Code Snippet

use AdventureWorks;

-- Make the query into a view for the sake of translation

create view [dbo].[vANSITranslateQuery1]

as

select SubCategory=psc.Name, ProductName=p.Name, ListPrice, Color

from Production.Product p, Production.ProductSubcategory psc

where p.ProductSubcategoryID = psc.ProductSubcategoryID

and Color is not null

GO

 

-- Right Click on View, select Design

-- Instant translation to new syntax in bottom query window

SELECT psc.Name AS SubCategory, p.Name AS ProductName, p.ListPrice, p.Color

FROM Production.Product AS p

INNER JOIN Production.ProductSubcategory AS psc

ON p.ProductSubcategoryID = psc.ProductSubcategoryID

WHERE (p.Color IS NOT NULL)

GO

 

 

 

 

Let us know if helpful.

Thats work for me. Thanks!!

Answer 12

Hi Kalman,

In my case Microsoft SQL Server Management Studio (2012) gives me an error without translation:

"Illegal use of outer join operator."

I'm running Management Studio 2012 against SQL Server 2005 so query execution works.

Couldn't get it to convert with Management Studio 2005 either.

Alex

Answer 13

Can you post the code?

This is what I get:

SELECT * FROM Production.ProductSubcategory PSC, Production.Product P
WHERE PSC.ProductSubcategoryID *= P.ProductSubcategoryID;
/*
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '*='.
*/

SELECT * FROM Production.ProductSubcategory PSC LEFT JOIN Production.Product P
ON PSC.ProductSubcategoryID = P.ProductSubcategoryID;
-- (295 row(s) affected)

BOL: "Illegal use of outer join operator.

 SQL Server 2008 R2

There is an error in the syntax of your query. The Query Designer has attempted to locate the source of the error. Review your query syntax and correct the error before running your query."

http://msdn.microsoft.com/en-us/library/ms163409(v=sql.105).aspx

You can use the GUI Query Designer to design the JOINs:

http://social.msdn.microsoft.com/Forums/en/transactsql/thread/964e48f4-0e3c-4c77-973e-3cc10cadb4fc

---

Kalman Toth Database & OLAP Architect
Paperback / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

Answer 14

NO, the extended equality was never ANSI!  Here is how OUTER JOINs work in ANSI/ISI Standard SQL. Assume you are given:

Table1       Table2
 a   b        a   c
 ======       ======
 1   w        1   r
 2   x        2   s
 3   y        3   t
 4   z

and the outer join expression: 

 Table1 
 LEFT OUTER JOIN 
 Table2
 ON Table1.a = Table2.a      <== join condition
    AND Table2.c = 't';      <== single table condition

We call Table1 the "preserved table" and Table2 the "unpreserved table" in the query. What I am going to give you is a little different, but equivalent to the ANSI/ISO standards.

1) We build the CROSS JOIN of the two tables. Scan each row in the result set. 

2) If the predicate tests TRUE for that row, then you keep it. You also remove all rows derived from it from the CROSS JOIN

3) If the predicate tests FALSE or UNKNOWN for that row, then keep the columns from the preserved table, convert all the columns from the unpreserved table to NULLs and remove the duplicates.

So let us execute this by hand:

 Let @ = passed the first predicate
 Let * = passed the second predicate

 Table1 CROSS JOIN Table2
 a   b        a   c
 =========================
 1   w       1   r @
 1   w       2   s
 1   w       3   t *
 2   x       1   r
 2   x       2   s @
 2   x       3   t *
 3   y       1   r
 3   y       2   s
 3   y       3   t @* <== the TRUE set
 4   z       1   r
 4   z       2   s
 4   z       3   t *

 Table1 LEFT OUTER JOIN Table2
 a   b        a   c
 =========================
 3   y     3      t      <= only TRUE row
 -----------------------
 1   w     NULL   NULL   Sets of duplicates
 1   w     NULL   NULL
 1   w     NULL   NULL
 -----------------------
 2   x     NULL   NULL
 2   x     NULL   NULL
 2   x     NULL   NULL
 3   y     NULL   NULL  <== derived from the TRUE set - Remove  
 3   y     NULL   NULL
 -----------------------
 4   z     NULL   NULL
 4   z     NULL   NULL
 4   z     NULL   NULL

the final results:

 Table1 LEFT OUTER JOIN Table2
 a   b        a   c
 =========================
 1   w     NULL   NULL
 2   x     NULL   NULL
 3   y     3      t
 4   z     NULL   NULL

The basic rule is that every row in the preserved table is represented in the results in at least one result row. 

There are limitations and very serious problems with the extended equality version of an outer join used in some diseased mutant products. Consider the two Chris Date tables

 Suppliers        SupParts
 sup_nbr            sup_nbr part_nbr qty
 =========        ==============
 S1               S1   P1    100
 S2               S1   P2    250
 S3               S2   P1    100
                  S2   P2    250

and let's do an extended equality outer join like this:

 SELECT *
  FROM Supplier, SupParts
 WHERE Supplier.sup_nbr *= SupParts.sup_nbr
   AND qty < 200;

If I do the outer first, I get:

 Suppliers LOJ SupParts
 sup_nbr sup_nbr part_nbr qty
 =======================
 S1     S1   P1    100
 S1     S1   P2    250
 S2     S2   P1    100
 S2     S2   P2    250
 S3   NULL  NULL   NULL

Then I apply the (qty < 200) predicate and get

 Suppliers LOJ SupParts
 sup_nbr sup_nbr part_nbr qty
 ===================
 S1   S1   P1    100
 S2   S2   P1    100

Doing it in the opposite order

 Suppliers LOJ SupParts
 sup_nbr sup_nbr part_nbr qty
 ===================
 S1   S1   P1    100
 S2   S2   P1    100
 S3   NULL NULL  NULL

Sybase did it one way, Oracle did it the other and Centura (nee Gupta) let you pick which one -- the worst of both non-standard worlds! In ANSI/ISI Standard SQL, you have a choice and can force the order of execution. Either do the predicates after the join ...

 SELECT *
   FROM Supplier
        LEFT OUTER JOIN
        SupParts
        ON Supplier.sup_nbr = SupParts.sup_nbr
 WHERE qty < 200;

 ... or do it in the joining:

 SELECT *
  FROM Supplier
       LEFT OUTER JOIN
       SupParts
       ON Supplier.sup_nbr = SupParts.sup_nbr 
          AND qty < 200;

Another problem is that you cannot show the same table as preserved and unpreserved in the extended equality version, but it is easy in ANSI/ISI Standard SQL. For example to find the students who have taken Math 101 and might have taken Math 102:

 SELECT C1.student, C1.math, C2.math
  FROM (SELECT * FROM Courses WHERE math = 101) AS C1
       LEFT OUTER JOIN
       (SELECT * FROM Courses WHERE math = 102) AS C2
       ON C1.student = C2.student;

I would rewrite the old code from the original specs and see what else you can improve. 

---

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL