SQL EXISTS usage and SQLEXISTS usage
For example, a query in the Northwind database is
SELECT c. CustomerId, CompanyName FROM Customers c
Where exists (
SELECT OrderID FROM Orders o WHERE o. CustomerID = c. CustomerID)
EXISTS is used to check whether a subquery returns at least one row of data. This subquery does not actually return any data, but returns True or False.
EXISTS specifies a subquery to check the existence of rows.
Syntax: EXISTS subquery
Parameter: subquery is a restricted SELECT statement (COMPUTE clauses and INTO keywords are not allowed ).
Result type: Boolean. If the subquery contains rows, TRUE is returned. Otherwise, FLASE is returned.
| 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)
Equivalent to: select * from TableIn
(2). Compare queries using EXISTS and IN. Note that the two 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 the queries using EXISTS and = ANY. Note that the two 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 is opposite to EXISTS. If the subquery does NOT return rows, the WHERE clause in not exists is satisfied.
Conclusion:
The return value of an EXISTS (including not exists) clause is a BOOL value. EXISTS has a subquery Statement (SELECT... FROM...), which is called the EXIST internal query statement. The query statement returns a result set. The EXISTS clause returns a Boolean value based on whether the query result set is null or not.
It can be understood as follows:
Each row of the external query table is substituted into the internal query as the test. If the results returned by the internal query are not null, The EXISTS clause returns TRUE. This row can be used as the results row of the external query, otherwise, it cannot be used as a result.
The analyzer first looks at the first word of the statement. When it finds that the first word is the SELECT keyword, it jumps to the FROM keyword, finds the table name through the FROM keyword, and loads the table into the memory. Next, find the WHERE keyword. If the WHERE keyword is not found, return to SELECT to find the field for parsing. If the WHERE keyword is found, analyze the conditions in it, and then return to SELECT to analyze the field. Finally, a virtual table is created.
The WHERE keyword is followed by a conditional expression. After the conditional expression is calculated, a return value is displayed, that is, if the value is not 0 or 0, the return value is true (true), and if the value is not 0, the return value is false (false ). Similarly, the condition after WHERE also has a return value, true or false, to determine whether to execute the SELECT statement.
Analyzer first finds the keyword SELECT, then jumps to the FROM keyword to import the STUDENT table to the memory, finds the first record through the pointer, and then finds the WHERE keyword to calculate its conditional expression, if it is true, this record is loaded into a virtual table, and the Pointer Points to the next record. If it is false, the pointer points directly to the next record without any other operations. Retrieve the complete table and return the retrieved virtual table to the user. EXISTS is part of a conditional expression, and it also has a return value (true or false ).
Before inserting a record, you must check whether the record EXISTS. The insert operation is performed only when the record does not exist. You can use the EXISTS condition to prevent repeated record insertion.
Insert into TableIn (ANAME, ASEX)
SELECT top 1 'zhang san', 'male' FROM TableIn
WHERE not exists (select * from TableIn where TableIn. AID = 7)
The efficiency of using EXISTS and IN is usually higher than that of in, because IN does not take the index, but depends on the actual situation:
IN is suitable for the case where the external table is large but the internal table is small; EXISTS is suitable for the case where the external table is small but the internal table is large.
