sql-servert-sqlsubqueryderived-table

Nested Subquery vs Derived table


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?


Solution

  • 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.