The result of a SQL query is always a table of data, just like the tables in the database. If you type a SELECT statement using interactive SQL, the DBMS displays the query results in tabular form on your computer screen. If a program sends a query to the DBMS using programmatic SQL, the table of query results is returned to the program. In either case, the query results always have the same tabular, row/column format as the actual tables in the database, as shown in Figure 6-2. Usually the query results will be a table with several columns and several rows. For example, this query produces a table of three columns (because it asks for three items of data) and ten rows (because there are ten salespeople):
List the names, offices, and hire dates of all salespeople.
SELECT NAME, REP_OFFICE, HIRE_DATE
FROM SALESREPS
In contrast, the following query produces a single row because only one salesperson has the requested employee number. Even though this single row of query results looks less “tabular” than the multirow results, SQL still considers it to be a table of three columns and one row.
What are the name, quota, and sales of employee number 107?
SELECT NAME, QUOTA, SALES
FROM SALESREPS
WHERE EMPL_NUM = 107
NAME QUOTA SALES
Nancy Angelli $300,000.00 $186,042.00
In some cases the query results can be a single value, as in the following example:
What are the average sales of our salespeople?
SELECT AVG(SALES)
FROM SALESREPS
AVG(SALES)
$289,353.20
These query results are still a table, although it’s a very small one consisting of one column and one row.
Finally, it’s possible for a query to produce zero rows of query results, as in this example:
List the name and hire date of anyone with sales over $500,000.
SELECT NAME, HIRE_DATE
FROM SALESREPS
WHERE SALES > 500000.00
NAME HIRE_DATE
————————-
Even in this situation, the query results are still a table. This one is an empty table with two columns and zero rows.
Note that SQL’s support for missing data extends to query results as well. If a data item in the database has a NULL value, the NULL value appears in the query results when the data item is retrieved. For example, the SALESREPS table contains NULL values in its QUOTA and MANAGER columns. The next query returns these NULL values in the second and third columns of query results.
List the salespeople, their quotas, and their managers.
The fact that a SQL query always produces a table of data is very important. It means that the query results can be stored back into the database as a table. It means that the results of two similar queries can be combined to form a larger table of query results. Finally, it means that the query results can themselves be the target of further queries. A relational database’s tabular structure thus has a very synergistic relationship with the relational query facilities of SQL. Tables can be queried, and queries produce tables.
Source: Liang Y. Daniel (2013), Introduction to programming with SQL, Pearson; 3rd edition.