Michael,
Thanks so much for the query. As I am surmising from your email, the
LEFT JOIN is the better way to go for performance. If you have any
reason to think I should go with the subquery, let me know!
Thanks again,
John
On Dec 12, 2005, at 9:57 AM, Michael Stassen wrote:
Rhino wrote:
----- Original Message ----- From: "John Mistler"
<[EMAIL PROTECTED]>
To: <mysql@lists.mysql.com>
Sent: Monday, December 12, 2005 12:34 AM
Subject: Select Unique?
I have two tables 'table1', 'table2' with a matching column
'column1'. How can I return all rows from table2 where the entry
for table2.column1 does not match any entries in table1.column1?
SELECT * FROM table2 WHERE table2.column1 <> table1.column1
returns all the rows, rather than the unique rows in table2 ... Any
ideas?
SELECT *
FROM table2
where table2.column1 not in
(select distinct column1
from table1
where column1 not null)
That should be "where column1 IS NOT NULL)".
The 'distinct' in the subquery is not strictly necessary but should
help performance. The WHERE clause in the subquery is often omitted
but really shouldn't be.
Rhino
If you're interested in performance, you probably shouldn't use a
subquery. If you put EXPLAIN in front, you'll see that mysql labels
this a "DEPENDENT SUBQUERY", meaning it will rerun the subquery for
each row in the outer query. The optimizer *should* be smart enough to
run the inner query once, then compare rows to that list using the
index, but it isn't. As a test, I made a 25 row table and a copy
missing 3 of those rows. The subquery version took twice as long to
execute (.12 sec) as the left join version (.06 sec). The larger the
tables involved, the larger the difference is likely to be.
Michael
(Test run on my iBook G4, OS X 10.3.9, mysql 4.1.15.)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
