SQL Teaser: Guess the output

[SQLDenis]

Here is a small teaser, can you guess the output?

SELECT d.c-d.b/d.a 
FROM(SELECT 1,2,5)d(a,b,c)

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[ptheriault]

What does that work? I would have thought it would fail because there is no alias name to (a,b,c).

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[SQLDenis]

How about this one?

SELECT d.c-d.b/d.a
FROM(SELECT 1 c,2 b,5 a)d(a,b,c)

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[SQLDenis]

>>I would have thought it would fail because there is no alias name to (a,b,c).


there is external column aliasing

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[ptheriault]

at the risk of sounding ignorant....

what is external column aliasing?

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[SQLDenis]

--internal column aliasing
select * from(
select 1 as a) b

--external column aliasing
select * from(
select 1)b(a)

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[ptheriault]

Ohhhh...

And that's why the answer is..

Spoiler

3

because 5-2 / 1 = 3

- Paul
- If at first you don't succeed, find out if the loser gets anything.