Infrequent SQL developers often get confused about when to put parentheses and/or aliases on derived tables. There has been this recent Reddit discussion about the subject, where user Elmhurstlol was wondering why they needed to provide an alias to the derived table (the subselect with the UNION) in the following query: SELECT AVG(price) AS AVG_PRICE … Continue reading Should I Put That Table Alias or Not?
Tag: subquery
Oracle scalar subquery caching
The importance of being able to fully control executed SQL (using jOOQ, or plain JDBC) on large-scale systems becomes obvious every time you need to fine-tune your SQL queries for a specific RDBMS. In this case, we're looking at Oracle and its miraculous scalar subquery caching mechanisms: Usually, the context switch from SQL to PL/SQL … Continue reading Oracle scalar subquery caching