Hi all, how are you?
I'm Jaime from Spain and I write here because I do not understand why 2 queries that, in my opinion, should return the same result do not return the same. I will expose the case:
1.- If I use the next query:
select a.business\_email, a.status from
(
SELECT USER\_EMPLOYEE\_REF.BUSINESS\_EMAIL, user\_employee\_ref.status
FROM SWITCH\_PERMITTED, USER\_EMPLOYEE\_REF
WHERE USER\_EMPLOYEE\_REF.switch\_date is null
and (SWITCH\_PERMITTED.EMAIL1= USER\_EMPLOYEE\_REF.BUSINESS\_EMAIL or SWITCH\_PERMITTED.EMAIL2= USER\_EMPLOYEE\_REF.BUSINESS\_EMAIL
or SWITCH\_PERMITTED.EMAIL3= USER\_EMPLOYEE\_REF.BUSINESS\_EMAIL)
) a
I obtain the next result:
BUSINESS\_EMAIL STATUS
"[JAMES.CORTES@a.COM](mailto:JAMES.CORTES@TELEFONICA.COM)" "INACTIVE"
"[JAMES.CORTES@a.COM](mailto:JAMES.CORTES@TELEFONICA.COM)" "INACTIVE"
"[JOSE.CORONEL@a.COM](mailto:JOSE.CORONEL@TELEFONICA.COM)" "ACTIVE"
"[JOSE.CORONEL@a.COM](mailto:JOSE.CORONEL@TELEFONICA.COM)" "INACTIVE"
"[JOSE.CORONEL@a.COM](mailto:JOSE.CORONEL@TELEFONICA.COM)" "ACTIVE"
2.- From this set of rows, I want to obtain the rows of the email that it is always INACTIVE. With that purpouse, I create the next query BUT IT RETURNS 0 rows (WHY????):
select business\_email, status from
(
SELECT USER\_EMPLOYEE\_REF.BUSINESS\_EMAIL, user\_employee\_ref.status
FROM SWITCH\_PERMITTED, USER\_EMPLOYEE\_REF
WHERE USER\_EMPLOYEE\_REF.switch\_date is null
and (SWITCH\_PERMITTED.EMAIL1= USER\_EMPLOYEE\_REF.BUSINESS\_EMAIL or SWITCH\_PERMITTED.EMAIL2= USER\_EMPLOYEE\_REF.BUSINESS\_EMAIL
or SWITCH\_PERMITTED.EMAIL3= USER\_EMPLOYEE\_REF.BUSINESS\_EMAIL)
)a
WHERE business\_email not in (
select business\_email from USER\_EMPLOYEE\_REF where STATUS='ACTIVE'
)
but if I add one extra condition it returns me the correct result (2 rows, the ones of JAMES):
select a.business\_email, a.status from
(
SELECT USER\_EMPLOYEE\_REF.BUSINESS\_EMAIL, user\_employee\_ref.status
FROM SWITCH\_PERMITTED, USER\_EMPLOYEE\_REF
WHERE USER\_EMPLOYEE\_REF.switch\_date is null
and (SWITCH\_PERMITTED.EMAIL1= USER\_EMPLOYEE\_REF.BUSINESS\_EMAIL or SWITCH\_PERMITTED.EMAIL2= USER\_EMPLOYEE\_REF.BUSINESS\_EMAIL
or SWITCH\_PERMITTED.EMAIL3= USER\_EMPLOYEE\_REF.BUSINESS\_EMAIL)
) a
WHERE a.business\_email not in (
select business\_email from USER\_EMPLOYEE\_REF where STATUS='ACTIVE' and **business\_email=a.business\_email**
)
Does anyone know why this is happening? To have more knowledge I can tell that USER_EMPLOYEE_REF has 109044 rows (and only 20 are INACTIVE), the SWITCH_PERMITTED has 3 rows and the query plans are:
Many thanks in advance and sorry for the inconveniences.