Tuesday 8 May 2007

The funny thing about SQL

I recieved a lession SQL logic the hard way, the other day.
Can you find the difference between the following two statements:
1)
select AY__ICODE, AY_UCODE, AY_SHNAM, AY_EXDES
from A_AYBOOK, A_ACTIVITY, A_MAYREQ
where MQ_IATTY = AY__ICODE
AND AK_AYCODE = AY__ICODE
and AK_BKCODE = 'IU010H01RNOL'
and MQ_IUNIT = 'SPEDA000001Y'
2)
Select count(A_ACTIVITY.AY__ICODE)
from A_AYBOOK, A_ACTIVITY, A_MAYREQ, A_SERPOIN
WHERE MQ_IATTY = AY__ICODE
and AK_AYCODE = AY__ICODE
and AK_BKCODE = 'IU010H01RNOL'
and MQ_IUNIT = 'SPEDA000001Y'
The from clause in the second statement contain a extra class A_SERPOIN. This makes the SQL return many more elements, because it interprets this as a inner join. Therefore read all your statement, before using.

No comments: