Post reply

T SQL joins to ansi sql joins conversion

  • March 14, 2012 at 9:14 am

    #253046

    Is there an easy way to convert T-SQL (*=) joins into ANSI ( LEFT / RIGHT) joins, rather than going through each and every query and logically converting the same ?

    The problem here is we have more than 500 of such queries to be converted to ANSI joins.

  • March 14, 2012 at 9:20 am

    #1459281

    I think you would probably spend as much, if not more, time writing a solution to automate this than it would take to just do it manually. The upside of manually updating your queries is it gives you a perfect opportunity to evaluate them and make some performance improvements.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • March 14, 2012 at 9:25 am

    #1459286

    Rewriting by hand is also probably going to be better any way. You will need to compare the outputs from both queries to ensure that they return the same result sets. When moving from the ANSI-89 style joins to the ANSI-92 style joins you may find that the results are different. This is because one or more criteria in the where clause may also need to be part of the outer join criteria.

    Automating something like this probably isn't going to be worth the effort.

  • March 15, 2012 at 4:34 am

    #1459719

    Sean Lange (3/14/2012)

    ...The upside of manually updating your queries is it gives you a perfect opportunity to evaluate them and make some performance improvements.

    And also securing your job for few more weeks 😀

    Actually, you will need to do it manually, as "*=" and "=*" are not exactly the same as LEFT and RIGHT JOINs in all cases...

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • March 15, 2012 at 6:05 am

    #1459758

    The way to do these conversions is to follow the WHERE clause and remember that the *, in the *=/=*, is against the table where all the rows are to be retained. Once you get the used to it, the conversions can be done quite quickly. You should still check that the original query and the final query produce the same results.

  • March 15, 2012 at 1:13 pm

    #1460110

    Here's a little cheat for you using the Query Designer in SSMS 2008R2.

    1. Restore a copy of your database into a DEV environment.

    2. Change the database to 80 compatibility mode.

    3. Right click any table in "Object Explorer" and choose "Edit Top ### Rows".

    4. Press Ctrl+3 to open the "SQL Pane" (or use the menu item "Query Designer" > Pane > "SQL Pane").

    5. Paste the query you need to convert into the "SQL Pane":

    -- Universal example to demo technique

    SELECT t.name

    FROM sys.tables t,

    sys.columns c

    WHERE t.object_id *= c.object_id

    6. Now choose "Verify SQL Syntax" from the "Query Designer" menu to see the magic.

    SELECT t.name

    FROM sys.tables AS t RIGHT OUTER JOIN

    sys.columns AS c ON t.object_id = c.object_id

    Notice the query was rewritten to use the desired JOIN syntax.

    Best I can tell this functionality leverages a combination of client-side code and server-side code. Results may seem awkward for some queries. The CROSS JOIN is a common result in the rewritten query when the WHERE-clause is complicated and some predicates are unintentionally left out, a common problem with writing and maintaining the old-style syntax.

    I personally used this in converting hundreds of queries from a version of Sybase that only supported the *=/=* syntax for outer joins over to SQL 2005. It significantly reduced conversion times. The results of the rewritten query are meant to be logically equivalent, however this technique does not obviate the need to test every query to ensure the results are as expected.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • March 15, 2012 at 1:21 pm

    #1460116

    opc.three (3/15/2012)

    Here's a little cheat for you using the Query Designer in SSMS 2008R2.

    1. Restore a copy of your database into a DEV environment.

    2. Change the database to 80 compatibility mode.

    3. Right click any table in "Object Explorer" and choose "Edit Top ### Rows".

    4. Press Ctrl+3 to open the "SQL Pane" (or use the menu item "Query Designer" > Pane > "SQL Pane").

    5. Paste the query you need to convert into the "SQL Pane":

    -- Universal example to demo technique

    SELECT t.name

    FROM sys.tables t,

    sys.columns c

    WHERE t.object_id *= c.object_id

    6. Now choose "Verify SQL Syntax" from the "Query Designer" menu to see the magic.

    SELECT t.name

    FROM sys.tables AS t RIGHT OUTER JOIN

    sys.columns AS c ON t.object_id = c.object_id

    Notice the query was rewritten to use the desired JOIN syntax.

    Best I can tell this functionality leverages a combination of client-side code and server-side code. Results may seem awkward for some queries. The CROSS JOIN is a common result in the rewritten query when the WHERE-clause is complicated and some predicates are unintentionally left out, a common problem with writing and maintaining the old-style syntax.

    I personally used this in converting hundreds of queries from a version of Sybase that only supported the *=/=* syntax for outer joins over to SQL 2005. It significantly reduced conversion times. The results of the rewritten query are meant to be logically equivalent, however this technique does not obviate the need to test every query to ensure the results are as expected.

    Have you tried this with code where additional equality comparisons were actually part of the LEFT (or RIGHT) OUTER JOIN and not filter criteria?

    This is why testing of the rewritten code is imperative, as I am sure you would agree.

  • March 15, 2012 at 1:40 pm

    #1460142

    Lynn Pettis (3/15/2012)

    Have you tried this with code where additional equality comparisons were actually part of the LEFT (or RIGHT) OUTER JOIN and not filter criteria?

    Some of the queries I converted had dozens of WHERE-clause predicates, a mix of JOIN and filter criteria of course. That said, I am not sure I completely get the root of what you're asking. Could you provide a code example?

    This is why testing of the rewritten code is imperative, as I am sure you would agree.

    I agree 100%. It's hard to get that point across in plain-text. I mentioned it, but it is important to drive the point home. You mentioned it again, and I'll mention it one more time for good measure. Using this technique gave me a starting point that was much further along than starting all query conversion by hand, but the results of this technique are no substitute for parallel testing.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • March 15, 2012 at 1:46 pm

    #1460150

    opc.three (3/15/2012)

    Lynn Pettis (3/15/2012)

    Have you tried this with code where additional equality comparisons were actually part of the LEFT (or RIGHT) OUTER JOIN and not filter criteria?

    Some of the queries I converted had dozens of WHERE-clause predicates, a mix of JOIN and filter criteria of course. That said, I am not sure I completely get the root of what you're asking. Could you provide a code example?

    This is why testing of the rewritten code is imperative, as I am sure you would agree.

    I agree 100%. It's hard to get that point across in plain-text. I mentioned it, but it is important to drive the point home. You mentioned it again, and I'll mention it one more time for good measure. Using this technique gave me a starting point that was much further along than starting all query conversion by hand, but the results of this technique are no substitute for parallel testing.

    Not off the top of my head. I know at a previous employer when we were upgrading to SQL Server 2005 from SQL Server 2000 the PeopleSoft developers had to rewrite quite of few SQL queries and one of them the entire where clause became the ON clause for a LEFT OUTER JOIN between two tables. If the equality comparision was left in the WHERE clause the query did not return the same result set.

    So, I will mention it again as well, be sure to test the queries to ensure the rewrite returns the same result set as the original query for the same input.

  • March 15, 2012 at 1:48 pm

    #1460151

    Thinking about it a bit more, the equality condition may have turned the LEFT OUTER JOIN into an INNER JOIN when done in the WHERE clause.

  • March 15, 2012 at 1:58 pm

    #1460159

    That makes perfect sense, and was a weakness of WHERE-clause JOINS due to the ambiguity of such things.

    This is an example to run in your 80 compat mode DB to illustrate:

    -- query to convert

    SELECT o.name,

    t.name

    FROM master.sys.objects AS o,

    master.sys.tables t

    WHERE o.name *= t.NAME

    AND t.is_ms_shipped = 1

    -- option A

    SELECT o.name,

    t.name

    FROM master.sys.objects AS o

    LEFT OUTER JOIN master.sys.tables AS t ON o.name = t.name

    AND t.is_ms_shipped = 1

    -- option B (correct)

    SELECT o.name,

    t.name

    FROM master.sys.objects AS o

    LEFT OUTER JOIN master.sys.tables AS t ON o.name = t.name

    WHERE t.is_ms_shipped = 1

    -- what the Query Designer does

    SELECT o.name, t.name AS Expr1

    FROM master.sys.objects AS o LEFT OUTER JOIN

    master.sys.tables AS t ON o.name = t.name

    WHERE (t.is_ms_shipped = 1)

    The Query Designer happened to get it right in this case, but you never know, which is why there is no substitute for parallel testing.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • March 22, 2012 at 12:03 pm

    #1462936

    opc.three (3/15/2012)

    Here's a little cheat for you using the Query Designer in SSMS 2008R2.

    1. Restore a copy of your database into a DEV environment.

    2. Change the database to 80 compatibility mode.

    3. Right click any table in "Object Explorer" and choose "Edit Top ### Rows".

    4. Press Ctrl+3 to open the "SQL Pane" (or use the menu item "Query Designer" > Pane > "SQL Pane").

    5. Paste the query you need to convert into the "SQL Pane":

    -- Universal example to demo technique

    SELECT t.name

    FROM sys.tables t,

    sys.columns c

    WHERE t.object_id *= c.object_id

    6. Now choose "Verify SQL Syntax" from the "Query Designer" menu to see the magic.

    SELECT t.name

    FROM sys.tables AS t RIGHT OUTER JOIN

    sys.columns AS c ON t.object_id = c.object_id

    My first post here and I'd like to say thanks for bringing this feature to my attention, opc.three.

    But the example result contains an error:

    "RIGHT OUTER JOIN" should be "LEFT OUTER JOIN" because sys.tables is the preserved table in the original query.

    I tried this example in SSMS 2008R2 SP1 (server and client) and it produced "LEFT OUTER JOIN":

    SELECT t.name

    FROM sys.tables AS t LEFT OUTER JOIN

    sys.columns AS c ON t.object_id = c.object_id

  • March 22, 2012 at 12:53 pm

    #1462971

    FairlySimple (3/22/2012)

    opc.three (3/15/2012)

    Here's a little cheat for you using the Query Designer in SSMS 2008R2.

    1. Restore a copy of your database into a DEV environment.

    2. Change the database to 80 compatibility mode.

    3. Right click any table in "Object Explorer" and choose "Edit Top ### Rows".

    4. Press Ctrl+3 to open the "SQL Pane" (or use the menu item "Query Designer" > Pane > "SQL Pane").

    5. Paste the query you need to convert into the "SQL Pane":

    -- Universal example to demo technique

    SELECT t.name

    FROM sys.tables t,

    sys.columns c

    WHERE t.object_id *= c.object_id

    6. Now choose "Verify SQL Syntax" from the "Query Designer" menu to see the magic.

    SELECT t.name

    FROM sys.tables AS t RIGHT OUTER JOIN

    sys.columns AS c ON t.object_id = c.object_id

    My first post here and I'd like to say thanks for bringing this feature to my attention, opc.three.

    But the example result contains an error:

    "RIGHT OUTER JOIN" should be "LEFT OUTER JOIN" because sys.tables is the preserved table in the original query.

    I tried this example in SSMS 2008R2 SP1 (server and client) and it produced "LEFT OUTER JOIN":

    SELECT t.name

    FROM sys.tables AS t LEFT OUTER JOIN

    sys.columns AS c ON t.object_id = c.object_id

    The Query Designer correctly produces a LEFT JOIN from the code in my Step 5, I just copied and pasted incorrectly into the Step 6 result. Good to see you're trying it out and paying attention 😉

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • March 22, 2012 at 1:26 pm

    #1462983

    opc.three (3/15/2012)

    That makes perfect sense, and was a weakness of WHERE-clause JOINS due to the ambiguity of such things.

    This is an example to run in your 80 compat mode DB to illustrate:

    -- query to convert

    SELECT o.name,

    t.name

    FROM master.sys.objects AS o,

    master.sys.tables t

    WHERE o.name *= t.NAME

    AND t.is_ms_shipped = 1

    -- option A

    SELECT o.name,

    t.name

    FROM master.sys.objects AS o

    LEFT OUTER JOIN master.sys.tables AS t ON o.name = t.name

    AND t.is_ms_shipped = 1

    -- option B (correct)

    SELECT o.name,

    t.name

    FROM master.sys.objects AS o

    LEFT OUTER JOIN master.sys.tables AS t ON o.name = t.name

    WHERE t.is_ms_shipped = 1

    -- what the Query Designer does

    SELECT o.name, t.name AS Expr1

    FROM master.sys.objects AS o LEFT OUTER JOIN

    master.sys.tables AS t ON o.name = t.name

    WHERE (t.is_ms_shipped = 1)

    The Query Designer happened to get it right in this case, but you never know, which is why there is no substitute for parallel testing.

    On my server "query to convert" returns 81 rows and so does option A.

    Option B and the Query Designer both return 6 rows.

    So the Query Designer isn't getting it "right" if "right" means the same behaviour as the "query to convert".

    The "query to convert" and option A are applying "t.is_ms_shipped = 1" as an outer join condition so all 81 rows in sys.objects are preserved in the result but only 6 fulfil the join condition so have non-null values for sys.tables.name.

    In option B and the Query Designer the "t.is_ms_shipped = 1" condition is applied to the result of the outer join, in which 75 rows have no match in sys.tables so are excluded.

  • March 22, 2012 at 1:30 pm

    #1462985

    A similar experience is described here:

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy