Databases Reference
In-Depth Information
3.
Rewrite the same query using the
EXISTS
construct:
SELECT AMOUNT_SOLD FROM sh.SALES S
WHERE EXISTS (
SELECT NULL FROM sh.CUSTOMERS C
WHERE S.CUST_ID = C.CUST_ID
AND C.CUST_CREDIT_LIMIT IN (10000, 11000, 15000)
);
4.
Select a table using the
NOT
EXISTS
operator:
SELECT AMOUNT_SOLD FROM sh.SALES S
WHERE NOT EXISTS (
SELECT NULL FROM sh.CUSTOMERS C
WHERE S.CUST_ID = C.CUST_ID
AND C.CUST_CREDIT_LIMIT IN (10000, 11000, 15000)
);
5.
Rewrite the same query using the
NOT
IN
construct:
SELECT AMOUNT_SOLD FROM sh.SALES S
WHERE S.CUST_ID NOT EXISTS (
SELECT C.CUST_ID FROM sh.CUSTOMERS C
WHERE C.CUST_CREDIT_LIMIT IN (10000, 11000, 15000)
);
6.
The following is a different (algebraic) way to express the same query:
SELECT AMOUNT_SOLD FROM sh.SALES S
LEFT OUTER JOIN sh.CUSTOMERS C
ON S.CUST_ID = C.CUST_ID
AND C.CUST_CREDIT_LIMIT IN (10000, 11000, 15000)
WHERE C.CUST_ID IS NULL;
How it works...
The query in step 2 selects the amount sold for the sales regarding customers who have a
credit limit which is either
10000
,
11000
, or
15000
.
We have expressed the query using a subquery to identify the customers with the appropriate
credit limit and the
IN
operator to filter only the sales related to those customers. We have
used the
IN
operator to correlate the subquery to the main query:
SELECT C.CUST_ID FROM sh.CUSTOMERS C WHERE C.CUST_CREDIT_LIMIT
