CODEWITHSUNDEEP
sqlinformix
Artem Kharytoniuk
Artem Kharytoniuk

Reputation: 1376

Table alias scope in informix SQL

In the following example SQL

select * from (
  select col
  from myTable mt
  inner join anotherMyTable amt on mt.id = amt.id
) as t
where 
  exists (select amt.colX from anotherMyTable amt where amt.id = 42)

The 'amt' alias is defined in two places. Is it correct to declare the second table alias with the same name as the first or I should use another name (amt2) ?

In this example I assume that both aliases are located in different scopes so it's okay to use the same name. I use Informix DBMS.

p.s. it's an example SQL, the question is only about table alias scope.

Upvotes: 2

Views: 417

Answers (1)

Yosi Dahari
Yosi Dahari

Reputation: 6999

In this scope:

exists (select amt.colX from anotherMyTable amt where amt.id = 42)

There is no amt alias defined, so you are allowed to use this alias.

both examples below are wrong:

select * from (
  select col
  from myTable amt
  inner join anotherMyTable amt on amt.id = amt.id
) as t
where 
  exists (select amt.colX from anotherMyTable amt where amt.id = 42)


select * from (
  select col
  from myTable mt
  inner join anotherMyTable amt on mt.id = amt.id
) as amt
where 
  exists (select amt.colX from anotherMyTable amt where amt.id = 42)

Upvotes: 1

Related Questions