Monday at 10:25am, Wade Preston Shearer said: > There's no difference (performance and results) between the following two > queries, right? (I get the same results.) > > SELECT > FROM a > INNER JOIN b > ON c > AND d > WHERE f > > > SELECT > FROM a > INNER JOIN b > ON c > WHERE f AND d
Belated reply, sorry... The short answer is that the first one makes 'd' a condition for the join, i.e. deciding whether or not the two rows from the two tables can be matched. The latter uses d as a where condition, which filters which rows that were joined are returned. Barring any optimization, the where conditions are applied after all the joined rows are generated, making them generally less efficient than doing a condition in the ON clause. However, most databases would likely do optimization on it. I'm trying to think of a good example of when they would return different results, but I'm having a hard time thinking of one. But I do think in theory it is possible to have them be different. I think there are examples involving 3 or more joins that come out differently, because it changes the number of rows that result from the first join to be used in the second join. You could try running BENCHMARK() on it to see what differences you get, and EXPLAIN might show how it optimized each of them. Mac -- Mac Newbold Code Greene, LLC CTO/Chief Technical Officer 44 Exchange Place Office: 801-582-0148 x102 Salt Lake City, UT 84111 Cell: 801-694-6334 www.codegreene.com _______________________________________________ UPHPU mailing list [email protected] http://uphpu.org/mailman/listinfo/uphpu IRC: #uphpu on irc.freenode.net
