Ambiguous column reference with AS alias

Question

I'm not sure how to resolve an ambiguous column reference when using an alias.

Imagine two tables a and b that have a column name . If I join these two tables and get the result, I don’t know how to refer to the name column for both tables. I tried several options, but none of them work:

Attempt 1

 SELECT a.name, b.name FROM (a INNER JOIN b ON a.id = b.id) AS x 

This does not work, since a and b are out of scope.

Attempt 2

 SELECT xaname, xbname FROM (a INNER JOIN b ON a.id = b.id) AS x 

SQL syntax does not work.

Attempt 3

 SELECT x.name, x.name FROM (a INNER JOIN b ON a.id = b.id) AS x 

This is simply ambiguous!

I have all of the ideas - any help would be greatly appreciated.

Answer 1

do not enclose it in parentheses since (a INNER JOIN b ON a.id = b.id) not a complete request.

 SELECT a.name AS A_Name, b.name AS B_Name FROM a INNER JOIN b ON a.id = b.id 

or (suppose) if you have more table names and want to make them short,

 SELECT a.name AS A_Name, b.name AS B_Name FROM longTableNameA a INNER JOIN longTableNameB b ON a.id = b.id