Assume I have a SQL statement as bellows:
You may notice the SQL statement which had been highlighted is duplicated.
You can utilize "with as..." to do SQL refactor.
The WITH clause, or subquery factoring clause, is part of the SQL-99 standard and was added into the Oracle SQL syntax in Oracle 9.2. The WITH clause may be processed as an inline view or resolved as a temporary table. The advantage of the latter is that repeated references to the subquery may be more efficient as the data is easily retrieved from the temporary table, rather than being requeried by each reference. You should assess the performance implications of the WITH clause on a case-by-case basis.
The foregoing SQL statement will be amended as following: