Difference between using aliases in outer query of subquery or not using them

user_16440433 0
Viewed 24

I have code with or without using aliases in outer query. Both work well and provide similar result.

Not using aliases in outer query:

SELECT DISTINCT ON (msg_id, msg_checksum) *
FROM 

(
SELECT id, jsonb_var,  
       jsonb_var ->> 'id' as msg_id,
       jsonb_var ->> 'checksum' as msg_checksum
FROM testing_json_as_table
) AS subquery_table_with_msg_id_and_msg_checksum

ORDER BY msg_id, msg_checksum;

Using aliases in outer query:

SELECT DISTINCT ON (subquery_table_with_msg_id_and_msg_checksum.msg_id, subquery_table_with_msg_id_and_msg_checksum.msg_checksum) *
FROM 

(
SELECT id, jsonb_var,  
       jsonb_var ->> 'id' as msg_id,
       jsonb_var ->> 'checksum' as msg_checksum
FROM testing_json_as_table
) AS subquery_table_with_msg_id_and_msg_checksum

ORDER BY subquery_table_with_msg_id_and_msg_checksum.msg_id, subquery_table_with_msg_id_and_msg_checksum.msg_checksum;

My question is:

  1. Why both code give similar result?
  2. When I should use aliases in outer query and when not?
postgresql sql
1 Answers

In general, I recommend always qualifying column names (i.e. using the aliases). Parallel with that, I recommend simple aliases, abbreviations. So:

SELECT DISTINCT ON (tjt.msg_id, tjt.msg_checksum) *
FROM (SELECT id, jsonb_var,  
             jsonb_var ->> 'id' as msg_id,
             jsonb_var ->> 'checksum' as msg_checksum
      FROM testing_json_as_table
     ) tjt
ORDER BY tjt.msg_id, tjt.msg_checksum;

Here tjt abbreviates testing_json_as_table.

That said, if you are only referencing one table one time in your query (as in this example), then you can relax that "always qualify all columns" rule.

user_11440350
Related