Do Temporary tables have any form of index in SQL?

Question

Consider a simple query like this:

SELECT * FROM DATA
JOIN
(
SELECT * FROM DATA
)TEMPORARY_DATA
ON TEMPORARY_DATA.DATA_IDN = DATA.DATA_IDN

What is the performance of this? Is it O(n2) because TEMPORARY_DATA doesn't have indexes as opposed to normal tables. Normal joins I think happens in O(nlogn) time because it is indexed right?

Does this change if the inner query is slightly complex and so direct? I mean is it possible the optimized it not able to guess that it is not the indexed DATA table!

I am newbie to these things, please let me know if this belongs to some other place on the SO Network.

Answer 1

(The following answer uses SQL Server RDBMS to explain)

First off, there are no temporary tables in your question. Temporary tables (in SQL Server) are actual tables that live in tempdb, and are defined with one or two pound signs (#) preceding it. What you have is a simple alias (temporary_data). Two extremely different things.

That is an extremely long and verbose way of writing a join on a table to itself. Take this table structure:

create table dbo.Data
(
    Id int not null
);
go

I will insert a few rows:

insert into dbo.Data
values (1), (2), (3);
go

The result set of:

select *
from dbo.Data
join
(
    select *
    from dbo.Data
) temporary_data
on temporary_data.Id = Data.Id;

Is...

Id  Id
1   1
2   2
3   3

Looking at the post execution plan of this operation, SQL Server just does a join on these two tables:

Logically this is the exact same query (results and execution plan) as the following:

select *
from dbo.Data d1
inner join dbo.Data d2
on d1.Id = d2.Id;

It is just an extremely long way to write it. Nothing is gained by the former, and the only thing lost is readability.