Come, young one. Come sit by the open fire; hold your cold hands to its warmth. Let me tell you a story. Back when I was young, the world was a simpler place: we roamed the plains, hunted game with our spears – and Oracle Select statements consisted of only a SELECT, a FROM and, possibly, a WHERE.
Things done changed.
In truth you’ll have to go back 20 years to find a time when the SELECT statement wasn’t a Swiss army knife with numerous, weird, clauses jutting out of it – from subquery factoring clauses to hierarchical query clauses. The purpose of this series is to shine light on some of those clauses, and help Oracle developers – especially those graduating from my Oracle For Absolute Beginners series – get to grips with them.
The With Clause
The With Clause – or, if you prefer fancy words, the subquery factoring clause – is a (optional) limb that grew out of the basic select statement as part of Oracle 9.2, back in 2002. The WITH clause allows you, as part of your select statement, to assign a name to a subquery and utilise its results by referencing that name.
It is, on first glance, quite jarring. Because the subquery factoring clause brutally transforms the look of a query, making it no longer start with the SELECT keyword.
WITH <alias> AS (subquery-select-sql) SELECT <column-list> FROM <alias>;
The advantage of using the WITH clause is that, once you have defined your subquery, you can subsequently reference it repeatedly in your main select statement. Oracle will optimise your subquery, resolving it as an inline view or a temporary table. Also, counterintuitively, once you’ve come to recognise the odd-looking syntax, the WITH clause does actually make complex SELECT statements easy to read. However, let us start with a reasonably simple example:
WITH emp_count AS (SELECT COUNT(*) num, deptno FROM emp GROUP BY deptno) SELECT dname department, loc location, num "number of employees" FROM dept, emp_count WHERE dept.deptno = emp_count.deptno;
What the above SELECT statement does, basically, is create a subquery that gets a count of employees by department (it names the subquery emp_count), and then calls that subquery in the FROM clause of the main select statement the way it would call a table or view.
Our query is, as I promised, a reasonably simple one, but you can probably already begin to see how, using the WITH clause, you can corral complex subqueries, leaving your main SELECT statement relatively simple.
But before we venture down that rabbithole, let’s revisit something I said earlier: you can reference your subquery repeatedly. This makes sense if you spare it some thought. After all, it’s a temporary table, and SELECT statements can reference the same table more than once. I’ll show you what I mean.
WITH avgsal AS (SELECT avg(sal) avgsal, job FROM emp GROUP BY job) SELECT s1.avgsal "Manager Average", s2.avgsal "Salesman Average" FROM avgsal s1, avgsal s2 WHERE s1.job = 'MANAGER' AND s2.job = 'SALESMAN';
This ability to reference our named subquery more than once lifts the with clause above ordinary inline subqueries. If we want to reproduce the above query using inline subqueries the solution would be longer and uglier. The with clause helps us with the complexity.
Indeed, taking advantage of the fact that you can also define multiple subqueries, you can tidy away a large amount of complexity, leaving your main select statement simple.
WITH emp_count AS (SELECT COUNT(*) num, deptno FROM emp GROUP BY deptno), avg_sal AS (SELECT AVG(sal) avgsal, deptno FROM emp GROUP BY deptno) SELECT dname department, loc location, num "number of employees", avgsal "average salary" FROM dept, emp_count, avg_sal WHERE dept.deptno = emp_count.deptno AND dept.deptno = avg_sal.deptno;
Oracle, as I said earlier, decides at runtime whether to process your subqueries as inline views or as temporary tables. However, you can nudge it in your preferred direction by using a couple of optimizer hints. The MATERIALIZE optimizer hint tells Oracle to treat your subquery as a temporary table; the INLINE hint, conversely, tells it to treat it as an inline view.
Personally, I try as much as possible not to fiddle with optimizer hints (I leave such black magic to our DBAs), however you may want to force Oracle to treat your subquery as a temporary table if it is complex and unwieldy.
I have seen WITH clause SELECT statements that stretch to many hundreds of lines. They mostly run like three-legged dogs, so no, I am not advocating them. It is in these situations that you may seriously consider optimizer hints.
With the release of 12C in 2014, the WITH clause learned a funky new trick: functions. That’s right, you can now create a function within the WITH clause and use it in the body of your main select statement. (Actually, the funky new trick isn’t really just functions; you can create procedures too, although I cannot see why you would want to, since you cannot call a procedure in a SELECT statement.)
Here’s the syntax:
WITH FUNCTION <function_name> RETURN <datatype> IS BEGIN <function body> END; SELECT <function_name> FROM <table> WHERE <where clause>;
Being eagle-eyed you probably noticed that semi-colon in the middle of the statement and assumed that it was a typo. Uh-uh, it’s not. Let me prove it with an example.
WITH FUNCTION get_avg(pDeptno NUMBER) RETURN NUMBER AS BEGIN FOR i IN (SELECT avg(sal) avgsal FROM emp WHERE deptno = pDeptno) LOOP RETURN i.avgsal; END LOOP; END; SELECT deptno, get_avg(deptno) FROM dept;
That’s nice, you might be thinking; but why don’t you just create a stand-alone function and use that instead? Isn’t this just showing off?
No, it’s not, actually. Firstly, there’s a neatness to a function that lives only for the duration that it is needed. (Because any function embedded within a WITH clause is only available to that statement.) Secondly, you may find that using inline functions in this way gives you a performance advantage over stand-alone or package functions.
Oh, and just in case you were wondering, you can use functions and subqueries in the same with clause.
WITH FUNCTION get_avg(pDeptno NUMBER) RETURN NUMBER AS BEGIN FOR i IN (SELECT avg(sal) avgsal FROM emp WHERE deptno = pDeptno) LOOP RETURN i.avgsal; END LOOP; END; emp_count AS (SELECT COUNT(*) num, deptno FROM emp GROUP BY deptno) SELECT dept.deptno, get_avg(dept.deptno) "average salary", emp_count.num "Employee count" FROM dept, emp_count WHERE dept.deptno = emp_count.deptno;
The previous example begins to lean towards the thing I like most about the WITH clause: the way you can stack subquery over function over subquery, like little building blocks, to create what I – and no one else – call a statement tower. I have not yet mentioned this, but in your WITH clause you can reference a subquery in subsequent subqueries.
What this means is that you could, conceivably, build a statement tower of inter-referencing subqueries, fine-tuning your data with each subsequent subquery, leaving your main SELECT statement pure and simple, and easy to read.
It’ll be difficult to demonstrate what I mean using just the EMP and DEPT tables, so let’s use a few dummy tables instead. Imagine we’re running some kind of a sales report in which we not only wish to list the sold items, but the price, a description, how much profit we’re making, and who made the sale. While the following example might not demonstrate the most efficient use of SQL, hopefully it gives you an idea of how you could build a statement tower, parsing your requirements with each block.
WITH FUNCTION get_price (pProduct_id number) RETURN NUMBER AS BEGIN FOR i IN (SELECT price FROM products WHERE product_id = pProduct_id) LOOP RETURN i.price; END LOOP; END; get_description AS (SELECT description, product_id FROM inventory), profit_or_loss AS (SELECT get_price(product_id) - cost_price p_or_l, product_id FROM stock), sales_assistant AS (SELECT staff_name, staff_id FROM staff) SELECT product, get_price(product_id) sale_price, profit_or_loss.p_or_l Profit, sales_assistant.staff_name FROM sales, get_description, profit_or_loss, sales_assistant WHERE sales.product_id = get_description.product_id AND sales.product_id = profit_or_loss.product_id AND sales.staff_id = sales_assistant.staff_id;
So that’s it. The with clause. Go ye into the world and use it.
Head on over to Part 2: The Hierarchical Query Clause.