Let me try to clarify what I was trying to say.
It was my contention that the SQL queries (1), (2), and (3) below produce identical results.
If that's not true, I'd glady stand corrected.
Queries (1) and (2) are based on Mike Yu's original two queries. Neither of Mike Yu's original versions had FROM clauses and therefore weren't valid SQL. It was my assumption -- and I guess I should have stated this in my first post -- that the FROM clause in the first query would be
from test1 , test2 and the FROM clause in the second query would be
from test1.
(1)
(2)
(3)
Paul Misoni responds with two queries, the first of which is identical to (1) above, and here's the second (which I'll call (4)). Paul's queries are also based on Mike Yu's, but I believe Paul and I have made different assumptions about the FROM clauses.
(4)
Paul provides sample data and sample results for his two queries. I have no issues with Paul's results given his queries.
Paul points out that queries (1) and (4) are not the same. I completely agree. They're not the same. Whoever said they are?
The difference between query (4) and query (2) is the FROM clause. (2)'s from clause only has one table -- test1. But (4)'s from clause has two tables -- test1 and test2 -- and there's no join condition on these two tables in the where clause, so the query produces the Cartesian product of the two tables (then limits the results based on the where clause).
(If you don't believe me that a Cartesian product is being generated, add columns from test2 to the select list of (4).)
As far as I can tell, SQL-92 vs. non-SQL-92 has nothing to do with any of this. (But would gladly be corrected if I'm mistaken.) The difference is in what tables we all envision in the FROM clause. Based on Michael Zalewski's post:
code:
--------------------------------------------------------------------------------
SELECT Test1.col1, Test1.col2 FROM Test1 WHERE Test1.col1 IN ( SELECT Test2.cola FROM Test2 )
--------------------------------------------------------------------------------
Which is what I believe Mike Yu meant when he said
"they return the same results".
I think all three Michaels taking part in this
thread were thinking of the same query and results.
By the way, Paul, if you're going to bring up SQL-92, shouldn't you have used INNER JOIN and CROSS JOIN syntax in the where clause in your examples?