Subqueries Flashcards
Common table expression (CTE) allows you to execute a subquery and save the result set for later reference by your main SELECT statement. What are 3 benefits?
neater code, can be referenced multiple times, can reference other CTEs or itself.
A CTE that references itself is known as
recursive
Which clause in a subquery needs to be modified to make a column available for reference in the outer query?
The SELECT clause. Only the columns returned by a subquery’s SELECT clause can be referenced by the outer query.
Which are typically more efficient: correlated or uncorrelated subqueries.
Uncorrelated - they only need to be run once for each time the main SELECT statement is run.
If you want to reference a CTE in a SELECT statement, in which clause do you need to add it?
FROM clause
What is a subquery?
A query that is used inside another query (the outer query) as if it were a table. Nested!
A subquery that you’ve put in the FROM clause could also be called
an inline view
What is a candidate key?
A set of columns that isn’t the primary key but behaves like the primary key.
What is a correlated subquery?
a subquery that references columns from the outer query. it executes once for every row in the outer query. Inefficient!
The EXISTS operator is followed by a subquery. It returns TRUE if the subquery yields any results, FALSE otherwise.
It can be combined with other Boolean logic such as NOT. This is commonly used in the WHERE clause.
For the EXISTS operator to work the subquery must reference…
columns in the outer query
The outer query can only reference what part of the subquery?
The results of the subquery (the columns named in the SELECT clause).
How can you test a subquery to see if it is correlated or uncorrelated?
Try to run it by itself. If it runs it’s uncorrelated.
What is the concept ‘scope’ as it pertains to SQL.
The extent to which a portion of a query can be referenced elsewhere.
