laika222
11/4/2016 - 6:31 PM
Subquery, Self-Contained and Correlated
A subquery is a query within a query. The subquery bewteen the () will execute first, and then the outer query will execute using the results from the subquery. In lines 1-2, the subquery will find the average Price from the table products1, and then the outer query will select all from those products where the Price is above the average from the subquery. Lines 5-6 it first returns the CustomerID column from the orders1 table, and then returns all rows from the customers1 table where the CustomerID value is found in the results of the subquery. Apparently the subquery can only return a single row (for instance you can SELECT * in subquery and return multiple rows, just a single row, in this case CustomerID) BUT CHECK ON THIS.
/*
A subquery is a query within a query. They can return a single value, a list of values (a single column of results), or a multi-row table of results (a derived table).
There are two main types of subquery:
SELF-CONTAINED Subquery - where the inner query has no connection with the out query, other than it passes results to the outer query. Vast majority of subqueries are self-contained.
CORRELATED subquery - where the subquery refers to elements of tables used in outer query, it's dependedent on the outer query and cannot be executed separately, behaves as if inner query is excecuted once per outer row, and may return scalar value or multiple values. These can be slower than self-contained subqueries.
*/
-- basic SELF-CONTAINED subquery. The inner query does not rely on the outer query, and can be tested completely independently of the outer query.
SELECT * FROM products1
WHERE Price >= (SELECT AVG(Price) from products1);
-- this...
SELECT * from customers1
WHERE CustomerID IN (Select customerID from orders1);
-- ... turns into this after the subquery is executed
SELECT * from customers1
WHERE CustomerID IN (1, 2, 5, 12, 17, 30);
-- example of self-contained subquery that returns multiple values
DELETE FROM customers11
WHERE CustomerID NOT IN (SELECT customers11.CustomerID
FROM customers11
JOIN orders11
ON customers11.CustomerID=orders11.CustomerID);
-- example of CORRELATED subquery. The subquery references alias O1, which is defined in the outer query. Therefore, the inner query is dependent on the outer query, and cannot be run or tested on its own. Correlated queries can be less effiecient, as they have to go back and forth from the outer query to the inner query for each row when rendering each line of the inner query.
SELECT orderid, empid, orderdate
FROM orders1 AS O1
WHERE orderdate = (SELECT MAX(orderdate)
FROM orders1 AS O2
WHERE O2.empid = O1.empid)
ORDER BY empid, orderdate;