CODEWITHSUNDEEP
sqlpostgresql
user48956
user48956

Reputation: 15788

Why do parentheses work in SELECT queries but no sub queries?

Why is this a valid query

SELECT T.A FROM 
 (SELECT A, B  
  FROM  test) T ;

and this:

(SELECT DISTINCT(A,B)
      FROM  test);

but not this:

SELECT T.A FROM 
 (SELECT DISTINCT(A, B)  
  FROM  test) T ;

(specifically in postgresql, but I suspect other sql too)?

UPDATE:

Postgres fails with:

ERROR:  column t.a does not exist
LINE 1: SELECT T.A FROM

Changing the query to

SELECT T.A FROM 
 (SELECT DISTINCT A, B  
  FROM  test) T ;

succeeds, where

SELECT T.A FROM 
 (SELECT DISTINCT (A, B)  
  FROM  test) T ;

fails. Why?

Upvotes: 0

Views: 1906

Answers (2)

Hambone
Hambone

Reputation: 16377

When you do this:

SELECT T.A FROM 
 (SELECT DISTINCT(A, B)  
  FROM  test) T ;

Your (A,B) syntax is causing that to be returned as a record datatype. As such your subquery (T) sees rows of an anonymous record coming back, not individual fields A and B.

Without the parentheses, they are treated like normal fields, which appears to be what you want.

Upvotes: 4

Turbot
Turbot

Reputation: 5227

You have to assign an alias from your sub-query otherwise it couldn't identify the column name from your first seq query...

  SELECT T.A FROM 
     (SELECT DISTINCT(A, B) AS A
      FROM  test) T ;

Upvotes: -1

Related Questions