locked
What's different between exist and in RRS feed

  • Question

  • What's different between exist and  in
    • Edited by Ruby-Joe Wednesday, August 16, 2017 3:11 PM
    Wednesday, August 16, 2017 2:44 PM

Answers

  • What's different between exist and  in

    In is a collection operator.
    A in {a, c, d, s, d ....}

    "Tom" in (select sname from student)



    This (select sname from student) returns a collection of class names used to determine whether "David" is a data in this collection;
    At the same time, you can also use the exists statement:

    Exists (select * from student where sname = "David")

    The two kernels are similar, but because of the different optimization options, usually NOT EXISTS faster than NOT IN, because NOT EXISTS can be used to combine the algorithm and NOT IN to die, and EXISTS is not as fast as IN, because this time IN may use more algorithms in combination.

    select * from tableA where exists(select * from tableB where tableB.id=tableA.id) 

    This sentence is equivalent to

    select * from tableA where id in (select id from tableB) 

    For each of the data in table A,will "excute select * from table B where the tableB.id = tableA.id" existence judgment, if tableB exists in the same row as the current id, then exists is true, the line Display, otherwise it is not displayed

    Exits for small outside the big inquiries, in the inside for small outside the query.
    In determines whether the given value matches the value in the subquery or list.
    Exists specifies a subquery that detects the presence of rows.

    Compare queries using EXISTS and IN

    This example compares two semantic similar queries. The first query uses EXISTS and the second query uses IN. Note that both queries return the same information.

    USE pubs 
    GO 
    SELECT DISTINCT pub_name 
    FROM publishers 
    WHERE EXISTS 
    (SELECT * 
    FROM titles 
    WHERE pub_id = publishers.pub_id 
    AND type = 'business') 
    GO 
    -- Or, using the IN clause: 
    
    USE pubs 
    GO 
    SELECT distinct pub_name 
    FROM publishers 
    WHERE pub_id IN 
    (SELECT pub_id 
    FROM titles 
    WHERE type = 'business') 
    GO 


    • Edited by 電腦神手吳子陵 Wednesday, August 16, 2017 3:16 PM
    • Marked as answer by Ruby-Joe Wednesday, August 16, 2017 3:27 PM
    • Unmarked as answer by Ruby-Joe Wednesday, August 16, 2017 3:34 PM
    • Marked as answer by Ruby-Joe Wednesday, August 16, 2017 3:46 PM
    Wednesday, August 16, 2017 3:07 PM
  • Forgot say that!

    Exits is equivalent to the existence of quantifiers: that the existence of the collection, that is, the set is not empty only a set of roles. For example, exist P that P is not empty is true; not exist P that p is empty when true is a scalar and one yuan relationship relationship. For example: s in P is true when s is equal to a value in P; s not in P is true when each value of s and P is not equal.

    Hope can help you

    • Marked as answer by Ruby-Joe Wednesday, August 16, 2017 3:46 PM
    Wednesday, August 16, 2017 3:37 PM
  • Forgot say that!

    Exits is equivalent to the existence of quantifiers: that the existence of the collection, that is, the set is not empty only a set of roles. For example, exist P that P is not empty is true; not exist P that p is empty when true is a scalar and one yuan relationship relationship. For example: s in P is true when s is equal to a value in P; s not in P is true when each value of s and P is not equal.

    Hope can help you


    Thanks
    your welcome : )
    • Marked as answer by Ruby-Joe Wednesday, August 16, 2017 4:09 PM
    Wednesday, August 16, 2017 4:01 PM

All replies

  • What's different between exist and  in

    In is a collection operator.
    A in {a, c, d, s, d ....}

    "Tom" in (select sname from student)



    This (select sname from student) returns a collection of class names used to determine whether "David" is a data in this collection;
    At the same time, you can also use the exists statement:

    Exists (select * from student where sname = "David")

    The two kernels are similar, but because of the different optimization options, usually NOT EXISTS faster than NOT IN, because NOT EXISTS can be used to combine the algorithm and NOT IN to die, and EXISTS is not as fast as IN, because this time IN may use more algorithms in combination.

    select * from tableA where exists(select * from tableB where tableB.id=tableA.id) 

    This sentence is equivalent to

    select * from tableA where id in (select id from tableB) 

    For each of the data in table A,will "excute select * from table B where the tableB.id = tableA.id" existence judgment, if tableB exists in the same row as the current id, then exists is true, the line Display, otherwise it is not displayed

    Exits for small outside the big inquiries, in the inside for small outside the query.
    In determines whether the given value matches the value in the subquery or list.
    Exists specifies a subquery that detects the presence of rows.

    Compare queries using EXISTS and IN

    This example compares two semantic similar queries. The first query uses EXISTS and the second query uses IN. Note that both queries return the same information.

    USE pubs 
    GO 
    SELECT DISTINCT pub_name 
    FROM publishers 
    WHERE EXISTS 
    (SELECT * 
    FROM titles 
    WHERE pub_id = publishers.pub_id 
    AND type = 'business') 
    GO 
    -- Or, using the IN clause: 
    
    USE pubs 
    GO 
    SELECT distinct pub_name 
    FROM publishers 
    WHERE pub_id IN 
    (SELECT pub_id 
    FROM titles 
    WHERE type = 'business') 
    GO 


    • Edited by 電腦神手吳子陵 Wednesday, August 16, 2017 3:16 PM
    • Marked as answer by Ruby-Joe Wednesday, August 16, 2017 3:27 PM
    • Unmarked as answer by Ruby-Joe Wednesday, August 16, 2017 3:34 PM
    • Marked as answer by Ruby-Joe Wednesday, August 16, 2017 3:46 PM
    Wednesday, August 16, 2017 3:07 PM
  • Forgot say that!

    Exits is equivalent to the existence of quantifiers: that the existence of the collection, that is, the set is not empty only a set of roles. For example, exist P that P is not empty is true; not exist P that p is empty when true is a scalar and one yuan relationship relationship. For example: s in P is true when s is equal to a value in P; s not in P is true when each value of s and P is not equal.

    Hope can help you

    • Marked as answer by Ruby-Joe Wednesday, August 16, 2017 3:46 PM
    Wednesday, August 16, 2017 3:37 PM
  • Forgot say that!

    Exits is equivalent to the existence of quantifiers: that the existence of the collection, that is, the set is not empty only a set of roles. For example, exist P that P is not empty is true; not exist P that p is empty when true is a scalar and one yuan relationship relationship. For example: s in P is true when s is equal to a value in P; s not in P is true when each value of s and P is not equal.

    Hope can help you


    Thanks
    Wednesday, August 16, 2017 3:55 PM
  • Forgot say that!

    Exits is equivalent to the existence of quantifiers: that the existence of the collection, that is, the set is not empty only a set of roles. For example, exist P that P is not empty is true; not exist P that p is empty when true is a scalar and one yuan relationship relationship. For example: s in P is true when s is equal to a value in P; s not in P is true when each value of s and P is not equal.

    Hope can help you


    Thanks
    your welcome : )
    • Marked as answer by Ruby-Joe Wednesday, August 16, 2017 4:09 PM
    Wednesday, August 16, 2017 4:01 PM