Saturday, 31 August 2013

MySQL use of LEFT JOIN vs WHERE col1=col2

MySQL use of LEFT JOIN vs WHERE col1=col2

I'm curious about the difference between using joins (the different types
of 'join') and using WHERE table1.col1 = table2.col2.
Here is my specific example I did to "test" my knowledge:
$cxn = mysqli_connect($host,$user,$password,$dbname);
if (mysqli_connect_errno()) {echo "No connection" . mysqli_connect_error();}
$query = " SELECT icd9.icd9codenodot,
icd9.icd9code,
icd9.icd9short,
icd10.icd10code,
icd10.icd10short ".
" FROM icd9, icd9icd10gem, icd10 ".
" WHERE (icd9.icd9long LIKE '%$firstword%') AND
(icd9.icd9codenodot = icd9icd10gem.icd9) AND
(icd10.icd10code = icd9icd10gem.icd10)";
$query = " SELECT icd9.icd9codenodot,
icd9.icd9code,
icd9.icd9short,
icd10.icd10code,
icd10.icd10short ".
" FROM icd9 LEFT JOIN (icd9icd10gem, icd10)
ON (icd9icd10gem.icd9 = icd9.icd9codenodot AND
icd9icd10gem.icd10 = icd10.icd10code)".
" WHERE (icd9.icd9long LIKE '%$firstword%')";
$result = mysqli_query($cxn, $query) or die ("could not query database 1");
while ($row = mysqli_fetch_array($result))
{
echo stuff
}
Fluency in any "language" requires the ability to express a single concept
in a variety of ways, and since I'm doing pretty good at the WHERE
table1.var1 = table2.var2 type of "join" I thought I'd try to use the
formal "join".
To use the script I comment out one of the queries, run it, then comment
out the other query and run it. (the relevant lines have the * in front of
them)
Both of them produce almost exactly the same result (one orders the rows a
little differently, but the overall result is the same).
On this forum, I've found a comment from one programmer to that effect
that (s)he doesn't like joins and only does the WHERE table1.var1 =
table2.var2.
My question: 1. Is there a reason why I/a person should use one technique
(join) over the other (WHERE table1.var1 = table2.var2 - which in reality
is a "type" of join)?
Thanks very much for any comments.

No comments:

Post a Comment