Marvin K. Bellamy ha scritto:
I'm still a noob to SQL and I *think* I just found my first real-world
need to perform a self join. Given the table:
person (user_id integer primary key, name text)
I want to find all the entries with identical names (before adding a
unique index). I executed this query which took an extremely long
time (over 30 seconds) to complete on a table with 8,000+ rows.
select user_id from person p1, person p2 where p1.user_id<>p2.user_id
and p1.name = p2.name
This is more an administrative task, so performance isn't a concern.
But, I was curious about a more efficient option for this query.
You can try this.
SELECT user_id FROM person GROUP BY name HAVING count(*) > 1
This will return 1 id for any group; to get a resultset similar to the
one you are receiving now you have to do:
SELECT user_id FROM person WHERE name IN (SELECT name FROM person GROUP
BY name HAVING count(*) > 1)
Paolo
