4 ms·
Natural joins also automatically break your queries when two columns happen to share a name but not the same meaning. Step 1: use natural join. Life is great.
by cldellow 3y ago
Natural joins also automatically break your queries when two columns happen to share a name but not the same meaning.
Step 1: use natural join. Life is great.
Step 2: someone adds a `comment` field on table A. Life is great.
Step 3: someone adds a `comment` field on table B. Ruh roh.
I'll use them in short-lived personal projects, but not on something where I'm collaborating with other people on software that evolves over several years.
- jethkl 3y agoa defense against collisions like this is through CTEs that select a minimal set of columns, with column names suitably selected and standardized: CTE_A AS (SELECT ... comment as comment_a from A...), CTE_B AS (SELECT ... comment as comment_b from B...)
- Cyberdog 3y agoIsn't this a lot more work both for the user and the RDBMS than just using a relatively simple left join?
- closeparen 3y agoIt seems like the database should be able to figure this out when a foreign key constraint is explicitly declared in the DDL.
- tqi 3y agoShould, but in practice I've rarely seen fk contraints used in analytics data warehouses (mostly for etl performance reasons)