locked
EXISTS, NOT EXISTS Question RRS feed

  • Question

  • Can some please confirm if the following is true?:

    The inverse of this exists statement:

    EXISTS (SELECT 1 FROM TableU u WHERE c.Column1 = u.Email AND c.Column2 != c.Column2)

     

    Is:

     

    NOT EXISTS (SELECT 1 FROM TableU u WHERE c.Column1 = u.Email OR c.Column2 != c.Column2)

     

    Sure I read this somewhere and it seems to work with my data, not sure I really understand it though.


    BoroFC
    Wednesday, May 18, 2011 1:19 PM

Answers

  • Hi

     

    The inverse of this exists statement:

     

    Select *

    from

    TableT C

    Where

    EXISTS (SELECT 1 FROM TableU u WHERE c.Column1 = u.Email AND c.Column2 != c.Column2)

     

    Is:


     

    Select *

    from

    TableT C

    Where

    NOT EXISTS (SELECT 1 FROM TableU u WHERE c.Column1 = u.Email AND c.Column2 != c.Column2)

     

     

    Regards

     

    Abdul Azeem.

    • Marked as answer by ColSchmoll Wednesday, May 18, 2011 2:18 PM
    Wednesday, May 18, 2011 1:39 PM

All replies

  • I do not think so, since the expressions belonging to the correlated subquery are not affected by the inverse operation of the outer operator.

    May be you are confused by:

    ...
    WHERE (c1 = c2 and c3 = c4);

    ...
    WHERE NOT (c1 = c2 and c3 = c4); --> (c1 <> c2 or c3 <> c4)

    The same doesn't apply to the query inside the EXISTS operator, since we are negating the EXISTS and not the conditions that  the EXISTS operator is proving.

     


    AMB

    Some guidelines for posting questions...

    Wednesday, May 18, 2011 1:26 PM
  • What EXISTS does is it will return all the rows from outer query that has a matching row in the inner query based on your WHERE condition. So for

    EXISTS (SELECT 1 FROM TableU u WHERE c.Column1 = u.Email AND c.Column2 != c.Column2)

    This will check if rows exists in table c which has a matching row in tableU specified in your WHERE condition.

     

    On the other hand NOT EXISTS will return rows from outer query that does not have a matching row in the inner query based on your WHERE condition.

     

     


    Regards, Deven ----------------------------------------- Please vote if you find any of my post helpful.
    Wednesday, May 18, 2011 1:26 PM
  • No, you don't need to inverse AND operator to OR. If EXISTS translates directly to NOT EXISTS with the same condition if we want to reverse the statement.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Wednesday, May 18, 2011 1:30 PM
  • Hi

     

    The inverse of this exists statement:

     

    Select *

    from

    TableT C

    Where

    EXISTS (SELECT 1 FROM TableU u WHERE c.Column1 = u.Email AND c.Column2 != c.Column2)

     

    Is:


     

    Select *

    from

    TableT C

    Where

    NOT EXISTS (SELECT 1 FROM TableU u WHERE c.Column1 = u.Email AND c.Column2 != c.Column2)

     

     

    Regards

     

    Abdul Azeem.

    • Marked as answer by ColSchmoll Wednesday, May 18, 2011 2:18 PM
    Wednesday, May 18, 2011 1:39 PM
  • Okay thanks.  So then the inverse of:

    EXISTS (SELECT 1 FROM TableU u WHERE c.Column1 = u.Email AND c.Column2 != c.Column2)

    Is simply:

    NOT EXISTS (SELECT 1 FROM TableU u WHERE c.Column1 = u.Email AND c.Column2 != c.Column2)

    ???


    BoroFC
    Wednesday, May 18, 2011 1:55 PM