A big difference is :
A SQL derived table differs from a temporary table in that a SQL derived table exists only for the duration of the query, while a temporary table exists until the server is brought down.

1, the derived table is generated
2. the join process kicks in and it can use three diferrent option.
a. nested loop : for each row in table A is scans all rows in table B for a match
Table A : driving Table , Table B driven Table (the optimizer will choose the smaller table as the driving so the execution will result with less loops. )

b. Hash join : The hash join first scans or computes the entire build input and then builds a hash table in memory. Each row is inserted into a hash bucket depending on the hash value computed for the hash key. This build phase is followed by the probe phase. The entire probe input is scanned or computed one row at a time, and for each probe row, the hash key's value is computed, the corresponding hash bucket is scanned, and the matches are produced.

c. The merge join requires that both inputs be sorted on the merge columns, which are defined by the equality (WHERE) clauses of the join predicate

poor_beggar,
>>if not ginving the Join statement SQL changes the , between 2 tables into a Inner join ;-) <<
You had better double check that. While SQL Server is very smart, what it cannot do is fix a comple error. Thre is no way in Hell that SQL Server would convert this to an INNER JOIN:
Select * from Products P
(select ID, Name where country = 'UK' From shops) S
ON P.ID = S.ID
Where P.ID IN (1,2,4,55,66)

0

Featured Post

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Introduction
SQL Server Integration Services can read XML files, that’s known by every BI developer. (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.)
But how far can you go? When does the XML Source component become …

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…

Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.