The exists usage

  • exists:
    • If the parentheses subquery does not return a null result, the WHERE condition is true and the main SQL statement is executed
    • If the parentheses subquery returns a null result, the WHERE condition is not true and the main SQL statement is not executed
  • not exists:In contrast to the exists
    • If the result of the subquery statement in parentheses is empty, the WHERE condition is true and the main SQL statement is executed
    • If the result of the subquery statement in parentheses is not empty, it indicateswhereIf the condition is not true, the Lord will not be executedSQLstatements
      • Differences between exists and in:
        • In can return only one field value
        • Does exists emphasize whether a result set is returned. Does exists allow multiple fields to be returned
      • existsIs generally better thanin:
        • Using EXISTS,Oracle first checks the primary query and then runs the sub-query until the first match is found
        • With the IN subquery, the subquery is first executed and the resulting list is placed in an indexed temporary table
      • existsIs more efficient thandistinct:
        • You can avoid the use of DISTINCT in select when submitting queries for one-to-many table information
        • Because the RDBMS core module will return the results as soon as the conditions of the subquery are satisfied, it is self-deprecating. The following two sets of SQL statements are equivalent:
        SELECT distinct dept_no, dept_name from dept D, EMP E WHERE D.dept_no = E.dept_no;
Copy the code
        SELECT dept_no, dept_name from dept D WHERE EXISTS (SELECT 1 from emp E WHERE E.dept_no = D.dept_no);
Copy the code
      • Exists applies to the case where the external result set is small
      • In is suitable for situations where both the inside and outside are large

Use the sample

  • exists:
SELECT ID,NAME FROM A WHERE 
EXISTS(SELECT * FROM B WHERE A.ID = B.AID)
Copy the code
  • SQL statement decomposition:
SELECT ID,NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE B.AID=1) 
SELECT * FROM B WHERE b.id =1 SELECT * FROM B WHERE b.id =1

SELECT ID,NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE B.AID=2) 
SELECT * FROM B WHERE b.id =2 SELECT * FROM B WHERE b.id =2

SELECT ID,NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE B.AID=3) 
-->SELECT * FROM B WHERE b.id =3
Copy the code
  • The SQL statement above is equivalent to:
SELECT id, name from A WHERE id in (select aid from B)
Copy the code

conclusion

  • SQL in, not in, exists, not exists
    • in:
      • Determines whether a given value matches a value in a subquery or list
      • The in keyword selects the row that matches any of the values in the list
      • Items following the in keyword must be separated by commas and enclosed in parentheses
    • not in:
      • Subqueries introduced with the not in keyword also return a list of zero or more values
    • exists:
      • Specifies a subquery that checks for the existence of rows
      • It’s the intersection of two sets
      • The value of “exists” can be a whole statement, and that of “in” can be a single column statement
    • not exists:
      • It’s the difference set of two sets
    • existsandnot existsThe result type returned isBoolean:
      • If the subquery contains rows:
        • The exists returns TRUE
        • Not exists Returns FALSE