2-2-6 Sub Reference (Subquery)

Highlighted text? Practice this concept. Highlighted passages and figures open related questions.

Practice related questions (2)Progress & bookmarks

Current section

p. 275

Practice this section 2 linked exam questions with default filters

Progress

Not marked complete

2-2-6 Sub Reference (Subquery)

Sub reference (subquery) refers to searching for another table on the basis of the search results of a certain table. SELECT statement that determines the conditions is called subquery statement, while the SELECT statement that searches for the table by using the results of subquery statement is called main query statement. Subquery is broadly classified into two types.

(1) Subquery statement that can be independently executed

This subquery statement is independently executed for getting the conditions of the main query statement. In this format, subquery statement inside the parentheses ( ) is executed first, and main query statement is executed on the basis of its results.

Original textbook table, example or syntax box, with its complete original layout. Source text: SELECT Column name, ••• FROM Table name WHERE Column name Comparison term (subquery statement) • Comparison term includes IN predicate, and ANY predicate and ALL predicate combined with comparison operator. Predicate Meaning Either of the results of subquery statement matches. (Same as “= ANY”) IN Comparison operator holds true for either of the results of subquery ANY statement. Comparison operator holds true for all results of subquery statement. ALL Note: If the result of subquery statement is always one, a single comparison operation can also be used. — p. 275 · Enlarge

ex21: From the “Student” table and the “Score” table, extract the names where ExaminationDate is '20XX-10-20'.

p. 276

Original SQL worked example with its complete source code, indentation, punctuation, source frame and any attached original notes. Source text: SELECT Name FROM Student WHERE StudentNumber IN ( SELECT StudentNumber FROM Score WHERE ExaminationDate = '20XX-10-20' ) — p. 276 · Enlarge

Original independent IN subquery · 3 practice questions

Original independent-subquery output lists Kazuki Yamamoto and Chiyo Yamamoto. The adjacent original equivalent IN expression and note are preserved. Source text: <Execution results of ex21> Name Note: Results of the subquery statement have the same meaning as the following SQL statement. Kazuki Yamamoto SELECT Name FROM Student Chiyo Yamamoto WHERE StudentNumber IN ('6724', '6817', '6817') — p. 276 · Enlarge

(2) Correlation subquery (subquery statement that cannot be executed independently)

Correlation subquery uses items of the table in the main query statement as a condition, and therefore, this subquery statement cannot be executed independently. In this case, tuples are fetched one by one from the table in the main query statement and the subquery statement is executed. On the basis of whether the result of the subquery statement is one row or more, whether to extract the concerned tuple or not is decided. EXISTS predicate is used in this decision. · 1 question

Original SQL worked example with its complete source code, indentation, punctuation, source frame and any attached original notes. Source text: ex22: From the “Student” table and the “Score” table, extract the names where ExaminationDate is '20XX-10-20'. SELECT Name FROM Student X WHERE EXISTS ( SELECT * FROM Score Y WHERE X.StudentNumber = Y.StudentNumber AND ExaminationDate = '20XX-10-20' ) — p. 276 · Enlarge

In the SQL statement of ex22, the subquery statement inside the parentheses ( ) cannot be executed independently. (Correlation name X cannot be interpreted.) Therefore, tuples of the “Student” table are fetched one at a time, and the subquery statement is executed.

Complete original correlated-subquery walkthrough for student 6724: the SQL substitutes the current student, the result has row 6724/K11/65/20XX-10-20, and the original conclusion retains that student. Source text: • Execute the subquery statement for the tuple of StudentNumber '6724'. SELECT * FROM Score Y WHERE '6724' = Y.StudentNumber AND ExaminationDate = '20XX-10-20' <Execution results> StudentNumber SubjectNumber Score ExaminationDate 6724 K11 65 20XX-10-20 => The result is one row or more, and therefore, the tuple of StudentNumber '6724' is extracted. — p. 276 · Enlarge

p. 277

Complete original correlated-subquery walkthrough for student 6725: the substituted SQL produces an empty result with its header intact, and the original conclusion excludes that student. Source text: • Execute the subquery statement for the tuple of StudentNumber '6725'. SELECT * FROM Score Y WHERE '6725' = Y.StudentNumber AND ExaminationDate = '20XX-10-20' <Execution results> StudentNumber SubjectNumber Score ExaminationDate => Since there is no single row in the result, the tuple of StudentNumber '6725' is not extracted. — p. 277 · Enlarge

In this manner, by executing the subquery statement by using StudentNumber of all tuples, tuples to be extracted are decided.

When this example is represented without using subquery, the SQL statement is described as below. Here, note that the DISTINCT specification is omitted and (Chiyo Yamamoto) is extracted twice.