Nested Subquery vs Derived table

Question

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. Thank you for your time.

They are identical? What is different between your 2 queries? A nested sub-query **IS** a derived table. — Dale K, Sep 07 '21 at 01:02
As per your tutorial "The SQL Derived Table is nothing but a Subquery used in the From Clause." — Dale K, Sep 07 '21 at 01:04
From the article [SQL syntax](https://en.wikipedia.org/wiki/SQL_syntax#Derived_table) on Wikipedia: "A _derived table_ is the use of referencing an SQL subquery in a FROM clause. Essentially, the derived table is a subquery that can be selected from or joined to. The derived table functionality allows the user to reference the subquery as a table. The inline view is also referred to as an _inline view_ or a _subselect_. " — DavidRR, Apr 11 '22 at 14:19

score 1 · Accepted Answer · answered Sep 07 '21 at 01:13

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.

Nested Subquery vs Derived table

1 Answers1