0

just out of curiosity: Is there a precedence on column names in SQL standard?

Let's take this query:

select * from mytable m1
where col1 = 123
and exists
(
  select * from mytable m2
  where m2.col2 = m1.col2 and m2.col1 = 456
);

In Oracle I can omit the qualifier m2 on col1 and col2, because the inner table (m2) has precedence over the outer table (m1). The line would then be

  where col2 = m1.col2 and col1 = 456

and still work. Is this behaviour standard SQL? I.e. can I rely on this query working in any standard compliant dbms? Or is this just a convienience enhancement offered by Oracle (and probably most other dbms, too)?

Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73

1 Answers1

2

Standard ANSI/ISO SQL behaves exactly like that! (But I can't guarantee all dbms products follow the standard, so I suggest keep the qualifying.)

jarlh
  • 42,561
  • 8
  • 45
  • 63
  • Thank you. I find those ISO/ANSI documents very hard to read and couldn't find any note on this topic. (Can you quote something from there describing it?) In EXISTS clauses I usually qualify all columns, as I am dealing both with columns from inside and outside the block, but in an IN clause I would usually only work with the inner table's columns unqualified. It's good to know that I do this in consent with the SQL standard. – Thorsten Kettner Jan 12 '15 at 15:04