Java MySQL database programming chapter Fourth advanced query (II)

Fourth. Advanced Enquiry (II.)

(1 ) by using exists in subqueries clause to check if a row exists in a subquery. How the subquery can appear where the expression appears

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

First, exists sub-query

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

DROP TABLE IF EXISTS temp;

2. Exists as a subquery for the WHERE statement:

SELECT ..... From table name WHERE EXISTS (sub-query);

3. The argument after the EXISTS keyword is an arbitrary subquery if the subquery has a return row. The result of the exists subquery is true, at which point the outer query statement is executed. If the subquery has no return rows. The result of the exists subquery is false, when the outer query statement is no longer executed.

Second, not EXISTS sub-query

exists and in, also allow the addition of the NOT keyword to implement a fetch, not exists to indicate that it does not exist.

Third, sub-query considerations

1) subqueries can be nested where any expression in an SQL statement appears

In a SELECTE statement, a subquery can be nested in the columns, tables, and query conditions of a SELECT statement, that is, a SELECT clause, a FROM clause, a WHERE clause, a GROUP by clause, and a HAVING clause.

2) tables that appear only in subqueries that do not appear in the parent query cannot be included in the output column

The final dataset for a multilayer nested subquery contains only the fields that appear in the SELECT clause of the parent query, and the output of the subquery is usually used as the outer-layer subquery data source or for data-judging matching

Java MySQL database programming chapter Fourth advanced query (II)