Select distinct ... inner join vs. select ... where id in (...)
- by Tonio
I'm trying to create a subset of a table (as a materialized view), defined as those records which have a matching record in another materialized view.
For example, let's say I have a Users table with user_id and name columns, and a Log table, with entry_id, user_id, activity, and timestamp columns.
First I create a materialized view of the Log table, selecting only those rows with timestamp some_date. Now I want a materliazed view of the Users referenced in my snapshot of the Log table. I can either create it as select * from Users where user_id in (select user_id from Log_mview), or I can do select distinct u.* from Users u inner join Log_mview l on u.user_id = l.user_id (need the distinct to avoid multiple hits from users with multiple log entries).
The former seems cleaner and more elegant, but takes much longer. Am I missing something? Is there a better way to do this?
