In Oracle SQL statements it is not allowed to use reserved words as identifiers. For example, it would confuse the SQL parser – and us as well – to name a table as TABLE or a column as SELECT. Of course, it is possible to use such names when the name is written in double quotes, but a statement like CREATE TABLE “TABLE” is definitely not a good practice.
A list of reserved words can be found in the appendix of the Oracle SQL Reference manual. A few days ago I used this list for the first time since many years and was surprised to see that all the keywords of the ANSI join syntax are missing on this list. Were they just forgotten in the documentation, or is it allowed to use the words LEFT, RIGHT, INNER, OUTER, FULL and JOIN as identifiers?
Although this question is not really relevant for real projects, I wanted to know how the SQL parser of Oracle 11.2 handles these keywords. Train journeys are always a good chance to do such things, and at the moment I’m traveling from Munich to Zurich by train and have time to do some tests with SQL. I’m a bit surprised that a statement like this is not only syntactically correct, but also returns the right result:
SELECT * FROM LEFT JOIN RIGHT ON LEFT.OUTER = RIGHT.JOIN
LEFT JOIN OUTER ON OUTER.JOIN = LEFT.RIGHT
You agree that this statement is a bit hard to read, but it seems that the SQL parser can handle it without problems. When we reformat the statement as shown in the test case below, it looks at least more readable, but still confusing. If you want to check whether this really works, here my test case:
-- Original statment:
JOIN dept ON emp.deptno = dept.deptno
LEFT JOIN bonus ON bonus.ename = emp.ename;
-- Prepare test case:
CREATE TABLE LEFT AS SELECT * FROM emp;
CREATE TABLE RIGHT AS SELECT * FROM dept;
CREATE TABLE OUTER AS SELECT * FROM bonus;
ALTER TABLE LEFT RENAME COLUMN deptno TO OUTER;
ALTER TABLE RIGHT RENAME COLUMN deptno TO JOIN;
ALTER TABLE OUTER RENAME COLUMN ename TO JOIN;
ALTER TABLE LEFT RENAME COLUMN ename TO RIGHT;
-- Statement for test case:
JOIN RIGHT ON LEFT.OUTER = RIGHT.JOIN
LEFT JOIN OUTER ON OUTER.JOIN = LEFT.RIGHT;
And what did I learn from this test case? First, SQL still contains some surprises, although I thought I know this query language since years. Second, I should avoid long train journeys – otherwise I will have more silly ideas for tests like this.