Professional Web Applications Themes

Optimizing SQL queries - Microsoft SQL / MS SQL Server

Hi, I would like to know if I should use inner joins instead of join clauses in the where section of a query - in order to have a better query (optimized). Please tell me the exact difference in joining tables : in the from section or in the where section of a Query. TIA Maria Bravo...

  1. #1

    Default Optimizing SQL queries

    Hi, I would like to know if I should use inner joins instead of join clauses
    in the where section of a query - in order to have a better query
    (optimized). Please tell me the exact difference in joining tables : in the
    from section or in the where section of a Query.

    TIA

    Maria Bravo


    Maria Guest

  2. #2

    Default Re: Optimizing SQL queries

    ANSI 92 uses INNER JOIN type syntax with the JOIN in the FROM clause, ANSI
    89 uses the join in the WHERE clause.

    I'd recommend sticking to one standard (ANSI 92), i've seen people mix both
    standards, but imho that adds complexity that doesn't need to be there.

    Filtering in the ON clause effects OUTER joins, when filtering in the ON
    clause the filter is applied before the join basically filtering first and
    then join, on the WHERE it is applied after the join.

    --
    Tony Rogerson
    SQL Server MVP
    http://www.sqlserverfaq.com?mbr=21
    (Create your own groups, Forum, FAQ's and a ton more)


    Tony Guest

  3. #3

    Default Re: Optimizing SQL queries

    For INNER JOINs it makes no difference whether the join criteria is
    specified in the FROM or the WHERE clause. The execution plan should be the
    same.

    For OUTER JOINs always use the ANSI join syntax after the FROM clause. The
    WHERE clause syntax for outer joins is deprecated and can produce
    unpredictable results.

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --


    David Guest

Similar Threads

  1. Optimizing
    By Campbell in forum Coldfusion - Advanced Techniques
    Replies: 3
    Last Post: October 5th, 01:51 AM
  2. Queries Of Queries Single Quote Problem
    By TimH2O in forum Macromedia ColdFusion
    Replies: 0
    Last Post: April 1st, 07:46 PM
  3. Optimizing PDF
    By Adrian Pinderhughes in forum Adobe Acrobat Windows
    Replies: 4
    Last Post: May 19th, 03:36 PM
  4. optimizing images when over each other?
    By lopz webforumsuser@macromedia.com in forum Macromedia Fireworks
    Replies: 2
    Last Post: August 19th, 11:26 AM
  5. Optimizing Query
    By Erwin in forum IBM DB2
    Replies: 2
    Last Post: August 14th, 03:07 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139