Union woes

Silly Me!

I always thought a Union should and would accept a Select clause that is exactly identical w.r.t number of columns, data types and column names. i.e if I want to say display the list of all employees who are in Salary Grade 'A' and the list of employees who are in HR Department in the same result set (for some strange reason) I was expecting this to be the correct query:

SELECT
E.EMPLOYEE_NAME,
S.SAL_GRADE AS GRADE_DEPT
FROM EMPLOYEE E , SALARY_GRADE S
WHERE E.SAL > S.LOW_SAL
AND E.SAL < S.HIGH_SAL
AND S.SAL_GRADE = 'A'

UNION

SELECT
E.EMPLOYEE_NAME,
D.DEPT_NAME AS GRADE_DEPT
FROM EMPLOYEE E, DEPARTMENT D
WHERE E.DEPT_CODE = D.DEPT_CODE
AND D.DEPT_CODE = 'HR'


But apparently my assumption made using my "profound knowledge" was wrong. All a Union requires is for the number of columns and the data type of the columns in the Select list to be the same (refer Wiki). So even if I interchange the columns in the second query above, the query will still run and give results, but not necessarily the results you would want!

I learn this the hard way after discovering a stange bug in one of the reports that I had developed. After looking into the SQL I realized that I had interchanged the column names in one of the SQLs in the Union. The SQL still worked fine because each query was still having equal number of columns and same data types for each column!

Felt like pulling my hair out after discovering the bug!

Correlated Update : A tricky Situation

Hi Folks,

Long time no see, any post.
Last day I saw a bewildering issue. It may not strike some of you if you already know it.
But i thought this should be shared.

In a correlated update :
eg:
UPDATE MASTER_ORDERS X
SET QTY=(SELECT Y.QTY
FROM ORDERS Y
WHERE X.ORDER_NUM = Y.ORDER_NUM);


We know one thing ; that is it will scan the whole table and consider each row for updating.
But in this case if the inner query returns not result, still the update will complete. It will update even the row for which the inner query did not give any data with NULL. Some kind of force update .Hence resulting in loss of data.
Tom Kyte says this not the right way to do such an update.

To avoid this there are two methods:

- Use an updatable join (updatable view), if the row to be updated is key preserved (i.e; only one row is fetched for the join condition).

- Use an exists clause in the outer where condition, so that only records with change are picked for update. It also makes the query faster as the update is done on a smaller subset.

UPDATE MASTER_ORDERS X
SET QTY=(SELECT Y.QTY
FROM ORDERS Y
WHERE X.ORDER_NUM = Y.ORDER_NUM)
WHERE EXISTS (SELECT 1
FROM ORDERS Y
WHERE X.ORDER_NUM = Y.ORDER_NUM);