Weird SQL Behavior? No. And the Importance of Table Aliases.

I received this email yesterday with a question about "weird SQL behavior".

I wrote a SQL delete statement with a select statement in its where clause. I made a mistake and forgot to create a column in the table that I used in the subquery. But the table from which I am deleting has a column with the same name. I did not get an error on compilation. Why not? There is no column with this name in this table in the where-clause. As a result I deleted all the rows in the table. 

That last sentence - "I deleted all the rows in the table." - has got to be one of the worst things you ever say to yourself as an Oracle Database developer. Well, OK, there are worse, like "I truncated a table in production accidentally". Still, that's pretty bad.

So is that "weird" SQL behavior? Should the DELETE have failed to compile? Answers: No and No. Let's take a look at an example to drive the point him clearly.

I create two tables:

CREATE TABLE houses
(
   house_id     INTEGER PRIMARY KEY,
   house_name   VARCHAR2 (100),
   address      VARCHAR2 (1000)
)
/

CREATE TABLE rooms
(
   room_id     INTEGER PRIMARY KEY,
   house_id    INTEGER,
   room_name   VARCHAR2 (100),
   FLOOR       INTEGER,
   CONSTRAINT rooms_house FOREIGN KEY (house_id) REFERENCES houses (house_id)
)
/

Then I populate them with data:

BEGIN
   INSERT INTO houses
        VALUES (1, 'Castle Feuerstein', 'Rogers Park, Chicago');

   INSERT INTO rooms
        VALUES (100, 1, 'Kitchen', 1);

   INSERT INTO rooms
        VALUES (200, 1, 'Bedroom', 2);

   COMMIT;
END;
/

OK, time to delete. I write the block below. Notice that my subquery selects the room_id from the houses table. There is no room_id column in houses, so the DELETE should fail to compile, right?

BEGIN
   DELETE FROM rooms
         WHERE room_id = (SELECT room_id FROM houses);

   DBMS_OUTPUT.put_line ('Deleted = ' || SQL%ROWCOUNT);
END;
/

Wrong! Instead, I see Deleted = 2. All the rows in the rooms table deleted. That's some pretty weird SQL, right? Wrong again!

Note: since there are no PL/SQL bind variables in the SQL statement, we don't need to talk at all about name capture in PL/SQL, but you should also be clear about that as well, so here's a link to the doc).

When the SQL engine parses this statement, it needs to resolve all references to identifiers. It does so within the scope of that DELETE statement. But wait, that DELETE statement has within it a sub-query.

So here's how it goes:

1. Does houses have a room_id column?
2. No. OK, does rooms have a room_id column?
3. Yes, so use that.
4. OK, well that essentially leaves us with "room_id = room_id"
5. All rows deleted.

It's easy to verify this flow. Let's add a column named "ROOM_ID" to houses:

ALTER TABLE houses ADD room_id INTEGER
/

Now, when I try to execute that same block of code that performs the delete, I then see Deleted = 0.

No rows were deleted, and that's because the value of houses.room_id is NULL in every row in the table.

The developer who sent me this email was confused and naturally thought that maybe there was something wrong or weird with SQL.

Now, don't get me wrong: Oracle SQL surely has its share of bugs. But I think that after 35 years, you can pretty well assume that for any basic, common statements, the language is pretty solid. So if you get confused about the result of a SQL statement you should:

First, make sure you understand how the language works.

Second, fully qualify all references inside your SQL statement.

Writing a SQL statement like this:

DELETE FROM rooms
 WHERE room_id = (SELECT room_id FROM houses);

Is akin to writing an arithmetic expression like this:

var := 12 * 15/ 3 - 27 + 100;

Believe this: the compiler NEVER GETS CONFUSED by code like this. Only us humans.

So with arithmetic expressions, you should always use parentheses to make your intent clear (and maybe fix a bug or two, as my parentheses do, below):

var := ((12 * 15) / 3) - (27 + 100);

and always fully qualify references to columns in your SQL statements, using table aliases, as in:

DELETE FROM rooms r
 WHERE r.room_id = (SELECT h.room_id FROM houses h);

This very simple step not only removes confusion, but also makes it much easier for developers "down the line" to maintain your complex SQL statements. It also reduces the chances for bugs to creep into said SQL statements.