[Digikam-devel] [Bug 96388] show number of images in the album

[chiefaua]

------- You are receiving this mail because: -------
You are the assignee for the bug, or are watching the assignee.
         
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