Tuning query to remove joins for reporting

Question

This is the query I was sent. There is currently no data in the system as I was converting this query from teradata to SQL server.

SELECT user_id FROM user_table
LEFT OUTER JOIN
(
SELECT user_id , MIN(event_date) FROM event_table WHERE event_code='859' GROUP BY user_id
) join_859
ON user_table.user_id = join_859.user_id
LEFT OUTER JOIN
(
SELECT user_id , MIN(event_date) FROM event_table WHERE event_code='223' GROUP BY user_id
) join_223
ON user_table.user_id = join_223.user_id

Would the following query return the same results and perform better?

SELECT user_table.user_id, event_table.event_date 
FROM user_table
LEFT OUTER JOIN event_table ON user_table.user_id = event_table.user_id 
WHERE event_table.event_code IN (
    '223', 
    '859'
)

Answer 1

No it wouldn't (that is, it's not logically equivalent. Performance would probably be better though). Two reasons:

• Both of the subqueries are aggregating to get the minimum event_date.
• The second subquery (join_223) is an OUTER JOIN, not an INNER JOIN.

Incidentally, you have a repeated ON ON in the last line of the original query.

Answer 2

It seems like you only need to do this:

;WITH x(user_id, event_date) AS 
(
  SELECT user_id, MIN(event_date)
    FROM dbo.event_table
    WHERE event_code IN ('223', '859')
    GROUP BY user_id
)
SELECT user_id, event_date FROM x;

If you need other columns from the user_table, or you need to include rows from user_table even if they don't have any corresponding rows in event_table, then outer join after aggregating, but without all that repetition you currently have with derived tables:

;WITH x(user_id, event_date) AS 
(
  SELECT user_id, MIN(event_date)
    FROM dbo.event_table
    WHERE event_code IN ('223', '859')
    GROUP BY user_id
)
SELECT u.user_id, x.event_date, u.other_columns
FROM dbo.user_table AS u LEFT OUTER JOIN x
ON x.user_id = u.user_id;

This also makes it very easy to expand to more or different event_code values (assuming you want the min event date, not the min event date per event code).

Now, you mention tuning, which to many will imply that the current query is slow. This won't necessarily speed it up all that much, since the slowness is probably not altogether the fault of your particular query, but rather your indexes (or lack thereof).

If you post the full schema, including indexes, and then a few rows of sample data (all cases, including source data that has users with only 223, only 859, neither, and both), and desired results from that sample data, it will be much easier to provide precise assistance. SQLFiddle is a great place to create schema and sample data.