Detailed explanation of usage examples of Exists in SQL

This article mainly introduces the usage of Exists in SQL. It is very good and has reference value. Friends in need can refer to it

For example, there is a query in the Northwind database as

SELECT c.CustomerId,CompanyName FROM Customers c
WHERE EXISTS(
SELECT OrderID FROM Orders o WHERE o.CustomerID=c.CustomerID)

How does EXISTS work here? The subquery returns the OrderId field, but the external query is looking for the CustomerID and CompanyName fields. These two fields are definitely not in the OrderID. How does this match?

EXISTS is used to check whether the subquery will return at least one row of data. The subquery does not actually return any data, but returns the value True or False

EXISTS Specifies a subquery to detect The existence of the line.

Syntax: EXISTS subquery

Parameters: subquery is a restricted SELECT statement (COMPUTE clause and INTO keyword are not allowed).

Result type: Boolean Returns TRUE if the subquery contains rows, FLASE otherwise.

Example table A: TableIn Example table B: TableEx

(1). Using NULL in the subquery still returns the result Set

select * from TableIn where exists(select null)

is equivalent to: select * from TableIn

(2). Compare queries using EXISTS and IN. Notice that both queries return the same results.

select * from TableIn where exists(select BID from TableEx where BNAME=TableIn.ANAME)
select * from TableIn where ANAME in(select BNAME from TableEx)

(3). Compare queries using EXISTS and = ANY. Notice that both queries return the same results.

select * from TableIn where exists(select BID from TableEx where BNAME=TableIn.ANAME)
select * from TableIn where ANAME=ANY(select BNAME from TableEx)

NOT EXISTS does the exact opposite of EXISTS. If the subquery returns no rows, the WHERE clause in NOT EXISTS is satisfied.

Conclusion:

The return value of the EXISTS (including NOT EXISTS) clause is a BOOL value. There is a subquery statement (SELECT ... FROM...) inside EXISTS, which I call the inner query statement of EXIST. The query statement within it returns a result set. The EXISTS clause returns a Boolean value based on whether the result set of the query statement within it is empty or non-empty.

A popular way to understand it is: Substitute each row of the outer query table into the inner query as a test. If the result returned by the inner query is a non-null value, the EXISTS clause returns TRUE, and this row can As the result row of the outer query, otherwise it cannot be used as the result.

The analyzer will first look at the first word of the statement. When it finds that the first word is the SELECT keyword, it will jump to the FROM keyword, then find the table name through the FROM keyword and put The table is loaded into memory. The next step is to look for the WHERE keyword. If it cannot be found, return to SELECT to find the field analysis. If WHERE is found, analyze the conditions, and then return to SELECT to analyze the field. Finally, a virtual table is formed.

What follows the WHERE keyword is the conditional expression. After the conditional expression is calculated, there will be a return value, which is non-zero or 0. Non-zero means true (true), and 0 means false (false). In the same way, the condition after WHERE also has a return value, true or false, to determine whether to execute SELECT next.

The analyzer first finds the keyword SELECT, then jumps to the FROM keyword to import the STUDENT table into the memory, and finds the first record through the pointer, and then finds the WHERE keyword to calculate its conditional expression, if it is true Then load this record into a virtual table, and the pointer points to the next record. If false, the pointer points directly to the next record without performing other operations. Always retrieve the entire table and return the retrieved virtual table to the user. EXISTS is part of a conditional expression, which also has a return value (true or false).

Before inserting a record, you need to check whether the record already exists. The insertion operation will only be performed when the record does not exist. You can prevent the insertion of duplicate records by using the EXISTS conditional statement.

INSERT INTO TableIn (ANAME,ASEX) 
SELECT top 1 '张三', '男' FROM TableIn
WHERE not exists (select * from TableIn where TableIn.AID = 7)

Regarding the usage efficiency of EXISTS and IN, it is usually more efficient to use exists than in, because IN does not use indexing, but the specific use depends on the actual situation:

IN is suitable for The situation where the outer surface is large but the inner surface is small; EXISTS is suitable for the situation where the outer surface is small but the inner surface is large.

The above is the detailed content of Detailed explanation of usage examples of Exists in SQL. For more information, please follow other related articles on the PHP Chinese website!