Topic
  • 1 reply
  • Latest Post - ‏2013-10-18T17:43:04Z by DiegoTurriaga
DiegoTurriaga
DiegoTurriaga
2 Posts

Pinned topic [SELECT WHERE NOT IN] not expected returns... why?

‏2013-10-18T17:28:42Z |

Check this out:

SELECT *
FROM sga_equiv_resol z
WHERE z.nro_resolucion = 'RCS 0102/2004';
-- 1 row (PK nro_resolucion)

SELECT *
FROM sga_equiv_operac x
WHERE x.nro_resolucion = 'RCS 0102/2004';
-- 4 rows (FK nro_resolucion) 

SELECT *
FROM sga_eqdef_matrices y
WHERE y.nro_resolucion = 'RCS 0102/2004';
-- 0 rows (FK nro_resolucion)

SELECT *
FROM sga_equiv_operac x
WHERE NOT EXISTS (
SELECT DISTINCT y.nro_resolucion
FROM sga_eqdef_matrices y
WHERE x.nro_resolucion=y.nro_resolucion
);
-- 4101 rows

SELECT *
FROM sga_equiv_operac x
LEFT JOIN sga_eqdef_matrices y
ON x.nro_resolucion=y.nro_resolucion
WHERE y.nro_resolucion IS NULL;
-- 4101 rows 

SELECT *
FROM sga_equiv_operac x
WHERE x.nro_resolucion NOT IN (
SELECT DISTINCT y.nro_resolucion
FROM sga_eqdef_matrices y
);
-- 0 rows


I'm using IDS 11.5
Some known bug?

  • DiegoTurriaga
    DiegoTurriaga
    2 Posts

    Re: [SELECT WHERE NOT IN] not expected returns... why?

    ‏2013-10-18T17:43:04Z  
    SELECT * FROM sga_equiv_operac x WHERE NOT EXISTS (SELECT DISTINCT y.nro_resolucion FROM sga_eqdef_matrices y WHERE x.nro_resolucion=y.nro_resolucion) and x.nro_resolucion = 'RCS 0102/2004';
    -- 4 rows

     

    SELECT * FROM sga_equiv_operac x LEFT JOIN sga_eqdef_matrices y ON x.nro_resolucion=y.nro_resolucion WHERE y.nro_resolucion IS NULL and x.nro_resolucion = 'RCS 0102/2004';

    -- 4 rows 

     

    SELECT * FROM sga_equiv_operac x WHERE x.nro_resolucion NOT IN (SELECT DISTINCT y.nro_resolucion FROM sga_eqdef_matrices y) and x.nro_resolucion = 'RCS 0102/2004';

    -- 0 rows