What is the difference between predicate and table?
May 2, 2008
This issue is becoming a recurring theme of mine. This time it has been triggered by OTN exchange. To my surprise, people have difficulty accepting this seemingly obvious idea.
In SQL there is syntactic difference between a predicate and a table. However, let’s look beyond what you may have learned from syntax diagrams in Oracle SQL Reference (or other vendor for that matter). For our purposes let’s create a simple table Dept10:
create table Dept10 (
insert into Dept10 values (10);
Here is the question: is there a difference between this table and the predicate deptno = 10?
Sure there is: we can examine the content of Dept10 with a simple query:
select * from Dept10
and we have no means to “examine the content” of a predicate! However, let’s include the predicate deptno = 10 and the Dept10 table into a bigger query:
select dname from dept where deptno = 10
select dname from Dept natural join Dept10;
Now, there is no difference between the two! In fact, you can construct many more queries: for every query involving Dept10 we may compose equivalent query without Dept10 and vice versa, with the only exception being the early mentioned single table query:
select * from Dept10
Therefore, I propose to extend SQL to allow queries like this
select * where deptno = 10
One consequence of this amendment is that the famous Dual table is no longer required. The other implication of equating tables with predicates is that physical query optimization can be achieved in a simpler optimizer framework.
Some people suggested to go even further and just write
how this proposal fits? Let’s analyze this expression step by step. The legitimate SQL query which achieves this is
select 10 from Dual
where the Dual could be any table or view containing one row. Next, what is this constant 10 in the select clause? I’m asking because in relational algebra you can’t just take a projection operator and substitute a column with an arbitrary expression. We may think of this expression being evaluated for each row, but a better idea is that we invoke some function. In our case it happen to be a function taking an empty argument list and returning a constant
function return10 return integer is
Now the query transforms to
select return10() from dual
Next, every function is a relation (or predicate) that matches its input with output. Which predicate the function return10 corresponds to? Well, it must be deptno=10! Certainly function output is unnamed, so the predicate argument deptno can be anything. With the query output column being aliased
select return10() as deptno from dual
however, we can interpret row-wise function invocation as a join:
select deptno from dual natural join Dept10
Removing the redundant Dual we get
select deptno from Dept10
Once again, the Dept10 can be thought as a table which materializes the deptno = 10 predicate, and we established that there is unambiguous interpretation of the queries
select 10 as deptno