Why can identifiers in SQL be referenced before assignment?

Question

In a SQL query you can reference a name before that name has been declared.

SELECT A.id FROM table_a A;

What is the history of this behavior and how does it relate to programming languages that require you to assign to variables before you can reference them?

None of that is a "variable". A variable would be `declare @id int`. In TSql you also cant reference a variable before it is declared so there is no change there from any other programming language. — Igor, Nov 05 '20 at 21:58
@igor What's the correct terminology instead of variable. What is 'A' here? — Ian Danforth, Nov 05 '20 at 22:03
`A` is an alias that references `table_a` which happens to be a table. — Igor, Nov 05 '20 at 22:08

score 4 · Accepted Answer · answered Nov 05 '20 at 22:08

4

You are misunderstanding the nature of the SQL language. It is a not a procedural language, but a declarative language. The statement describes what the result should look like, and the database builds the actual query plan accordingly - which, by default, you don't get to see.

Some databases provide some kind of procedural sub-language, which can be used to write procedures - eg PL/SQL in Oracle. However, what you are showing here is a standard SQL SELECT statement. There is no notion of variable declaration in there. A in the FROM clause is an alias for table_a, aka an identifier, that you can the use to refer to the columns of table, using an expression like <table identifier>.<column name>.

answered Nov 05 '20 at 22:08

GMB

216,147
25
84
135

One nuance to help understanding, is this done in stages? Is there an alias resolution / parsing stage, prior to creation of the query plan? – Ian Danforth Nov 05 '20 at 22:13
@IanDanforth: you don't need to worry about how the database proceeds the query (unless you are doing some advanced optimization). You just need to focus on how to write valid SQL, that properly describes the result you want. – GMB Nov 05 '20 at 22:29

Lukasz Szozda · Answer 2 · 2020-11-06T17:51:25.777

Why can identifiers in SQL be referenced before assignment?

It is alias for a table. But key point here is that SQL should not be read top-down.

SELECT A.id 
FROM table_a A;

In reality is executed more like:

FROM table_a A
SELECT A.id;

Now it makes perfect sense.

Going further:

SELECT A.id + 1 AS c 
FROM table_a A
WHERE c = 10;
-- error, we cannot use c, even if it was defined 2 lines before

Because the order is as follow:

FROM table_a A
WHERE c = 10       -- here C is not defined
SELECT A.id + 1 c;

Even on the same level in SELECT it is also not possible to reuse defined alias:

 SELECT a + 1 AS b, b + 1 AS c, c + 1 AS d
 FROM tab
 -- error
 -- (some databases allow to use lateral column aliasing)

Why can identifiers in SQL be referenced before assignment?

2 Answers2