SoFunction
Updated on 2025-03-05

Exams of the usage of exists and not exists in oracle

exists (sql returns the result set as true)
not exists (sql does not return the result set as true)
exists means the same as in, the syntax is different, and the efficiency is higher than in
not exists and not in meaning the same meaning, different syntax, and higher efficiency than in

Basic concepts:

select * from A where not exists(select * from B where  = );
select * from A where exists(select * from B where  = );

1. First execute the external query select * from A, and then take out a piece of data from the external query and pass it to the internal query.

2. Execute select * from B in the inner query. The data passed in the outer query and the data obtained in the inner query are matched according to the conditions after where. If the data meets =, it will return true, and if none of them is satisfied, it will return false.

3. If the inner query returns true, the data of the outer query is retained. If the inner query returns false, the data of the outer query is not displayed. All data in the outer query are line by line query and matched.

Note: The execution order of exists or not exists is to execute external queries first and then internal queries. This conflicts with the subquery concept we learned.

Give an example

as follows:

Table A
ID NAME
1 A1
2 A2
3 A3

Table B
ID AID NAME
1 1 B1
2 2 B2
3 2 B3

Table A and Table B are 1-to-many relationship =>

SELECT ID,NAME FROM A WHERE EXIST (SELECT * FROM B WHERE =)

The execution result is

1 A1
2 A2

The reasons can be analyzed as follows

SELECT ID,NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE =１) 
--->SELECT * FROM B WHERE =１There is a value to return true so there is data 

SELECT ID,NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE =2) 
--->SELECT * FROM B WHERE =２There is a value to return true so there is data 

SELECT ID,NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE =3) 
--->SELECT * FROM B WHERE =３No value returns true so no data

NOT EXISTS is the other way around

SELECT ID,NAME FROM A WHERE　NOT EXIST (SELECT * FROM B WHERE =)

The execution result is 3 A

===========================================================================

EXISTS = IN,The same meaning, but the grammatical difference，It seems to be usedINEfficiency is a little worse，It should be the reason why the index will not be executed 
SELECT ID,NAME FROM A　 WHERE　ID IN (SELECT AID FROM B) 

NOT EXISTS = NOT IN ,The same meaning, but the grammatical difference 
SELECT ID,NAME FROM A WHERE　ID　NOT IN (SELECT AID FROM B)

Here are the common usages:

The usage and differences of IN, NOT IN, EXISTS, NOT EXISTS in SQL:

IN: Determines whether the given value matches the value in the subquery or list.

The IN keyword allows you to select rows that match any value in the list.

When you want to obtain a list of names and states of all authors living in California, Indiana, or Maryland, the following query is required:

SELECT ProductID, ProductName FROM  WHERE CategoryID = 1 OR CategoryID= 4 OR CategoryID = 5

However, if you use IN, typing fewer characters can also get the same result:

SELECT ProductID, ProductName FROM  WHERE CategoryID IN (1, 4, 5)

Items after the IN keyword must be separated by commas and enclosed in brackets.

The following query looks for all authors' au_ids in the titleauthor table that gets less than 50% of the royalties in either book, and then select au_id from the authors table and

titleauthor The names of all authors whose query results match:

SELECT au_lname, au_fname FROM authors WHERE au_id IN (SELECT au_id FROM titleauthor WHEREroyaltyper <50)

The results show that some authors fall into the category of less than 50%.

NOT IN: Subqueries introduced through the NOT IN keyword also return a column of zero values ​​or more values.The following query finds the name of a publisher who has not published a commercial book.

SELECT pub_name FROM publishers WHERE pub_id NOT IN (SELECT pub_id FROM titles WHERE type= 'business')

Subqueries introduced using EXISTS and NOT EXISTS can be used for operations on two set principles: intersection and difference.

The intersection of two sets contains all elements that belong to the two original sets at the same time.

The difference contains elements that belong only to the first set of two sets.

EXISTS: Specify a subquery to detect the existence of rows.

This example shows a query to find a title published by any publisher in a city starting 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 pub_id FROM titles WHEREtype = 'business')

The difference between the two:

EXISTS: The query statement that can be followed by the whole sentence, such as: SELECT * FROM titles

IN: Only single columns can be followed: SELECT pub_id FROM titles

NOT EXISTS:

For example, to find the name of a publisher who cannot publish a commercial book:

SELECT pub_name FROM publishers WHERE NOT EXISTS (SELECT * FROM titles WHERE pub_id =publishers.pub_id AND type = 'business')

The following query looks for the names of books that are no longer sold:

SELECT title FROM titles WHERE NOT EXISTS (SELECT title_id FROM sales WHERE title_id =titles.title_id)

grammar

EXISTS subqueryParameter subquery: is a limited SELECT statement (COMPUTE clause and INTO keyword are not allowed). For more information, see the discussion on 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 setThis example specifies NULL in the subquery and returns the result set, which still takes the value TRUE by using EXISTS.

USE Northwind 
GO 
SELECT CategoryName 
FROM Categories 
WHERE EXISTS (SELECT NULL) 
ORDER BY CategoryName ASC 
GO

B. Comparison of queries using EXISTS and IN

This example compares two semantic similar queries. The first query uses EXISTS while 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

Here is the result set of any query:

pub_name

Algodata Infosystems
New Moon Books

C. Compare queries using EXISTS and = ANY

This example shows two query methods for finding authors who live in the same city as the publisher: the first method uses = ANY and the second method uses EXISTS. Note that both methods return the same information.

USE pubs 
GO 
SELECT au_lname, au_fname 
FROM authors 
WHERE exists 
    (SELECT * 
    FROM publishers 
    WHERE  = ) 
GO 

-- Or, using = ANY 

USE pubs 
GO 
SELECT au_lname, au_fname 
FROM authors 
WHERE city = ANY 
    (SELECT city 
    FROM publishers) 
GO

D. Compare queries using EXISTS and IN

This example shows a query to find a title published by any publisher in a city starting with the letter B:

USE pubs 
GO 
SELECT title 
FROM titles 
WHERE EXISTS 
    (SELECT * 
    FROM publishers 
    WHERE pub_id = titles.pub_id 
    AND city LIKE \'B%\') 
GO 

-- Or, using IN: 

USE pubs 
GO 
SELECT title 
FROM titles 
WHERE pub_id IN 
    (SELECT pub_id 
    FROM publishers 
    WHERE city LIKE \'B%\') 
GO

E. Use NOT EXISTS

NOT EXISTS functions the opposite of EXISTS. If the subquery does not return a row, the WHERE clause in NOT EXISTS is satisfied. This example finds the name of a publisher that does not publish a commercial book:

USE pubs 
GO 
SELECT pub_name 
FROM publishers 
WHERE NOT EXISTS 
    (SELECT * 
    FROM titles 
    WHERE pub_id = publishers.pub_id 
    AND type = \'business\') 
ORDER BY pub_name 
GO

Summarize

This is the end of this article about the usage of exists and not exists in oracle. For more related content on the usage of exists and not exists, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!