Over a million developers have joined DZone.

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

Got complex SQL statements? Well, the compiler is going to handle them one way or another. Here's how to keep them tidy so you don't do something like delete all the rows in a table.

· Database Zone

Build fast, scale big with MongoDB Atlas, a hosted service for the leading NoSQL database. Try it now! Brought to you in partnership with MongoDB.

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 home clearly.

I create two tables:

   house_id     INTEGER PRIMARY KEY,
   house_name   VARCHAR2 (100),
   address      VARCHAR2 (1000)

   room_id     INTEGER PRIMARY KEY,
   house_id    INTEGER,
   room_name   VARCHAR2 (100),
   CONSTRAINT rooms_house FOREIGN KEY (house_id) REFERENCES houses (house_id)

Then I populate them with data:

   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);


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?

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

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

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

Note: Because 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:


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:

    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:

    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.

Now it's easier than ever to get started with MongoDB, the database that allows startups and enterprises alike to rapidly build planet-scale apps. Introducing MongoDB Atlas, the official hosted service for the database on AWS. Try it now! Brought to you in partnership with MongoDB.

pl/sql ,sql ,oracle database ,database

Published at DZone with permission of Steven Feuerstein. See the original article here.

Opinions expressed by DZone contributors are their own.

The best of DZone straight to your inbox.

Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}