exists and not exists usages in Oracle __oracle

EXISTS (SQL returns the result set to true) not EXISTS (SQL does not return the result set to True) is as follows: Table A ID NAME1A12A23A3 Table B ID AID NAME1    1B12    2B23    2B3 table A and table B are 1 pairs of relationships a.id => B.aid Select Id,name from A where EXIST (SELECT * from B WHERE a.id=b.aid) execution results are1A12A2 causes can be analyzed as follows select Id,name from A where EXISTS (SELECT * from B where b.aid=1--->select * from B where b.aid=1 has a value to return true so there is a data select Id,name from A where EXISTS (select * from B where b.aid=2--->select * from B where b.aid=2 has a value to return true so there is a data select Id,name from A where EXISTS (select * from B where b.aid=3--->select * from B where b.aid=3 no value returns true so there is no data not EXISTS is the reverse select Id,name from A where not EXIST (SELECT * FRO M B WHERE a.id=b.aid) execution results are3A3 =========================================================================== EXISTS = in, meaning the same but there is a little difference in grammar, As if using in efficiency to be almost, should not be the reason for the index SELECT id,name from A WHERE ID in (select AID from B) is not EXISTS = no in, meaning the same but there is a little difference in syntax SE Lect Id,name from the WHERE ID not in (SELECT AID from B) is a common usage: SQL In,not in,exists,not EXISTS usage and difference: in: determining a given value 
　　Matches the values in the subquery or list. 
　　The In keyword allows you to select a row that matches any of the values in the list. When you want to obtain a list of the names and states of all authors residing in California, Indiana, or Maryland states, you need the following query: SELECT ProductID, ProductName from Northwind.dbo.Product s WHERE CategoryID =1OR CategoryID =4OR CategoryID =5However, if you use in, you can get the same result by typing fewer characters: SELECT ProductID, ProductName from Northwind.dbo.Products WHERE CategoryID in (1,4,5The items after the In keyword must be separated by commas and enclosed in parentheses. The following query finds in the titleauthor table that the royalty received in either book is less than- M% of all authors, and then select from the authors table the names of all authors that match au_id titleauthor query results: Select au_lname, au_fname from authors WHERE au_ au_id ID in (SELECT au_id from titleauthor WHERE Royaltyper <- MThe results show that some authors belong to less than- M% of the class. 
　　Not in: subqueries introduced through the NOT in keyword also return a column of 0 or more values. 
　　The following query finds the name of a publisher who has not published a business book.  Select Pub_name from Publishers where pub_id (select pub_id from titles where type = ' business ') uses EXISTS and not 
      The EXISTS introduced in this paper can be used in the operation of two sets of principles: intersection and Difference sets. 
　　The intersection of two sets contains all elements that belong to two original collections at the same time. 
　　The difference set contains elements that belong only to the first collection in two collections. 
　　EXISTS: Specifies a subquery that detects the existence of a row. This example shows a query that finds titles published by any publisher in a city that starts with the letter B: Select DISTINCT pub_name from Publishers where EXISTS (SELECT * FROM titles where pub_id = publishers.pub_id and type = ' business ') select distinct pub_name from Publishers WHERE pub_id in (SELECT p UB_ID from titles WHERE type = ' business ') the difference between the two: EXISTS: The following can be the whole sentence of the query statement such as: SELECT * FROM titles in: The following can only be a single column: Select Pu b_id from Title not EXISTS: for example, to find the name of a publisher that does not publish a business book: Select Pub_name from Publishers WHERE not EXISTS (SELECT * FRO M titles WHERE pub_id = publishers.pub_id and type = ' business ') The following query finds the name of a book that has not been sold: SELECT title from titles Wher E not EXISTS (SELECT title_id from sales WHERE title_id = Titles.titLE_ID) Syntax EXISTS subquery parameter subquery: is a restricted SELECT statement (no COMPUTE clause and into keyword is allowed). 

For more information, see the discussion of subqueries in SELECT. 


Result type: Boolean result value: Returns TRUE if the subquery contains rows. 

Example A. Using NULL in a subquery still returns the result set the example specifies NULL in the subquery and returns the result set, which is TRUE by using the EXISTS still value. Use Northwind go SELECT categoryname from Categories WHERE EXISTS (SELECT NULL) Order by CategoryName ASC go B. Compare Using EXISTS and in queries This example compares two semantically similar queries. The first query uses EXISTS and the second query uses in. 

Note that two queries return the same information. Use the 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 to (SELECT pub_id from titles WHERE type = \ ' Business\ ') The following is the result set of any query                    
: pub_name----------------------------------------Algodata Infosystems New Moon Books C. Compare the use of EXISTS and = ANY Query This example shows two ways to find authors who live in the same city as the Publisher: The first method uses = Any, and the second method uses EXISTS. 

Note that both of these methods return the same information. Use the pubs go SELECT au_lname, au_fname from authors where exists (SELECT * from publishers where authors . City = publishers.city] Go