I haven’t made too many changes to how I write SQL recently, but I did adopt a new convention recently that I really like so wanted to share.
In the past, I would have written the following query like so:
select email, sum(amount) as total_revenue from users inner join charges on users.id = charges.user_id
Note that this does not prefix
amount with the table name where they came from.
Claire Caroll of dbt fame recently pinged me to suggest a change: whenever there’s a join involved, you should include the table name to make it clear where the column originated. The query above would look like this:
select users.email, sum(charges.amount) as total_revenue from users inner join charges on users.id = charges.user_id
When there’s no join involved, it’s fine to leave it out because there’s no room for confusion:
select id, name from companies
I’ve been following this convention for a few weeks and really like it because there’s zero ambiguity when seeing this around where each column originated. It’s more verbose obviously, but I think the extra clarity outweighs that downside.
I’ve updated the style guide to reflect this guide.
I’d love to hear your thoughts – do you always include the name name, only when necessary, or follow a convention like this and only include it when joins are involved?