Professional Web Applications Themes

JOINs instead of AND - MySQL

Where do I go to learn how to do JOINs instead of using multiple ANDs in a WHERE clause? I hear a JOIN is faster, and I am all for speed in a query....

  1. #1

    Default JOINs instead of AND

    Where do I go to learn how to do JOINs instead of using multiple ANDs in a
    WHERE clause? I hear a JOIN is faster, and I am all for speed in a query.


    Jim Michaels Guest

  2. #2

    Default Re: JOINs instead of AND

    "Jim Michaels" <NOSPAMFORjmichae3> wrote in message
    news:GZKdne4umIZaj4_ZRVn-gAcomcast.com...
    > Where do I go to learn how to do JOINs instead of using multiple ANDs in a
    > WHERE clause? I hear a JOIN is faster, and I am all for speed in a query.
    Well, it's actually not true that the JOIN syntax is faster, according to
    Chapter 5 of "SQL Performance Tuning" by Peter Gulutzan and Trudy Pelzer.
    The "old style" of putting join conditions in the WHERE clause (i.e. SQL-89
    joins) was never slower in Gulutzan & Pelzer's tests of eight different
    RDBMS brands. In the case of IBM DB2 the old style was sometimes actually
    faster in their tests.

    However, in my opinion their test cases were pretty simple; it's possible
    that using more exotic joins there might be a different result. The best
    advice is for you to try both styles of query against some real live data in
    a given project, and apply some timing measurements to see if there's any
    difference in the queries that matter in your project. I expect any
    differences will be insignificant, especially compared to other performance
    improvement techniques you might use, like table maintenance, increasing
    index key buffers, etc. Those factors should affect both types of join
    syntax identically.

    So performance isn't why you'd use the JOIN syntax. The reason to use JOIN
    is that you _can't_ achieve OUTER JOINs with the old-style syntax. Since
    it's good form to use one syntax consistently in a given project, it's
    preferred to use the SQL-92 JOIN syntax for inner joins too.

    As for how: any book or tutorial on SQL written since 2001 should cover the
    JOIN syntax.
    But briefly, here are a couple of examples that are equivalent queries:

    SELECT *
    FROM a, b, c
    WHERE a.field1 = b.field1 AND a.field2 = b.field2
    AND b.field2 = c.field2;

    SELECT *
    FROM a INNER JOIN b ON a.field1 = b.field1 AND a.field2 = b.field2
    INNER JOIN c ON b.field2 = c.field2;

    So when you see conditions that relate a to b, put those conditions in the
    ON clause following the join of a with b. When you see conditions that
    relate b to c, put those conditions in the ON clause following the join of
    the first part with c.

    Some people like to use lots of parentheses to show the order of evaluation
    of the joins, but most often it's not necessary; the default order of
    evaluation of JOINs is just fine.

    Regards,
    Bill K.


    Bill Karwin Guest

Similar Threads

  1. Alternative joins
    By Murdoc in forum MySQL
    Replies: 0
    Last Post: August 18th, 10:31 AM
  2. Joins or subqueries
    By alexford in forum Coldfusion Database Access
    Replies: 1
    Last Post: April 26th, 02:31 AM
  3. SQL help on joins
    By zCrow in forum Dreamweaver AppDev
    Replies: 3
    Last Post: May 10th, 09:17 PM
  4. JOINs in Views
    By Martin Lingl in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: July 9th, 11:36 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