I'm learning SQL Server and have a question between nested Subquery vs Derived table using from clause. Example for nested Subquery where it is using the from clause. Example was taken from the link : https://www.tutorialgateway.org/sql-subquery/
USE [SQL Tutorial]
GO
SELECT subquery.FirstName + ' ' + subquery.LastName AS [Full Name]
,subquery.[Occupation]
,subquery.[YearlyIncome]
,subquery.[Sales]
FROM (
SELECT [Id]
,[FirstName]
,[LastName]
,[Education]
,[Occupation]
,[YearlyIncome]
,[Sales]
FROM [Employee Table]
WHERE [Sales] > 500
) AS [subquery]
Example for Derived table where it is using the from clause. Example was taken from the link : https://www.tutorialgateway.org/sql-derived-table/
USE [SQLTEST]
GO
SELECT *
FROM (
SELECT [EmpID]
,[FirstName]
,[LastName]
,[Education]
,[YearlyIncome]
,[Sales]
,[DeptID]
FROM [EmployeeDetails]
) AS [Derived Employee Details]
WHERE [Sales] > 500
What makes the nested subquery different form the derived table?
A derived table is specifically a subquery that is used in the from
clause, that returns a result set with an arbitrary number of columns and rows.
A subquery is more generic and refers to any query-within-a-query. One type of subquery, for instance, is a scalar subquery. Such a subquery returns at most one row and one column. It can be used in select
and where
(and some other places) where a scalar value can be used. A scalar subquery can also be used in the from
clause.