Back in December 2012 I blogged about a range of problems that can occur if columns referenced in queries are not qualified with the name or alias of the table that they belong to. This post is mostly a rehash of just one of those scenarios as it seems to crop up time and time again…
Suppose you are presented with the following:
DELETE FROM emp WHERE dept_id IN (SELECT dept_id FROM dept WHERE dept_name = 'Dept_5')
You would look at the above statement and think that it will do something along the lines of “delete the entries in EMP where they relate to a DEPT entry with the name of ‘Dept_5′”. You might be right… but if the tables were defined as follows you would be wrong:
CREATE TABLE dept (id NUMBER (4) NOT NULL ,dept_name VARCHAR2 (30) NOT NULL) / ALTER TABLE dept ADD CONSTRAINT dept_pk PRIMARY KEY (id) / CREATE TABLE emp (id NUMBER (4) NOT NULL ,first_name VARCHAR2 (30) NOT NULL ,last_name VARCHAR2 (30) NOT NULL ,dept_id NUMBER (4) NOT NULL) / ALTER TABLE emp ADD CONSTRAINT emp_pk PRIMARY KEY (id) / ALTER TABLE emp ADD CONSTRAINT emp_fk1 FOREIGN KEY (dept_id) REFERENCES dept (id) /
If we were to populate these tables with 10 DEPT entries and 100 EMP entries and run our delete:
SQL>INSERT INTO dept 2 SELECT ROWNUM 3 , 'Dept_' || TO_CHAR(ROWNUM) 4 FROM dual 5 CONNECT BY ROWNUM <= 10 6 / 10 rows created. SQL>INSERT INTO emp 2 SELECT ROWNUM 3 , 'E_' || TO_CHAR(ROWNUM) 4 , 'Employee_' || TO_CHAR(ROWNUM) 5 , MOD(ROWNUM,10)+1 6 FROM dual 7 CONNECT BY ROWNUM <= 100 8 / 100 rows created. SQL>COMMIT 2 / Commit complete. SQL>DELETE 2 FROM emp 3 WHERE dept_id IN (SELECT dept_id 4 FROM dept 5 WHERE dept_name = 'Dept_5') 6 / 100 rows deleted. SQL>SELECT * 2 FROM emp 3 / no rows selected
Ummm… where’s my data gone?
Let’s rewrite the query to show what really happened:
DELETE FROM emp e WHERE e.dept_id IN (SELECT e.dept_id FROM dept d WHERE d.dept_name = 'Dept_5')
Now we can see what went wrong. The DEPT tables does not contain a column named DEPT_ID so Oracle has referenced the column in the EMP table. It can do this as identifiers in an outer query are visible inside a sub-query. Hence when Oracle is unable to find an identifier local to the sub-query then it will expand its search to the outer query.
Our delete query therefore will delete any EMP entry where the DEPT_ID is not null if there exists an entry in DEPT with a DEPT_NAME of ‘Dept_5’. Since DEPT_ID is defined as NOT NULL and there does exist a DEPT entry named ‘Dept_5’ we end up deleting all entries from EMP. A rather nasty outcome.
The best way to prevent this type of a problem is to always qualify column names with the table name or table alias. If we had done that with our DELETE query we would have got the following:
SQL>DELETE 2 FROM emp e 3 WHERE e.dept_id IN (SELECT d.dept_id 4 FROM dept d 5 WHERE d.dept_name = 'Dept_5') 6 / WHERE e.dept_id IN (SELECT d.dept_id * ERROR at line 3: ORA-00904: "D"."DEPT_ID": invalid identifier
Our qualified reference would have resulted in an error. Even better, if the DELETE was included as part of a package then the package would have thrown a compilation error.
Echoing my advice from the previous post: always use qualified references in your queries. The effort is negligible so there’s no excuse not to.