Professional Web Applications Themes

JOIN vs no JOIN - MySQL

Is there any benefit in doing: SELECT a.f1, a.f2, b.f3 FROM t1 a INNER JOIN t2 b ON a.f1=b.id WHERE a.f1> 0 rather than SELECT a.f1, a.f2, b.f3 FROM t1 a , t2 b WHERE a.f1> 0 and a.f1 = b.id TIA Paul...

  1. #1

    Default JOIN vs no JOIN

    Is there any benefit in doing:

    SELECT a.f1, a.f2, b.f3 FROM t1 a INNER JOIN t2 b ON a.f1=b.id WHERE a.f1> 0

    rather than

    SELECT a.f1, a.f2, b.f3 FROM t1 a , t2 b WHERE a.f1> 0 and a.f1 = b.id

    TIA
    Paul


    Paul Lautman Guest

  2. #2

    Default Re: JOIN vs no JOIN

    Paul,
    > Is there any benefit in doing:
    >
    > SELECT a.f1, a.f2, b.f3 FROM t1 a INNER JOIN t2 b ON a.f1=b.id WHERE a.f1>
    0
    >
    > rather than
    >
    > SELECT a.f1, a.f2, b.f3 FROM t1 a , t2 b WHERE a.f1> 0 and a.f1 = b.id
    IMO, the "join" syntax is the preffered syntax.

    1 - it's "newer" (SQL92 as opposed to SQL89)
    2 - it's easier to read (especially when joining multiple tables)
    3 - it's more clear (you're "joining" to show that you're getting data from
    multiple tables in the FROM clause of your statement, makes perfect sense)
    4 - you can easily modify the join options (eg: LEFT JOIN) without having
    to re-write the query
    5 - think of "where" as restrictions on the output rather then a place of
    joining tables



    --
    Martijn Tonies
    Database Workbench - development tool for MySQL, and more!
    Upscene Productions
    [url]http://www.upscene.com[/url]
    My thoughts:
    [url]http://blog.upscene.com/martijn/[/url]
    Database development questions? Check the forum!
    [url]http://www.databasedevelopmentforum.com[/url]


    Martijn Tonies Guest

  3. #3

    Default Re: JOIN vs no JOIN

    Martijn Tonies wrote:
    > Paul,
    >
    >> Is there any benefit in doing:
    >>
    >> SELECT a.f1, a.f2, b.f3 FROM t1 a INNER JOIN t2 b ON a.f1=b.id WHERE a.f1>
    > 0
    >> rather than
    >>
    >> SELECT a.f1, a.f2, b.f3 FROM t1 a , t2 b WHERE a.f1> 0 and a.f1 = b.id
    >
    > IMO, the "join" syntax is the preffered syntax.
    >
    > 1 - it's "newer" (SQL92 as opposed to SQL89)
    > 2 - it's easier to read (especially when joining multiple tables)
    > 3 - it's more clear (you're "joining" to show that you're getting data from
    > multiple tables in the FROM clause of your statement, makes perfect sense)
    > 4 - you can easily modify the join options (eg: LEFT JOIN) without having
    > to re-write the query
    > 5 - think of "where" as restrictions on the output rather then a place of
    > joining tables
    6 - You do not suffer from any precedence changes between JOIN and ','

    Greetings
    Kai

    --
    This signature is left as an exercise for the reader.
    Kai Ruhnau Guest

Similar Threads

  1. Is left-join faster then inner join?
    By howachen@gmail.com in forum MySQL
    Replies: 5
    Last Post: March 5th, 07:11 AM
  2. FMS Join two flv...
    By Luiz Alberto in forum Macromedia Flash Flashcom
    Replies: 0
    Last Post: November 23rd, 02:51 PM
  3. Is self-join appropriate?
    By Michal Stankoviansky in forum MySQL
    Replies: 6
    Last Post: January 3rd, 08:42 PM
  4. DB2 join
    By Prakash in forum IBM DB2
    Replies: 1
    Last Post: September 10th, 05:24 PM
  5. How to Join...
    By CD in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 10th, 06:26 PM

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