Types and Typos in SQL [1]

Almost anyone who has spent time working with SQL made — or had to fix — this kind of bug:

-- two tables (sketch)
users {id, user_name}

trans {id, amt, cur, tx_time, user_id}
-- query with a typo
SELECT user_name
     , tx_time
     , amt
     , cur
 FROM users as u
 JOIN trans as t ON u.id = t.id -- < - typo !!
WHERE user_name = 'jack' ;

Try the query, here is the DDL with some data; note the wrong result. Continue reading "Types and Typos in SQL [1]"