Some usages of exist

Recently, I found that I was not quite sure about the exists SQL written by my colleagues, and I collected some information on the Internet to study and study.

The following statements are missing, please correct the wrong place.

1, exists literal meaning exists.

The subquery result set in EXISTS is not empty, and the value of the EXISTS () clause is true.
The subquery result set in the EXISTS is empty, and the value of the EXISTS () clause is false.

SELECT * from SCOTT. EMP WHERE EXISTS (SELECT sysdate from DUAL);
This sentence will identify all the contents of the Scott.emp table.

SELECT * from SCOTT. EMP WHERE EXISTS (SELECT * from Scott.salgrade WHERE 1=2);
Returns an empty result set


Refer to the discussion:
Subqueries with EXISTS predicates do not return any data, generating only the logical truth "true" or the logical false value "false".
With the existence of quantifier exists, if the inner query result is non-null, the outer WHERE clause returns the True value otherwise the false values are returned.
A subquery drawn from exists, whose target column expression is usually *, because a subquery with exists returns only the truth or false value.
Give the column name no practical significance.



2, with the exists related subquery

Understand related subqueries:
Query criteria for subqueries depend on a property value of the outer parent query, which is called a related subquery.
Solving a correlated subquery is not like solving a unrelated subquery, solving the subquery one at a time, and then solving the parent query.
The inner query has to be evaluated repeatedly because it is related to the outer query.

Clarify the related subquery processing process:
Conceptually, the general process of related queries is:
First, we go to the 1th tuple of the table in the outer query, and process the inner query according to the attribute value related to the inner query.
If the WHERE clause returns a true value, the tuple is put into the result table, and then the next tuple of the table is fetched;
Repeat this process until the outer table is fully checked.




Sql> Select A.ename from Scott.emp a
where exists (SELECT * from Scott.dept b where b.deptno=a.deptno);

Ename
----------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
Adams
JAMES
FORD
MILLER

Query to 14 records.


Equivalent to down SQL:
Select A.ename from Scott.emp a
where A.deptno in (select B.deptno from scott.dept b);


The exists subquery actually shrinks the scope of the main query by associating other tables with a certain condition.



3, not exists simple understanding is not exists=
The actual not exists gets the dataset that is not a primary table that is not part of the exists restriction condition



Sql> Select A.ename from Scott.emp a
where exists (SELECT * from Scott.dept b where B.deptno=a.deptno and b.deptno=10);

Ename
----------
CLARK
KING
MILLER


Select A.ename from Scott.emp a
Where NOT EXISTS (SELECT * from Scott.dept b where B.deptno=a.deptno and b.deptno=10)

Ename
----------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
SCOTT
TURNER
Adams
JAMES
FORD

Query to 11 records.





4, exists and in

In clauses are often used in unrelated subqueries. A subquery is usually executed first, and the structure of the subquery is used for the parent query.
Query criteria for subqueries are not dependent on parent queries, which are called unrelated subqueries.

Keywords: the difference between in and exists when fetching data in Oracle SQL.
When fetching data in Oracle SQL it is sometimes necessary to use in and exists so what's the difference between them?

1 Performance Comparisons
such as SELECT * from T1 where x ' (select y from T2)
The process of execution is equivalent to:
SELECT *
from T1, (select distinct y from T2) T2
where t1.x = T2.y;

Relative to the

SELECT * from t1 where exists (select null from t2 where y = x)
The process of execution is equivalent to:
For x in (SELECT * from T1)
Loop
if (exists (select null from t2 where y = x.x))
Then
OUTPUT the Record
End If
End Loop
Table T1 inevitable to be completely scanned again

What are the circumstances of each application?
In the direction of subqueries (select Y from T2), if the result set of the subquery is large enough to consume a lot of time,
But T1 relatively small execution (select null from t2 where y = x.x) is very fast, then exists is more suitable for use here.
The result set of the relative due subquery should be used in when it is small.





5. Question: I created a table to store customer information, and I know that you can insert information into a table with INSERT statements.
But how do you make sure that you don't insert duplicate records?

Answer: You can prevent the insertion of duplicate records by using EXISTS conditional sentences.


Example one: inserting more than one record

Suppose you have a clients table with a primary key of client_id, you can use the following statement:




INSERT into clients
(client_id, Client_name, Client_type)
SELECT supplier_id, Supplier_name, ' advertising '
From suppliers
WHERE NOT EXISTS (SELECT * from clients
where clients.client_id = suppliers.supplier_id);


Personal annotation: Not exists does not exist, which means that the condition does not exist if the data is returned in the parentheses behind it.
It can be understood that the notexists before parentheses is a left expression, and the query after parentheses is a right expression,
The equation is only true when the right expression returns the not EXISTS (that is, the result of the subsequent query is Non-null).






Example one: inserting a single record

Code:


INSERT into clients
(client_id, Client_name, Client_type)
SELECT 10345, ' IBM ', ' advertising '
From dual
WHERE NOT EXISTS (SELECT * from clients
where clients.client_id = 10345);


Using dual as a table name allows you to follow directly to the value of the field you want to insert after the SELECT statement, even if the values do not yet exist in the current table.