In simple terms, if possible, what is the difference between using the WHERE clause in a SELECT statement vs an INNER JOIN? According to Rob Viera's book the WHERE is "inclusive" in nature, meaning that all records that meet the WHERE condition are included in the result set. The text further stated that an INNER JOIN is "exclusive" in nature meaning that data not meeting the JOIN condition is excluded from the result set.
In layman's terms, what is the difference? Any examples? Thanks in advance.
Originally posted by blindman
...and will make optimium use of the indexes on the two tables to create an efficient execution plan. For more complicated queries the optimizer may not be able to make this conversion.
Ya think? Not so sure...but youcan test it and check out the plan the optimizer chooses by looking at the query plan
The JOIN syntax leads to better organized and more easily readable and debuggable code, because it clearly establishes the relationships between tables.
It won't for simple queries. I've heard that it might not be able to for complex queries. At some point of complexity there must be a statement that it is beyond its capacity to reduce to simple join clauses. I mean, complexity is theoretically infinite, right? I suspect that it may have difficulty reevaluating WHERE clauses containing conditional syntax, but I haven't formally tested it.
If it's not practically useful, then it's practically useless.