Derived Tables - SQL Server to Aurora PostgreSQL Migration Playbook
Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China.

Derived Tables

Feature compatibility Amazon SCT / Amazon DMS automation level Amazon SCT action code index Key differences


                              Five star feature compatibility


                              Five star automation level

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) in the SQL Server documentation.

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 in the PostgreSQL documentation.