[prev in list] [next in list] [prev in thread] [next in thread] 

List:       kde-bugs-dist
Subject:    [Bug 96388] show number of images in the album
From:       chiefaua <tom_t () gmx ! at>
Date:       2007-12-12 10:45:02
Message-ID: 20071212104502.3582.qmail () ktown ! kde ! org
[Download RAW message or body]

------- You are receiving this mail because: -------
You are on the CC list for the bug, or are watching someone who is.
         
http://bugs.kde.org/show_bug.cgi?id=96388         




------- Additional Comments From tom_t gmx at  2007-12-12 11:44 -------
Hello

This query:

SELECT ImageTags.tagid, Images.name FROM ImageTags, Images WHERE ImageTags.imageid = \
Images.id;

is the implicit way of writing

SELECT ImageTags.tagid, Images.name FROM ImageTags INNER JOIN Images ON \
ImageTags.imageid=Images.id;

The database should treat both queries in exactly the same way (and optimise both \
queries to the same disk/database operations). The second way of writing it is more \
explicit, and stresses the fact that there is a join involved. But the database \
(should) work out that the WHERE clause in the first query is equivalent. MySQL, in \
fact, replaces the JOIN clause internally by WHERE clauses.


In the second query, we used an INNER JOIN. This join is different from the LEFT \
JOIN, but due to the nature of the table ImageTags they produce the same result in \
this case. The LEFT JOIN ensures that all rows from the "left" table (in our case \
ImageTags) are included in the resulting set. If there were no corresponding row in \
the Images table, it would fill in null values. But as there are only valid imageid's \
in ImageTags, the database finds for each row in ImageTags an Image, and the result \
is the same as for the INNER JOIN (which would never add null-values).

So, this query is (in this case) also equivalent:

SELECT ImageTags.tagid, Images.name FROM ImageTags LEFT JOIN Images ON \
ImageTags.imageid=Images.id;

There exists also a RIGHT JOIN and a FULL OUTER JOIN. SQLite 3 does not support them, \
and MySQL does not support the FULL OUTER JOIN. Also, there are some other variants \
of joins, like NATURAL joins, but I'm not sure which databases support which joins, \
and where are the exact differences.


So, to conclude, all three queries return the same result, and a database should \
optimize all three of them to the same low level operations (note that the LEFT JOIN \
could possibly constrain the database's optimizations, but in this case there is no \
difference, as there are no further WHERE clauses).

So, I think it comes down to personal taste which of the three queries you use.

Hopefully I could clarify a bit,

Thomas


[prev in list] [next in list] [prev in thread] [next in thread]