Shulou(Shulou.com)06/01 Report--

1. The literal meaning of exists exists.

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

The subquery result set in 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 find out 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:

A subquery with an EXISTS predicate returns no data, only a logical truth value of "true" or a logical false value of "false".

After using the existential quantifier EXISTS, if the inner query result is not empty, the outer WHERE clause returns true value or false value.

Subqueries derived from EXISTS usually use * in their target column expressions, because subqueries with EXISTS only return true or false values

It is of no practical significance to give a column name.

2. Use the related subquery of exists

Understand the relevant subqueries:

The query condition of a subquery depends on a property value of the outer parent query, which is called a related subquery.

Solving related subqueries can not be like solving unrelated subqueries, solving subqueries at one time, and then solving parent queries.

Because the inner query is related to the outer query, it must be evaluated repeatedly.

Clarify the relevant subquery processing process:

Conceptually, the general processing of related queries is:

First, go to the first tuple of the table in the outer query, and process the inner query according to its attribute values related to the inner query.

If the return value of the WHERE clause is true, take this tuple and put it in the result table; then take the next tuple of the table

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 the following sql:

Select a.ENAME from scott.emp a

Where a.deptno in (select b.deptno from scott.dept b)

Subqueries actually narrow the scope of the main query by associating other tables with certain conditions.

3. Not exists is simply understood as not exists= not in

The actual not exists gets the dataset of the master table that does not belong to the exists constraint

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

11 records were found.

4. Exists and in

The in clause is commonly used in unrelated subqueries. Usually the subquery is executed first, and the structure of the subquery is used for the parent query.

The query condition of the subquery does not depend on the parent query, and this kind of subquery is called irrelevant subquery.

Keyword: what is the difference between in and exists when fetching data in Oracle SQL?

In and exists are sometimes used to fetch data in Oracle SQL, so what's the difference between them?

1 comparison of performance

For example, Select * from T1 where x in (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

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 will inevitably be scanned completely.

What are the circumstances in which they apply?

Taking the subquery (select y from T2) as the consideration direction, it will take a lot of time if the result set of the subquery is very large.

But T1 relatively small execution (select null from T2 where y = x.x) is very fast, so exists is more suitable for use here.

In should be used when the result set of the corresponding subquery is small.

Question: I have created a table to store customer information, and I know that I can insert information into the table using insert statements

But how can we make sure that duplicate records are not inserted?

Answer: you can prevent duplicate records from being inserted by using EXISTS conditionals.

Example 1: insert multiple records

Suppose you have an clients table with the primary key 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 note: not exists does not exist, that is, as long as the data is returned in parentheses, then this condition does not exist.

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 true only if the right expression also returns not exists (that is, the result of the subsequent query is non-empty).

Example 1: insert 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 the table name allows you to follow the values of the fields you want to insert directly after the select statement, even if those values do not already exist in the current table.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.