How to: Create Queries using Something Besides a Table (Visual Database Tools)

Whenever you write a retrieval query, you articulate what columns you want, what rows you want, and where the query processor should find the original data. Typically, this original data consists of a table or several tables joined together. But the original data can come from sources other than tables. In fact, it can come from views, queries, synonyms, or user-defined functions that return a table.

Using a View in Place of a Table

You can select rows from a view. For example, suppose the database includes a view called "ExpensiveBooks," in which each row describes a title whose price exceeds 19.99. The view definition might look like this:

SELECT *
FROM titles
WHERE price > 19.99

You can select the expensive psychology books merely by selecting the psychology books from the ExpensiveBooks view. The resulting SQL might look like this:

SELECT *
FROM ExpensiveBooks
WHERE type = 'psychology'

Similarly, a view can participate in a JOIN operation. For example, you can find the sales of expensive books merely by joining the sales table to the ExpensiveBooks view. The resulting SQL might look like this:

SELECT *
FROM sales 
         INNER JOIN 
         ExpensiveBooks 
         ON sales.title_id 
         =  ExpensiveBooks.title_id

For more information about adding a view to a query, see How to: Add Tables to Queries (Visual Database Tools).

Using a Query in Place of a Table

You can select rows from a query. For example, suppose you have already written a query retrieving titles and identifiers of the coauthored books — the books with more than one author. The SQL might look like this:

SELECT 
     titles.title_id, title, type
FROM 
     titleauthor 
         INNER JOIN
         titles 
         ON titleauthor.title_id 
         =  titles.title_id 
GROUP BY 
     titles.title_id, title, type
HAVING COUNT(*) > 1

You can then write another query that builds on this result. For example, you can write a query that retrieves the coauthored psychology books. To write this new query, you can use the existing query as the source of the new query's data. The resulting SQL might look like this:

SELECT 
    title
FROM 
    (
    SELECT 
        titles.title_id, 
        title, 
        type
    FROM 
        titleauthor 
            INNER JOIN
            titles 
            ON titleauthor.title_id 
            =  titles.title_id 
    GROUP BY 
        titles.title_id, 
        title, 
        type
    HAVING COUNT(*) > 1
    ) 
    co_authored_books
WHERE     type = 'psychology'

The emphasized text shows the existing query used as the source of the new query's data. Note that the new query uses an alias ("co_authored_books") for the existing query. For more information about aliases, see How to: Create Table Aliases (Visual Database Tools) and How to: Create Column Aliases (Visual Database Tools).

Similarly, a query can participate in a JOIN operation. For example, you can find the sales of expensive coauthored books merely by joining the ExpensiveBooks view to the query retrieving the coauthored books. The resulting SQL might look like this:

SELECT 
    ExpensiveBooks.title
FROM 
    ExpensiveBooks 
        INNER JOIN
        (
        SELECT 
            titles.title_id, 
            title, 
            type
        FROM 
            titleauthor 
                INNER JOIN
                titles 
                ON titleauthor.title_id 
                =  titles.title_id 
        GROUP BY 
            titles.title_id, 
            title, 
            type
        HAVING COUNT(*) > 1
        )

For more information about adding a query to a query, see How to: Add Tables to Queries (Visual Database Tools).

Using a User-Defined Function in Place of a Table

In SQL Server 2000 or higher, you can create a user-defined function that returns a table. Such functions are useful for performing complex or procedural logic.

For example, suppose the employee table contains an additional column, employee.manager_emp_id, and that a foreign key exists from manager_emp_id to employee.emp_id. Within each row of the employee table, the manager_emp_id column indicates the employee's boss. More precisely, it indicates the employee's boss's emp_id. You can create a user-defined function that returns a table containing one row for each employee working within a particular high-level manager's organizational hierarchy. You might call the function fn_GetWholeTeam, and design it to take an input variable — the emp_id of the manager whose team you want to retrieve.

You can write a query that uses the fn_GetWholeTeam function as a source of data. The resulting SQL might look like this:

SELECT * 
FROM 
     fn_GetWholeTeam ('VPA30890F')

"VPA30890F" is the emp_id of the manager whose organization you want to retrieve. For more information about adding a user-defined function to a query, see How to: Add Tables to Queries (Visual Database Tools). For a complete description of user-defined functions, see User-Defined Functions (Database Engine).