26: Common Table Expressions

"We want all users that were tagged in a caption or a photo before Jan 7th.""

1SELECT users.username, tags.created_at
2FROM users
3JOIN (
4    SELECT user_id, created_at FROM caption_tags
5    UNION ALL
6    SELECT user_id, created_at FROM photo_tags
7) AS tags ON tags.user_id = users.id
8WHERE tags.created_at < '2010-01-07';
copy

It might be challenging to understand what is going on. Can we rewrite this another way to make it easier to understand?

So what's a CTE?

-- this is our Common Table Expression
WITH tags AS (
    SELECT user_id, created_at FROM caption_tags
    UNION ALL
    SELECT user_id, created_at FROM photo_tags
)

SELECT users.username, tags.created_at
FROM users
JOIN tags ON tags.user_id = users.id
WHERE tags.created_at < '2010-01-07';
copy

This query now makes it easier to read.