Subqueries Flashcards
What type of query is used inside another query as if it were a table?
Subquery
Last Revised: 4/2/21, 3.3
True or False: A subquery is always enclosed within parentheses, and it is usually executed prior to its outer query.
True
Last Revised: 4/2/21, 3.5
Put parentheses around your query, give it an alias, and you’ve got a subquery that you can put into the ____________ clause.
FROM
Last Revised: 4/2/21, 3.6
Inline views are another name for ________ ?
Subqueries in the FROM clause of the outer query
Last Revised: 4/2/21, 3.6
What is a candidate key?
(Last Revised: 4/2/21, 3.7)
What operator returns TRUE if the subquery yields any results and FALSE otherwise?
The EXISTS operator
Last Revised: 4/2/21, 3.8
What follows the EXISTS operator?
A subquery
Last Revised: 4/2/21, 3.8
Because the EXISTS operator returns TRUE or FASLE, it can be combined with other Boolean logic such as _____ .
NOT
Last Revised: 4/2/21, 3.8
What clause is the EXISTS operator commonly used in?
The WHERE clause
Last Revised: 4/2/21, 3.8
What is another way to think about latest encounters?
Think of it as “there are no later encounters.” In SQL, that translates to WHERE NOT EXISTS followed by a subquery that returns later encounters
(Last Revised: 4/2/21, 3.8)
What is the extent to which a portion of a query can be referenced elsewhere?
Scope
Last Revised: 4/2/21, 3.10
True or False: An outer query can only reference the results of a subquery, not the data or logic used to create the results.
True
• That means that the outer query can only reference the columns returned by the SELECT clause of the subquery.
(Last Revised: 4/2/21)
What clause does the subquery use to pass columns to the outer query?
The SELECT clause
Last Revised: 4/2/21, 3.10
Can a subquery reference the columns retuned by the FROM clause of the outer query?
Yes
• When a subquery is processed, SQL attempts to resolve references within the subquery; it that fails, then SQL attempts to resolve the reference with columns retuned by the FROM clause in the outer query.
(Last Revised: 4/2/21, 3.10)
What is an Uncorrelated Subquery a result of?
If a subquery doesn’t reference columns from the outer query, it is known as an uncorrelated subquery.
(Last Revised: 4/2/21)
What is a Correlated Subquery a result of?
If a subquery references columns from the outer query, it is known as a correlated subquery. Correlated subqueries are executed one for each row processed by the outer query, so they should generally be avoided for performance reasons. Queries that include correlated subqueries can often be rewritten to not use correlated subqueries.
(Last Revised: 4/2/21, 3.11)
Are subqueries that appear in the FROM clause always correlated or uncorrelated?
They are always uncorrelated because the FROM clause hasn’t finished executing by the time the subquery is processed
• Subqueries elsewhere in the outer query may be correlated or uncorrelated.
(Last Revised: 4/2/21, 3.11)
What operator should you use if you want your subquery to return only one column of data?
The IN operator
Write a query that has only one column in the SELECT clause. Then wrap it in parenthesis and it’s ready to be used as a subquery with the IN operator.
(Last Revised: 4/2/21, 3.13)
What type of operator and in what clause should you use if you want your subquery to return a single value?
A comparison operator in the WHERE clause
Write a query that has only one column in the SELECT clause and returns only one row. Then wrap it in parentheses and it’s ready to be used as a subquery with a comparison operator.
(Last Revised: 4/2/21, 3.15)
What do common table expressions (CTE) do?
They allow you to execute a subquery and save the result set for later reference by you main SELECT statement.
(Last Revised: 4/2/21, 3.16)
What is a recursive CTE?
It is when a CTE references other CTEs.
Last Revised: 4/2/21, 3.16
Where is the WITH clause positioned in your query?
Immediately before the main SELECT statement.
Last Revised: 4/2/21, 3.16
Where are CTEs defined?
In the WITH clause.
Last Revised: 4/2/21, 3.16
Can CTEs be referenced directly and be given aliases?
Yes, in the FROM clause.
Last Revised: 4/2/21, 3.17
