Derived Tables
Feature compatibility | Amazon SCT / Amazon DMS automation level | Amazon SCT action code index | Key differences |
---|---|---|---|
|
|
N/A |
N/A |
SQL Server Usage
SQL Server implements derived tables as specified in ANSI SQL:2011. Derived tables are similar to CTEs, but the reference to another query is used inside the FROM
clause of a query.
This feature enables you to write more sophisticated, complex join queries.
Examples
SELECT name, salary, average_salary FROM (SELECT AVG(salary) FROM employee) AS workers (average_salary), employee WHERE salary > average_salary ORDER BY salary DESC;
For more information, see FROM clause plus JOIN, APPLY, PIVOT (Transact-SQL)
PostgreSQL Usage
PostgreSQL implements derived tables and is fully compatible with SQL Server derived tables.
Examples
SELECT name, salary, average_salary FROM (SELECT AVG(salary) FROM employee) AS workers (average_salary), employee WHERE salary > average_salary ORDER BY salary DESC;
For more information, see Table Expressions