① ORACLE vs PostgreSQL Join type/feature PostgreSQL Oracle Natural joins (only tested: NATURAL LEFT JOIN) USING-clause FULL joins3 (tested: SELECT...FULL JOIN...ON...=...) Explicit CROSS JOIN (cartesian product)
The SELECT statement PostgreSQL Allows ORDER BY in contexts other than cursor definitions. NULLs are considered higher than any non-NULL value. DOCUMENTATIONOracle Allows ORDER BY in contexts other contexts than cursor definitions. By default, NULLs are considered higher than any non-NULL value; however, this sorting behaviour may be changed by adding NULLS FIRST or NULLS LAST to the ORDER BY expression. Beware of Oracle's strange treatment of empty strings and NULLs as the same 'value'. DOCUMENTATIONLimiting result sets PostgreSQL Doesn't support ROW_NUMBER(). Supports cursors (in all contexts, not only in embedded, dynamic SQL). Alternative to using ROW_NUMBER(): SELECT columns FROM tablename ORDER BY key ASC LIMIT n Note that LIMIT changes the semantics of SELECT...FOR UPDATE.Oracle Supports ROW_NUMBER. Seems to have non-compliant cursor facilities. As Oracle doesn't allow AS for subquery naming (and doesn't need a subquery-name at all in this case), the standard SQL code above needs to be rewritten slightly: SELECT * FROM ( SELECT ROW_NUMBER() OVER (ORDER BY key ASC) AS rownumber, columns FROM tablename ) WHERE rownumber <= n Top-n query PostgreSQL Supports the slow standard SQL query variant. In practice, a PostgreSQL-only method should be used, in order to obtain acceptable query performance. SELECT * FROM person WHERE ( age <= ( SELECT age FROM person ORDER BY age ASC LIMIT 1 OFFSET 2 -- 2=n-1 ) ) IS NOT FALSE (Change <= to >= and ASC to DESC in the positions ed like this in order to get a top-3 oldest query instead.) Oracle Supports the fast standard SQL variant. However, as Oracle doesn't like "AS ..." after subqueries (and doesn't require naming of subqueries), the query has to be paraphrased slightly: SELECT * FROM ( SELECT RANK() OVER (ORDER BY age ASC) AS ranking, person_id, person_name, age FROM person ) WHERE ranking <= 3 (Change ASC to DESC in the position ed like this in order to get a top-3 oldest query instead.)
Join type/feature PostgreSQL Oracle
Natural joins (only tested: NATURAL LEFT JOIN)
USING-clause
FULL joins3 (tested: SELECT...FULL JOIN...ON...=...)
Explicit CROSS JOIN (cartesian product)
PostgreSQL Allows ORDER BY in contexts other than cursor definitions. NULLs are considered higher than any non-NULL value.
DOCUMENTATIONOracle Allows ORDER BY in contexts other contexts than cursor definitions. By default, NULLs are considered higher than any non-NULL value;
however, this sorting behaviour may be changed by adding NULLS FIRST or NULLS LAST to the ORDER BY expression.
Beware of Oracle's strange treatment of empty strings and NULLs as the same 'value'.
DOCUMENTATIONLimiting result sets
PostgreSQL Doesn't support ROW_NUMBER(). Supports cursors (in all contexts, not only in embedded, dynamic SQL).
Alternative to using ROW_NUMBER():
SELECT columns
FROM tablename
ORDER BY key ASC
LIMIT n
Note that LIMIT changes the semantics of SELECT...FOR UPDATE.Oracle Supports ROW_NUMBER. Seems to have non-compliant cursor facilities.
As Oracle doesn't allow AS for subquery naming (and doesn't need a subquery-name at all in this case), the standard SQL code above needs to be rewritten slightly:
SELECT * FROM (
SELECT
ROW_NUMBER() OVER (ORDER BY key ASC) AS rownumber,
columns
FROM tablename
)
WHERE rownumber <= n
Top-n query
PostgreSQL Supports the slow standard SQL query variant. In practice, a PostgreSQL-only method should be used, in order to obtain acceptable query performance.
SELECT *
FROM person
WHERE (
age <= (
SELECT age FROM person
ORDER BY age ASC
LIMIT 1 OFFSET 2 -- 2=n-1
)
) IS NOT FALSE
(Change <= to >= and ASC to DESC in the positions ed like this in order to get a top-3 oldest query instead.)
Oracle Supports the fast standard SQL variant. However, as Oracle doesn't like "AS ..." after subqueries (and doesn't require naming of subqueries), the query has to be paraphrased slightly:
SELECT * FROM (
SELECT
RANK() OVER (ORDER BY age ASC) AS ranking,
person_id,
person_name,
age
FROM person
)
WHERE ranking <= 3
(Change ASC to DESC in the position ed like this in order to get a top-3 oldest query instead.)