Oracle 23ai: SQL Productivity Power-Up – Streamlined SELECT Syntax

 

Introduction

Oracle 23ai clearly shows that Oracle is putting developers first when it comes to SQL. For a long time, writing Oracle SQL meant putting up with extra clutter—things like always using FROM DUAL or repeating the same expressions again and again in GROUP BY clauses. It worked, but it often made simple queries feel unnecessarily complicated.

With the latest releases, Oracle is clearly trying to make SQL easier and more pleasant to use. The focus is on writing cleaner, more natural code with less repetition. In this blog, I will look at four small but powerful improvements that remove a lot of that old hassle and let you focus on the data and logic instead of outdated syntax rules.

SELECT without FROM

Previously, Oracle required a FROM clause for every SELECT statement, forcing developers to use the DUAL to retrieve constants, system dates, or perform calculations. Now, you can simply SELECT what you need. It is cleaner, shorter, and matches the behavior of PostgreSQL and SQL Server.

Example 

In this example, we retrieve the current system date, add a simple label, and calculate a projected value, all without using DUAL.

SELECT 
    SYSDATE AS current_time, 
    'HR_REPORT' AS report_tag, 
    (100 * 1.15) AS projected_value;

VALUES Clause Inside SELECT

The VALUES clause used to be limited to INSERT statements, but Oracle 23ai elevates it into a powerful table constructor. You can now use VALUES directly inside a SELECT, effectively creating a small in-memory table on the fly.

This is especially useful when you need a temporary lookup set without creating a table, using UNION ALL, or relying on temporary tables.

Example: 

In this example, the VALUES clause is used to create a temporary, in-memory table directly inside the query. Think of it as a small dataset that exists only for the duration of this SELECT. Each row in the VALUES list represents an employee ID paired with a performance rating.

The alias v(emp_id, rating) gives names to the two columns created by the VALUES clause. This is important because it allows Oracle to treat the result just like a normal table, with clearly defined column names.

The query then joins this temporary table to the EMPLOYEES table using a standard JOIN. The join condition matches employees.employee_id with v.emp_id, ensuring that only employees listed in the VALUES clause are returned.

As a result, the query displays each employee’s last name alongside the temporary rating assigned in the VALUES list. 

SELECT e.last_name, v.rating
FROM employees e
JOIN (VALUES (100, 'A+'), (101, 'B'), (102, 'A')) AS v(emp_id, rating)
  ON e.employee_id = v.emp_id;
  
---The following is another syntax for it with the same output
  SELECT e.last_name, v.rating
FROM employees e,  (VALUES (100, 'A+'), (101, 'B'), (102, 'A')) AS v(emp_id, rating)
  where e.employee_id = v.emp_id;

GROUP BY Alias

Repeating long expressions in the GROUP BY clause has always been one of Oracle SQL’s biggest annoyances. If you used a CASE expression or formatted value in the SELECT, you had to copy it exactly into the GROUP BY.

Oracle 23ai finally fixes this by allowing you to reference column aliases directly. This makes queries shorter, clearer, and much easier to maintain.

Example 

In this example, employees are grouped into salary bands using a CASE expression. Instead of repeating the entire expression in GROUP BY, I simply reuse the alias salary_level.

SELECT 
    CASE WHEN salary > 10000 THEN 'High' ELSE 'Standard' END AS salary_level,
    COUNT(*) AS total_staff
FROM employees
GROUP BY salary_level;

GROUP BY ALL

Complex reports often require grouping by many columns, which means copying those same column names into the GROUP BY clause. This is tedious and error-prone. GROUP BY ALL solves this by automatically grouping on every non-aggregated column in the SELECT list.

Example

This query calculates the average salary for each combination of department, job, and manager. It uses GROUP BY ALL to automatically group by all the non-aggregated columns in the SELECT list, so you don’t have to list them again manually.

The HAVING clause then filters the grouped results, keeping only those groups where the average salary is greater than 5,000. In addition, this example shows that in Oracle 23ai, you can use a column alias (avg_sal) directly in the HAVING clause, which makes the query shorter and easier to read.

SELECT 
    d.department_name, 
    e.job_id, 
    e.manager_id,
    ROUND(AVG(e.salary), 2) AS avg_sal
FROM employees e , departments d 
 Where  e.department_id = d.department_id
GROUP BY ALL
Having avg_sal >5000;

Conclusion

Oracle 23ai and 26ai make it clear that Oracle is focusing more on developers and their day-to-day work. These updates don’t change the power of Oracle SQL, but they make it much easier and nicer to use. By removing extra syntax, writing SQL becomes simpler, clearer, and less error-prone.

Whether you’re creating reports, running analytics, or just querying data, these improvements will quickly feel natural. After using them, the older ways of writing SQL will seem outdated, and that’s a good thing.