3.6 Sub Queries Flashcards
What can be said about this subquery
The outer SELECT statement uses a subquery to determine which languages are used by a larger percentage of a country’s population than Aruba’s official language.
Which query executes first?
- The subquery
2executes first to find the official language Percentage for ABW, which is 5.3. - The outer query
1executes using the value 5.3 returned by the subquery2.
What would this subquery return?
What would this subquery return?
What does this query return?
Dutch, Balochi, Kongo
switching step 1 & 2 would be ideal.
Using the Exist operator
What does this query return?
Sam Snead
- Lisa Ellison and Maria Rodriguez both have a ‘Spouse’ in the Family table, but Sam Snead does not.
- NOT EXISTS returns TRUE when no rows are selected in the subquery, so only Sam Snead is returned.
Using the Exist operator
What does this query return?
Lisa Ellison and Maria Rodriguez
- The correlated subquery returns one row for Lisa Ellison and one row for Maria Rodriguez.
- No row exists in Family that has Sam Snead’s Id and relationship ‘Spouse’.
What is a subquery?
A subquery, also known as a nested query or inner query, is a query embedded within another SQL query
Typically used in a SELECT statement’s WHERE clause to provide data to the outer query, influencing the outcome of the selection. The subquery is enclosed in parentheses.
What is the purpose of a subquery in SQL?
To provide data to the outer query, influencing the outcome of the selection
Typically used in the WHERE clause of a SELECT statement.
A subquery is enclosed in _______.
(parentheses)
What does a correlated subquery do?
It references a column from the outer query within the subquery’s WHERE clause
Rows selected depend on the current row being processed in the outer query.
A correlated subquery selects rows based on _______.
the current row being processed in the outer query.
What does the EXISTS operator do in SQL?
Returns TRUE if the subquery fetches at least one row, FALSE otherwise
NOT EXISTS returns TRUE if no rows are selected, FALSE otherwise.
How can subqueries be transformed for performance optimization?
By flattening subqueries into joins
Most subqueries following IN or EXISTS can be flattened into joins, enhancing efficiency.
What is a key concept of flattening subqueries?
It can significantly improve query performance in data-intensive applications
Example: INNER JOIN City ON CountryCode WHERE Population > 1000000.
Subqueries are used $.
Subqueries can be used in various clauses, including WHERE.
What role do subqueries play in SQL queries?
They serve as powerful tools for organizing complex SQL queries
Understanding the use of subqueries and correlated subqueries enhances data extraction strategies.
This is an example of what type of query
Selecting languages with a higher usage percentage than Aruba’s official language
This is an example of a subquery
This is an example of what type of query
- Selecting cities with a population larger THAN
- the country’s average city population
This is an example of a correlated subquery
What is an Alias?
A temporary name given to a table or column for the duration of a query.
Why use Aliases? 3
- simplifies complex queries
- avoids ambiguity
- improves clarity
How do you create an Alias using the AS Keyword?
Use the AS keyword to define an alias
SELECT Name AS N FROM City AS C
Can you create an Alias without the AS Keyword?
- The
ASkeyword is optional and may be omitted.
In the query what does E represent?
SELECT Name
FROM Employee E WHERE NOT EXISTS
(SELECT * FROM Family
WHERE Id = E.Id AND Relationship = 'Spouse');
E is an alias for the Employee table.
What is the importance of the alias Employee.Id in the subquery?
SELECT Name
FROM Employee E
WHERE EXISTS
(SELECT *
FROM Family
WHERE ID = E.Id
AND Relationship = 'Spouse');
It allows referencing the Id of the current employee without repeating Employee.Id
An alias gives tables or columns a temporary name for _______.
clarity.
Using the AS keyword is strictly \_\_\_ when creating an alias.
Optional
What does the alias N refer to in the example?
SELECT Name AS N FROM Employee AS E
N refers to the Name column.
What does the alias C refer to in the example?
SELECT Name AS N FROM City AS C
C refers to the City table.
What is the term for replacing a subquery with an equivalent join?
Flattening a query
When are joins usually preferred in SQL?
When performance is a concern
Which types of subqueries can usually be flattened?
Most subqueries that follow:
IN-
EXISTS, or - return a single value
Which types of subqueries typically cannot be flattened?
Most subqueries that follow NOT EXISTS or contain a GROUP BY clause cannot be flattened
List the first step in flattening a query.
Retain the outer query SELECT , FROM, GROUP BY, HAVING, and ORDER BY clauses
What should be added to each subquery table during the flattening process?
INNER JOIN clauses
Second step in first past
During flattening, where should comparisons between subquery and outer query columns be moved?
To ON clauses
Third step in the first pass
What should be included in the WHERE clause after flattening a query?
Remaining expressions in the subquery and outer query WHERE clauses
Fourth step in the first pass
What is the final step if necessary during the flattening process?
Remove duplicate rows with SELECT DISTINCT
Fifth step in the first pass
What is the first pass step at flattening a query?
Retain the outer query SELECT, FROM, GROUP BY, HAVING, and ORDER BY clauses.
What should be done after the first pass?
- Test the flattened query and adjust to achieve the correct result.
- Verify that the original and flattened queries are equivalent against a variety of data.
