The exists in SQL Drip 35-sql statement

Text: The exists in the SQL drip 35-sql statement

For example, in the Northwind database there is a query for select C.customerid,companyname from Customers C where EXISTS (select OrderID from Orders o where O . Customerid=c.customerid) How does this exists work? The subquery returns the OrderID field, but the outside query is looking for the CustomerID and CompanyName fields, and the two fields are definitely not in OrderID, how does this match?

EXISTS is used to check if a subquery returns at least one row of data, and the subquery does not actually return any data, but instead returns a value of TRUE or False EXISTS specifies a subquery to detect the presence of a row.
Syntax: EXISTS subquery parameter: subquery is a restricted SELECT statement (COMPUTE clauses and into keywords are not allowed). Result type: Boolean returns TRUE if the subquery contains rows, otherwise returns flase.

Example Table A:tablein	Example Table B:tableex

(i). Using NULL in a subquery still returns the result set select * from Tablein where exists (select NULL) is equivalent to: SELECT * from Tablein

(b). Compare queries that use EXISTS and in. Note Two queries return the same result. SELECT * from Tablein where exists (select BID from Tableex where bname=tablein.aname) select * from Tablein where ANAME in (select Bname from Tableex)

(iii). Compare queries that use EXISTS and = any. Note Two queries return the same result. SELECT * from Tablein where exists (select BID from Tableex where bname=tablein.aname) select * from Tablein where Aname=an Y (select bname from Tableex)

The role of not EXISTS is the opposite of EXISTS. If the subquery does not return a row, 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 inside the exists (SELECT ... From ...), which I call an inner query statement for exist. The inner query statement returns a result set. The EXISTS clause returns a Boolean value based on the result set of its query statement, either empty or non-empty.
A popular can be understood as: each row of the outer query table, in the query as a test, if the query returned by the result of a non-null value, then the EXISTS clause returns true, this row of rows can be used as the result row of the outer query, otherwise it cannot be a result.

The parser first looks at the first word of the statement, and when it finds out that the first word is the SELECT keyword, it jumps to the FROM keyword and then finds the table name from the keyword and loads the table into memory. Then find the WHERE keyword, if not found, return to the Select to find the field resolution, if found where, then analyze the conditions, complete and then return to the Select analysis field. Finally form a virtual table we want. The WHERE keyword is followed by a conditional expression. When the conditional expression is evaluated, a return value of 0 or 0, not 0 is true (true), and 0 is False (false). Similarly, the condition behind the where also has a return value, true or FALSE, to determine that the next hold does not execute the SELECT. The parser first finds the keyword Select, jumps to the FROM keyword, imports the student table into memory, finds the first record through the pointer, and then finds the conditional expression where the keyword evaluates it, and if it is true then put the record in a virtual table, and the pointer points to the next record. If False then the pointer points directly to the next record without any other action. Retrieves the entire table and returns the retrieved virtual table to the user. Exists is part of the conditional expression, and it also has a return value (TRUE or false).

Before inserting a record, you need to check to see if the record already exists, and to perform the insert operation only if the record does not exist, you can prevent the insertion of duplicate records by using the EXISTS conditional clause. INSERT into Tablein (aname,asex) SELECT top 1 ' Zhang San ', ' Male ' from Tablein where isn't exists (SELECT * from Tablein WHERE Tablein . AID = 7)
The problem of efficiency in the use of exists and in, usually using exists is higher than in efficiency, because in does not walk the index, but depends on the actual use: in the appearance of large and small inside the case, exists suitable for the appearance of small and inner table large case.

Transferred from: http://www.cnblogs.com/netserver/archive/2008/12/25/1362615.html

The exists in SQL Drip 35-sql statement