Usage of exists in SQL

For example, there is a query in the Northwind database that
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 that detects the existence of a row.

Syntax: EXISTS subquery
Parameter: subquery is a restricted SELECT statement (the COMPUTE clause and the INTO keyword 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)
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 on (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=any (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 NOT EXISTS (SELECT * from Tablein where Tablein.aid = 7)

The use of exists and in efficiency, usually using exists is higher than in efficiency, because in does not walk the index, but depends on the actual use of:
In the case of large appearance and small inner table, exists is suitable for small appearance and large inner table.

The difference between in, not in, exists, and NOT exists:

Let's talk about the difference between in and exists:
exists: exists, followed by a subquery, and exists returns True when the subquery returns the number of rows.
SELECT * from class where exists (select ' X "Form Stu where Stu.cid=class.cid)
When the in and exists are compared in query efficiency, in query efficiency is faster than exists query efficiency
The subquery behind exists (XXXXX) is called a correlated subquery, and he does not return the value of the list.
Just returns the result of a ture or false (which is why the subquery is a select ' X ' cause of course it can also

Select anything) that is, it only cares if the data in parentheses can be found and if there is such a record.
Its operation is to run the main query once and then go to the subquery query and its corresponding results if there is, return ture is lost

Out, the reverse returns false is not output, and then according to each row in the main query to query the subquery.

The execution sequence is as follows:
1. First execute an external query
2. The subquery is executed once for each row in the outer query and is referenced in the external query each time the subquery is executed.

The forward value.
3. Use the results of the subquery to determine the result set of the external query.
If an external query returns 100 rows, SQL executes 101 queries, executes an external query one time, and then returns an external query

Executes the subquery once for each row.

In: Contains
Check with all girls of the same age boys
SELECT * from Stu where sex= ' man ' and "Age" (select age from Stu where sex= ' female ')
The subquery after in () returns the result set, in other words the execution order is not the same as exists (). The subquery first produces the result set,
The main query then goes to the result set to find the list of fields that match the requirements. The output that meets the requirements, and vice versa.

The difference between not and NOT exists:
Not in a subquery only if the field after the SELECT keyword has a NOT null constraint, or if there is such a hint with not, and if the table in the main query is large, the table in the subquery is small but has more records, you should use not in,
For example, to inquire about students who are not in the class,
SELECT * FROM class where CID No in (select DISTINCT CID from Stu)
When there is a null value in the CID in the table, not the null value is not processed
Resolution: SELECT * from class

Where CID not in

(SELECT DISTINCT CID from Stu where CID was not null)

The execution order of not in is: A query that records a record in a table (querying each record) meets the requirements of returning the result set, and continues to query the next record until the records in the table have been queried. In other words, in order to prove not found, so can only query all records to prove. The index is not used.
NOT exists: If there are fewer records in the main query table, there are many records in the subquery table, and there are indexes.
For example, to inquire about students who are not in the class,
SELECT * FROM Class2

Where NOT EXISTS

(SELECT * from STU1 where Stu1.cid =class2.cid)

The execution order of NOT EXISTS is: query in the table, is based on the index query, if it exists, return true, if it does not exist, return false, not every record will go to query.
The reason is to use not exists rather than not, that is, the efficiency of NOT EXISTS query is far higher than the efficiency of not in query.

Instance:

Exists,not exists example of how to use, you need to refer to a friend.

Student table: CREATE TABLE Student
(
ID Number (8) primary key,
Name Varchar2 (ten), deptment number (8)
)

Choose a timetable: CREATE TABLE Select_course
(
ID Number (8) primary key,
STUDENT_ID Number (8) foreign key (course_id) references COURSE (ID),
COURSE_ID Number (8) foreign key (student_id) references STUDENT (ID)
)

Curriculum: CREATE TABLE COURSE
(
ID Number (8) is not NULL,
C_name VARCHAR2 (20),
C_no VARCHAR2 (10)
)

 data for the student table: 
         ID name             deptment_id 
------------------------------------
           1 echo                    
          2 spring                  
           3 smith                   
          4 liter                   

 data for the course table: 
         ID c_name                c_no 
--------------------------------------
           1 Databases                 data1 
          2 Math                   month1 
           3 English                   English1 

Data for the Select_course table:
ID student_id course_id
---------- ---------- ----------
1 1 1
2 1 2
3 1 3
4 2 1
5 2 2
6 3 2

1. The query took the student ID, name: For all courses (that is, the student does not have a course he has not chosen.) ）

and sc.course_id=c.id exist null,

This indicates that (2) SELECT * FROM course C does not exist in the query results (1 queries), the results of the query returned no selected courses,

The NOT EXISTS after select * from T_student TS evaluates to FALSE and does not execute the query.

Sql> SELECT * from T_student ts where not exists
(SELECT * from course C where not exists

ID NAME deptment_id
---------- --------------- -----------
1 Echo 1000

2. Inquire about students who have not selected all the courses, that is, students who do not have a full selection. (There is such a student, he has at least one course not chosen),

Analysis: As long as there is a door not selected, that is, select * from Select_course SC where student_id=t_student.id and course_id
=course.id has an empty, that is, not exists null is true, at which point the SELECT * from course has a query result (ID is course.id in the subquery),

Therefore the select Id,name from t_student executes the query (ID is t_student.id in the subquery).

Sql> Select Id,name from t_student where exists

(SELECT * from course where NOT exists

(SELECT * from Select_course SC where student_id=t_student.id and course_id=course.id));

ID NAME
---------- ---------------
2 Spring
3 Smith
4 liter

3. Inquire about a student who has not chosen a course. (He does not have such a student, he has at least one course),

Analysis: If he took a select * from course the result set is not empty and not exists evaluates to false;

Select Id,name from T_student does not execute the query.

Sql> Select Id,name from t_student where NOT exists

(SELECT * from course where exists

(SELECT * from Select_course SC where student_id=t_student.id and course_id=course.id));

ID NAME
---------- ---------------
4 liter

4. Check with a student who has enrolled at least one course.
Sql> Select Id,name from t_student where exists

(SELECT * from course where exists

(SELECT * from Select_course SC where student_id=t_student.id and course_id=course.id));

ID NAME
---------- ---------------
1 echo
2 Spring
3 Smith

Usage of exists in SQL