Differ bet WHERE clause & INNER JOIN?

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.

ddaveSo, I saw the question about Inner Join vs Where in Queries. I'm using all the "Joins" in clause where, is there problem to do it or I should be using Inner Join ?|||Linking tables should be done using a JOIN clause. Filtering on individual data elements should be done with WHERE clauses.

For simple queries, the SQL Server Optimizer will convert a statement like

select * from a, b where a.key = b.key

into

select * from a inner join b on a.key = b.key

...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.

The JOIN syntax leads to better organized and more easily readable and debuggable code, because it clearly establishes the relationships between tables.|||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.

100%|||Not so sure it will reevalutate the simple query, or not so sure it won't be able to evaluate a complex query?|||I'm not sure that it will give you 2 different plans...have you seen this?|||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.sql