Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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'
)
share|improve this question
    
LEFT INNER JOIN -- errr... –  Jon Seigel Jan 8 '13 at 21:14
    
haha sorry, jon, I was typing it while looking at a different inner join on another screen. –  grid Jan 8 '13 at 21:35
    
If the only output is the user_id column, what is the purpose of the aggregate? And why bother including user_table in the query, even if the output should be user_i9d, event_date_1, event_date_2? Finally, don't use aliases that include keywords, e.g. join_859 initially looked to me like a join. –  Aaron Bertrand Jan 8 '13 at 21:41
    
Because the original query was very poorly formatted, and I simplified the query to what was really needed. –  grid Jan 8 '13 at 21:51
1  
But do you see how transcription led to many more errors than would have resulted from copying and pasting and then editing here? –  Aaron Bertrand Jan 8 '13 at 21:58

2 Answers 2

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.

share|improve this answer
    
They are both left outer joins, that was my bad. –  grid Jan 8 '13 at 21:36
2  
Looking at the query in the question, it could simplify to SELECT user_id FROM user_table. The LEFT OUTER JOINs will include all rows, and you're not actually doing anything with the min_date you calculate. Are you sure you've translated this correctly from your Teradata query? –  Simon Righarts Jan 8 '13 at 21:55

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.