Derived Tables (Table expressions)

Table expressions are subqueries that are used where a table is expected. There are two types of table expressions:

• Derived tables
• Common table expressions

A derived table is a table expression that appears in the FROM clause of a query. You can apply derived tables when the use of column aliases is not possible because another clause is processed by the SQL translator before the alias name is known. Example 6.75 shows an attempt to use a column alias where another clause is processed before the alias name is known.

Get all existing groups of months from the enter_date column of the works_on table:

USE sample;
SELECT MONTH(enter_date) as enter_month
FROM works_on
GROUP BY enter_month;Code language: PHP (php)

The result is

Message 207: Level 16, State 1, Line 4
The invalid column 'enter_month'Code language: JavaScript (javascript)

The reason for the error message is that the GROUP BY clause is processed before the corresponding SELECT list, and the alias name enter_month is not known at the time the grouping is processed.

By using a derived table that contains the preceding query (without the GROUP BY clause), you can solve this problem, because the FROM clause is executed before the GROUP BY clause, as shown in Example 6.76.

USE sample;
SELECT enter_month
	FROM (SELECT MONTH(enter_date) as enter_month
		FROM works_on) AS m
GROUP BY enter_month;Code language: PHP (php)

The result is

Generally, it is possible to write a table expression any place in a SELECT statement where a table can appear. (The result of a table expression is always a table or, in a special case, an expression.) Example 6.77 shows the use of a table expression in a SELECT list.

USE sample;
SELECT w.job, (SELECT e.emp_lname
		FROM employee e WHERE e.emp_no = w.emp_no) AS name
	FROM works_on w
	WHERE w.job IN('Manager', 'Analyst');Code language: PHP (php)

The result is