CODEWITHSUNDEEP
sqloracle-database
Will
Will

Reputation: 2978

Oracle SQL Syntax: Column bases

Currently I'm thinking about some artificially created SQL statements and want to know the base tables to each attributes in the statement. However there are statements where we can't determine the base table without having the table definition. As I will apply my thoughts on Oracle SQL statements I want to know if all the statements that I made up, will actually be valid Oracle SQL?

1)

select a from table

attribute a belongs to table, as there is no other base table

2)

select a from table t

attribute a belongs to table, as there is no other base table

3)

select t.a from table t

attribute a belongs to table, according to prefix.

4)

select a from table1, table2

attribute a either belongs to table1 or table2, we can't say without the table definitions

5)

select a from table1 t1, table2

attribute a either belongs to table1 or table2, we can't say without the table definitions

6)

select a from (select * from table t) s

attribute a belongs to table, as the subselect in the from clause only contains table.

Best, Will

Upvotes: 1

Views: 108

Answers (2)

Gary Myers
Gary Myers

Reputation: 35401

Not quite complete

select a from table

"A" could be a user defined function rather than a table column (or a synonym for a function). There are also several built in pseudo-columns (such as ROWNUM, LEVEL) plus things like SYSDATE.

Also, if the statements is in PL/SQL, then "A" may also be a variable local to the PL/SQL program unit.

select t.a from table t

There are some weird precedence rules worth reading. "T.A" could refer to a pl/sql package "T" with a function "A", or it could refer to a schema "T" with a function "A".

Also beware of "sequence_name.nextval" as another special case

Upvotes: 2

Paul Creasey
Paul Creasey

Reputation: 28834

All should be valid, but 4 and 5 may be invalid if both table1 and table2 contain a column named a since that would be ambiguous.

Upvotes: 2

Related Questions