To mix the last post with something that may be easier to digest, I’ll make this one about one of my favorite keywords in SQL. It is the WITH clause. I can only think of one bad thing with it; it’s name. It’s virtually impossible to search for information on something with a name that results in over 4 billion hits on a Google search.

One reason for why I like it is for how I learned about it’s existence. It was a few years ago and my manager at the time found it during leisure reading about Oracle and asked me what I thought about it. I probably answered something stupid like I’m not sure that feature even exists. We broke out the book and read about it, and I knew this was going to be an important feature for some problems. It was one of those things where you know that you have no immediate need for it, but that you have a new tool to solve problems with and it will come handy soon enough. Every DBA should be so lucky as to have a group manager that cares enough about technology that they read about it on their spare time.

Lets leave memory lane and return to reality so we can look a little on how this feature works. The technical name for it is “subquery refactoring”, but I’ve never heard anyone refer to it as anything other than “the with clause”.

It allows you to create one or more result tables you can refer to in the real select clause. Inline views can sometimes be used to achieve the same result, but the really cool thing with this feature is that you can refer to it as a table and refer to it in more than one place in your query.

Lets consider a situation where you have one table with products and one with services. Both of these tables have a category id field and that links to a category table. Lets assume that you want to retrive only the products and services that are in priority 1 categories. This is easy to do with normal SQL, but WITH can probably make it more effective and easier to maintain.

Let’s first create the user and the tables we’ll use in the test cases. Log on with a DBA user and execute the following commands.

drop user withtest cascade;
create user withtest identified by withtest;
alter user withtest quota unlimited on users;
grant create session, resource to withtest;

conn withtest/withtest
create table products (prod_id     number(2)    not null
                      ,prod_name   varchar2(20) not null
                      ,category_id number(2)    not null);

insert into products (prod_id, prod_name, category_id) values(10,'Chair',20);
insert into products (prod_id, prod_name, category_id) values(11,'Sofa',21);
insert into products (prod_id, prod_name, category_id) values(12,'Bench',21);
insert into products (prod_id, prod_name, category_id) values(13,'Stool',22);
insert into products (prod_id, prod_name, category_id) values(14,'Armchair',21);
insert into products (prod_id, prod_name, category_id) values(15,'Ottoman',20);

create table services (serv_id     number(2)    not null
                      ,serv_name   varchar2(20) not null
                      ,category_id number(2)    not null);

insert into services (serv_id, serv_name, category_id) values(30,'Watering',20);
insert into services (serv_id, serv_name, category_id) values(31,'Cooking',21);
insert into services (serv_id, serv_name, category_id) values(32,'Cleaning',21);
insert into services (serv_id, serv_name, category_id) values(33,'Shopping',22);
insert into services (serv_id, serv_name, category_id) values(34,'Washing',21);
insert into services (serv_id, serv_name, category_id) values(35,'Decorating',20);

create table categories (category_id   number(2)    not null
                        ,category_name varchar2(20) not null
                        ,priority      number(1)    not null);

insert into categories (category_id, category_name, priority) values(20,'Unimportant',3);
insert into categories (category_id, category_name, priority) values(21,'Very Important',1);
insert into categories (category_id, category_name, priority) values(22,'A little important',2);

commit;

We now have tables and data to allow us to write the SQL we normally would have used.

select prod_name
  from products a inner join categories b
    on a.category_id = b.category_id
   and b.priority = 1
union all
select serv_name
  from services a inner join categories b
    on a.category_id = b.category_id
   and b.priority = 1;

Editorial note: Hmmm, I wonder where in the time since this was written the rest went.