Search code examples
sqlstandards-complianceoperator-precedence

SQL name precedence

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)?

Solution

  • 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.)