Saturday, May 14, 2005

Cinema Bizarre

As I turn on the TV, there is a wierd movie from ~1980 on, starring a totally suave, chain-smoking, dressed-to-the-nines Art Garfunkel.

This type of hallucination can only be explained by food poisoning...

Wednesday, May 11, 2005

Coding, Style, and Performance

At this point, my job seems to be (a) helping a group of developers implementing on-the-fly designs, and (b) writing design documents for some of our deeper issues. As part of this, I've been watching people write a fair amount of SQL. One of the great issues of the day seems to be surrounding join syntax in Oracle. Traditionally, an Oracle sql statement that joins emp and dept would look like:

SQL> select e.empno, e.ename, d.dname
2 from emp e, dept d
3 where e.deptno = d.deptno;

And, if there were employees who did not belong to a department, you would use an outer join:

SQL> select e.empno, e.ename, d.dname
2 from emp e, dept d
3* where e.deptno = d.deptno(+);

or, if you wanted to make sure to get any departments that had no employees, you would use a right outer join:

SQL>select e.empno, e.ename, d.dname
2 from emp e, dept d
3* where e.deptno(+) = d.deptno;

All well and good - easy examples, understandable code, what could be easier?
But -- and there's always a but -- when you start adding real logic to the where clause
(where ename like 'KIN%'), and perhaps add several more joins to the query, you have a festering mess, just waiting for a bug to appear.

Oracle 9 and above support ANSI join syntax, which removes the stuff from the where clause in its entirety (that's a good thing). More importantly, I think it helps people think through the questions they are asking, which can expose some of the bugs we typically see much earlier.

SQL> select empno, ename, dname
2* from emp inner join dept using (deptno);

SQL> select empno, ename, dname
2* from emp left outer join dept using (deptno);

SQL> select empno, ename, dname
2* from emp right outer join dept using (deptno);


SQL> select empno, ename, dname
2* from emp full outer join dept using (deptno);

Now I consider the new syntax to be a great improvement. It's much more clear which is the driving table, and you can put the logic in English:
Inner Join: "Fetch me the empno, empname, and corresponding dept name from these two lists; don't list anyone that doesn't belong to a department"
Left Outer Join: "Fetch me the empno, empname, and corresponding dept name if one exists from these two lists"
Right Outer Join: "Fetch me the empno, empname, and corresponding dept name if one exists from these two lists, and also list any dept names that have no employees"

It only gets better from here... but that will have to wait until tomorrow.