DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports Events Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
What's in store for DevOps in 2023? Hear from the experts in our "DZone 2023 Preview: DevOps Edition" on Fri, Jan 27!
Save your seat
  1. DZone
  2. Data Engineering
  3. Databases
  4. Weird SQL Behavior? No. (And the Importance of Table Aliases)

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.

Steven Feuerstein user avatar by
Steven Feuerstein
·
Oct. 21, 16 · Tutorial
Like (7)
Save
Tweet
Share
6.05K Views

Join the DZone community and get the full member experience.

Join For Free

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:

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

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.

Database sql

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

Opinions expressed by DZone contributors are their own.

Popular on DZone

  • 2023 Software Testing Trends: A Look Ahead at the Industry's Future
  • Beginners’ Guide to Run a Linux Server Securely
  • Handling Virtual Threads
  • Getting Started With JMS-ActiveMQ: Explained in a Simple Way

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com
  • +1 (919) 678-0300

Let's be friends: