Derived Tables

Subqueries

A subquery is a SELECT statement that is nested within another statement – that’s why it’s called a subquery, because it’s like having a query within another query . Subqueries are usually used in the WHERE clause as a way to filter out certain rows returned in the result set of the outer query.

Here are some examples of subqueries

select employee_name 
from employee
where employee_salary > (
                        select avg(employee_salary)
                        from employee
                        )

select avg(employee_salary) from employee: is the subquery

SELECT last_name
FROM employee
WHERE employee_number IN (
                         SELECT manager_employee_number
                         FROM department
                         )

SELECT manager_employee_number FROM department: is the subquery

INSERT INTO math_study_group (id, name)
SELECT id, name
FROM student_details
WHERE subject= 'Math' 

SELECT id, name FROM student_details WHERE subject= 'Math': is the subquery

Derived Tables

A derived table is basically a subquery, except it is always in the FROM clause of a SQL statement. The reason it is called a derived table is because it essentially functions as a table as far as the entire query is concerned.

But, remember that a derived table only exists in the query in which it is created. So, derived tables are not actually part of the database schema because they are not real tables.

Here is an example of a derived table

SELECT OrderNum, OrderDate, C.CustomerNum, AmountOrder, SumPrice
FROM (
         SELECT O.OrderNum, O.OrderDate, O.CustomerNum, COUNT(O.OrderNum) AS AmountOrder, SUM(NumOrdered * QuotedPrice) AS SumPrice
         FROM Orders O
             INNER JOIN OrderLine OL ON O.OrderNum = OL.OrderNum
         GROUP BY O.OrderNum, O.OrderDate, O.CustomerNum
     )
    AS OrderDetail
    INNER JOIN Customer C ON C.CustomerNum = OrderDetail.CustomerNum
WHERE C.CustomerNum = 608
GO

Common Table Expressions

The Common Table Expressions or CTE’s for short are used within SQL Server to simplify complex joins and subqueries, and to provide a means to query hierarchical data such as an organizational chart.

Simple CTE structure

WITH expression_name [(column_name [,...n])]
AS
(
     CTE_query_definition
)

SELECT <column_list> FROM expression_name

IRL Example

WITH OrderDetail (OrderNum, OrderDate, CustomerNum, AmountOrder, SumPrice)
AS
(
    SELECT O.OrderNum, O.OrderDate, O.CustomerNum, COUNT(O.OrderNum) AS AmountOrder, SUM(NumOrdered * QuotedPrice) AS SumPrice
    FROM Orders O
        INNER JOIN OrderLine OL ON O.OrderNum = OL.OrderNum
    GROUP BY O.OrderNum, O.OrderDate, O.CustomerNum
)
SELECT *
FROM OrderDetail
WHERE OrderDetail.CustomerNum = 608
GO

Inline Table-Valued Function

This is function that return a table

Here is how to create one

CREATE FUNCTION func_OrderDetail(@customerNum INT) RETURNS TABLE
AS
RETURN
(
    SELECT O.OrderNum, O.OrderDate, O.CustomerNum, COUNT(O.OrderNum) AS AmountOrder, SUM(NumOrdered * QuotedPrice) AS SumPrice
    FROM Orders O
    INNER JOIN OrderLine OL ON O.OrderNum = OL.OrderNum
    WHERE O.CustomerNum = @customerNum
    GROUP BY O.OrderNum, O.OrderDate, O.CustomerNum
);
GO

SELECT * FROM func_OrderDetail(608)
GO