Chapter 15 – Sub-query Functions
“An invasion of Armies can be resisted, but not an idea whose time has come.”
- Victor Hugo
An IN List is much like a Subquery
This query is very simple and easy to understand. It uses an IN List to find all Employees who are in Dept_No 100 or Dept_No 200.
An IN List Never has Duplicates – Just like a Subquery
What is going on with this IN List? Why in the world are their duplicates in there? Will this query even work? What will the result set look like? Turn the page!
An IN List Ignores Duplicates
Duplicate values are ignored here. We got the same rows back as before, and it is as if the system ignored the duplicate values in the IN List. That is exactly what happened.
The Subquery
The query above is a Subquery which means there are multiple queries in the same SQL. The bottom query runs first, and its purpose in life is to build a distinct list of values that it passes to the top query. The top query then returns the result set. This query solves the problem: Show all Employees in Valid Departments!
The Three Steps of How a Basic Subquery Works
The bottom query runs first and builds a distinct IN list. Then the top query runs using the list.
These are Equivalent Queries
Both queries above are the same. Query 2 has values in an IN list. Query 1 runs a subquery to build the values in the IN list.
The Final Answer Set from the Subquery
Quiz- Answer the Difficult Question
How are Subqueries similar to Joins between two tables?
A great question was asked above. Do you know the key to answering? Turn the page!
Answer to Quiz- Answer the Difficult Question
How are Subqueries similar to Joins between two tables?
A Subquery between two tables or a Join between two tables will each need a common key that represents the relationship. This is called a Primary Key/Foreign Key relationship.
A Subquery will use a common key linking the two tables together very similar to a join! When subquerying between two tables, look for the common link between the two tables. Most of the time they both have a column with the same name, but not always.
Should you use a Subquery or a Join?
If you only want to see a report where the final result set has only columns from one table, use a Subquery. Obviously, if you need columns on the report where the final result set has columns from both tables, you have to do a Join.
Quiz- Write the Subquery
Write the Subquery
Select all columns in the Customer_Table if the customer has placed an order!
Here is your opportunity to show how smart you are. Write a Subquery that will bring back everything from the Customer_Table if the customer has placed an order in the Order_Table. Good luck! Advice: Look for the common key among both tables!
Answer to Quiz- Write the Subquery
The common key among both tables is Customer_Number. The bottom query runs first and delivers a distinct list of Customer_Numbers which the top query uses in the IN List!
Quiz- Write the More Difficult Subquery
Write the Subquery
Select all columns in the Customer_Table if the customer has placed an order over $10,000.00 Dollars!
Here is your opportunity to show how smart you are. Write a Subquery that will bring back everything from the Customer_Table if the customer has placed an order in the Order_Table that is greater than $10,000.00.
Answer to Quiz- Write the More Difficult Subquery
Here is your answer!
Quiz- Write the Subquery with an Aggregate
Write the Subquery
Select all columns in the Employee_Table if the employee makes a greater Salary than the AVERAGE Salary.
Another opportunity knocking! Would someone please answer the query door?
Answer to Quiz- Write the Subquery with an Aggregate
Quiz- Write the Correlated Subquery
Write the Correlated Subquery
Select all columns in the Employee_Table if the employee makes a
greater Salary than the AVERAGE Salary (within their own Department).
Another opportunity knocking! This is a tough one, and only the best get this written correctly.
Answer to Quiz- Write the Correlated Subquery
The Basics of a Correlated Subquery
The Top Query is Co-Related (Correlated) with the Bottom Query.
The table name from the top query and the table name from the bottom query are given a different alias.
The bottom query WHERE clause co-relates Dept_No from Top and Bottom.
The top query is run first.
The bottom query is run one time for each distinct value delivered from the top query.
SELECT *
FROM Employee_Table as EE
WHERE Salary > (
SELECT AVG(Salary)
FROM Employee_Table as EEEE
WHERE EE.Dept_No = EEEE.Dept_No) ;
A correlated subquery breaks all the rules. It is the top query that runs first. Then, the bottom query is run one time for each distinct column in the bottom WHERE clause. In our example, this is the column Dept_No. This is because in our example, the WHERE clause is comparing the column Dept_No. After the top query runs and brings back its rows, the bottom query will run one time for each distinct Dept_No. If this is confusing, it is not you. These take a little time to understand, but I have a plan to make you an expert. Keep reading!
The Top Query always runs first in a Correlated Subquery
Correlated Subquery Example vs. a Join with a Derived Table
Both queries above will bring back all employees making a salary that is greater than the average salary in their department. The biggest difference is that the Join with the Derived Table also shows the Average Salary in the result set.
Quiz- A Second Chance to Write a Correlated Subquery
Select all columns in the Sales_Table if the Daily_Sales column is
greater than the Average Daily_Sales within its own Product_ID.
Another opportunity knocking! This is your second chance. I will even give you a third chance.
Answer - A Second Chance to Write a Correlated Subquery
Select all columns in the Sales_Table if the Daily_Sales column is
greater than the Average Daily_Sales within its own Product_ID.
SELECT * FROM Sales_Table as TopS
WHERE Daily_Sales > (
SELECT AVG(Daily_Sales)
FROM Sales_Table as BotS
WHERE TopS.Product_ID = BotS.Product_ID)
ORDER BY Product_ID, Sale_Date ;
Quiz- A Third Chance to Write a Correlated Subquery
Select all columns in the Sales_Table if the Daily_Sales column is
greater than the Average Daily_Sales within its own Sale_Date.
Another opportunity knocking! There is just one minor adjustment and you are home free.
Answer - A Third Chance to Write a Correlated Subquery
Select all columns in the Sales_Table if the Daily_Sales column is
greater than the Average Daily_Sales within its own Sale_Date.
SELECT * FROM Sales_Table as TopS
WHERE Daily_Sales > (
SELECT AVG(Daily_Sales)
FROM Sales_Table as BotS
WHERE TopS.Sale_Date = BotS.Sale_Date)
ORDER BY Sale_Date ;
Quiz- Last Chance to Write a Correlated Subquery
Select all columns in the Student_Table if the Grade_Pt column
is greater than the Average Grade_Pt within its own Class_Code.
Another opportunity knocking! There is just one minor adjustment and you are home free.
Answer – Last Chance to Write a Correlated Subquery
Select all columns in the Student_Table if the Grade_Pt column
is greater than the Average Grade_Pt within its own Class_Code.
SELECT * FROM Student_Table as TopS
WHERE Grade_Pt > (
SELECT AVG(Grade_Pt)
FROM Student_Table as BotS
WHERE TopS. Class_Code = BotS.Class_Code )
ORDER BY Class_Code ;
Answer Set
Quiz- Write the NOT Subquery
Write the Subquery
Select all columns in the Customer_Table if the
Customer has NOT placed an order.
Another opportunity knocking! Write the above query!
Answer to Quiz- Write the NOT Subquery
Quiz- Write the Subquery using a WHERE Clause
Write the Subquery
Select all columns in the Order_Table that were placed
by a customer with ‘Bill’ anywhere in their name.
Another opportunity to show your brilliance is ready for you. Make it happen!
Answer - Write the Subquery using a WHERE Clause
Great job on writing your query just like the one above!
Quiz- Write the Subquery with Two Parameters
Write the Subquery
What is the highest dollar order for each Customer?
This Subquery will involve two parameters!
Get ready to be amazed at either yourself or the Answer on the next page!
Answer to Quiz- Write the Subquery with Two Parameters
Write the Subquery
What is the highest dollar order for each Customer?
This Subquery will involve two parameters!
This is how you utilize multiple parameters in a Subquery! Turn the page for more.
How the Double Parameter Subquery Works
SELECT Customer_Number, Order_Number, Order_Total
FROM Order_Table
WHERE (Customer_Number, Order_Total) IN
(SELECT Customer_Number, MAX(Order_Total)
FROM Order_Table GROUP BY 1) ;
The bottom query runs first returning two columns. Turn to the next page for more info!
More on how the Double Parameter Subquery Works
The IN list is built and the top query can now process for the final Answer Set.
Quiz – Write the Triple Subquery
Write the Subquery
What is the Customer_Name who has the highest dollar order
among all customers? This query will have multiple Subqueries!
Good luck in writing this. Remember that this will involve multiple Subqueries.
Answer to Quiz – Write the Triple Subquery
Write the Subquery
What is the Customer_Name who has the highest dollar order
among all customers? This query will have multiple Subqueries!
The query is above and, of course, the answer is XYZ Plumbing.
Quiz – How many rows return on a NOT IN with a NULL?
How many rows return from the query now that a
NULL value is in a Customer_Number?
We really didn’t place a new row inside the Order_Table with a NULL value for the Customer_Number column, but in theory, if we had, how many rows would return?
Answer – How many rows return on a NOT IN with a NULL?
How many rows return from the query now that a
NULL value is in a Customer_Number?
ZERO rows will return
The answer is no rows come back. This is because when you have a NULL value in a NOT IN list, the system doesn’t know the value of NULL, so it returns nothing.
How to handle a NOT IN with Potential NULL Values
How many rows return NOW from the query? 1 Acme Products
You can utilize a WHERE clause that tests to make sure Customer_Number IS NOT NULL. This should be used when a NOT IN could encounter a NULL.
Using a Correlated Exists
Use EXISTS to find which Customers have placed an Order?
SELECT |
Customer_Number, Customer_Name |
FROM |
Customer_Table as Top1 |
WHERE |
EXISTS |
(SELECT * FROM Order_Table as Bot1
Where Top1.Customer_Number = Bot1.Customer_Number ) ;
The EXISTS command will determine via a Boolean if something is True or False. If a customer placed an order, it EXISTS, and using the Correlated Exists statement, only customers who have placed an order will return in the answer set.
How a Correlated Exists matches up
SELECT |
Customer_Number, Customer_Name |
FROM |
Customer_Table as Top1 |
WHERE |
EXISTS |
(SELECT * FROM Order_Table as Bot1
Where Top1.Customer_Number = Bot1.Customer_Number ) ;
Customer_Number |
Customer_Name |
11111111 |
Billy’s Best Choice |
31323134 |
ACE Consulting |
57896883 |
XYZ Plumbing |
87323456 |
Databases N-U |
Only customers who placed an order return with the above Correlated EXISTS.
The Correlated NOT Exists
Use NOT EXISTS to find which Customers have NOT placed an Order?
SELECT |
Customer_Number, Customer_Name |
FROM |
Customer_Table as Top1 |
WHERE |
NOT EXISTS |
(SELECT * FROM Order_Table as Bot1
Where Top1.Customer_Number = Bot1.Customer_Number ) ;
The EXISTS command will determine via a Boolean if something is True or False. If a customer placed an order, it EXISTS, and using the Correlated Exists statement, only customers who have placed an order will return in the answer set. EXISTS is different than IN as it is less restrictive as you will soon understand.
The Correlated NOT Exists Answer Set
Use NOT EXISTS to find which Customers have NOT placed an Order?
SELECT |
Customer_Number, Customer_Name |
FROM |
Customer_Table as Top1 |
WHERE |
NOT EXISTS |
(SELECT * FROM Order_Table as Bot1
Where Top1.Customer_Number = Bot1.Customer_Number ) ;
Customer_Number |
Customer_Name |
31313131 |
Acme Products |
The only customer who did NOT place an order was Acme Products.
Quiz – How many rows come back from this NOT Exists?
SELECT |
Customer_Number, Customer_Name |
FROM |
Customer_Table as Top1 |
WHERE |
NOT EXISTS |
(SELECT * FROM Order_Table as Bot1
Where Top1.Customer_Number = Bot1.Customer_Number ) ;
How many rows return from the query?
A NULL value in a list for queries with NOT IN returned nothing, but you must now decide if that is also true for the NOT EXISTS. How many rows will return?
Answer – How many rows come back from this NOT Exists?
SELECT |
Customer_Number, Customer_Name |
FROM |
Customer_Table as Top1 |
WHERE |
NOT EXISTS |
(SELECT * FROM Order_Table as Bot1
Where Top1.Customer_Number = Bot1.Customer_Number ) ;
How many rows return from the query?
One row
Acme Products
NOT EXISTS is unaffected by a NULL in the list and that’s why it is more flexible!