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]
