Java MySQL database programming Chapter 4 advanced query (II), mysql advanced Query

Java MySQL database programming Chapter 4 advanced query (II), mysql advanced Query
Chapter 4 advanced query (II)

(1) By using EXISTS in the subqueryClause to check whether the row in the subquery exists. Where can a subquery appear in an expression?

(2) Subquery statements can be nested in SQLThe position where any expression appears in the statement.

1. EXISTS subquery

 

1. Use the EXISTS statement to determine whether the database object EXISTS:

Drop table if exists temp;

 

2. As a subquery of the where statement, EXISTS:

SELECT... FROM table name where exists (subquery );

 

3. The parameter following the EXISTS keyword is an arbitrary subquery. If the subquery has rows returned. The result of the EXISTS subquery is true, and then the outer query statement is executed. If any row is returned in the subquery. The result of the EXISTS subquery is false. At this time, the outer query statement is not executed.

Ii. not exists subquery

Like EXISTS and IN, the NOT keyword can also be added for the return operation. not exists indicates that the return operation does NOT exist.

 

Iii. subquery considerations

1) subqueries can be nested in any position where expressions appear in SQL statements.

In SELECTE statements, subqueries can be nested in columns, tables, and query conditions of SELECT statements, that is, SELECT clauses, FROM clauses, WHERE clauses, group by clauses, and HAVING clauses.

 

2) tables that only appear in the subquery but not in the parent query cannot be included in the output column.

The final dataset of a multi-layer nested subquery only contains the fields in the SELECT clause of the parent query. The output result of the subquery is usually used as the data source of its outer subquery or used for data judgment and matching.