The following SQL contains a nest IN subquery (shown in bold text) that is graphically represented with the Subquery summary icon and the IN join.
SELECT
cs.customerid,
cs.firstname,
cs.lastname,
mr.rentalid,
mr.duedate,
mr.totalcharge,
ri.itemnumber
FROM
(
SELECT
c1.customerid,
c1.firstname,
c1.lastname,
c1.phone
FROM MOVIES.customer c1
WHERE EXISTS (SELECT NULL
FROM MOVIES.customer c2
WHERE
c1.customerid <> c2.customerid AND
c1.lastname = c2.lastname AND
c1.phone BETWEEN 0 AND 9999569900)
)
cs,
(
SELECT
customerid,
rentalid,
duedate,
totalcharge,
rentaldate
FROM MOVIES.movierental
WHERE totalcharge > 10
)
mr,
MOVIES.rentalitem ri
WHERE
LENGTH (cs.lastname) = 10 AND
- 1 < cs.customerid AND
ROUND (ri.rentalid) > 10 AND
TRUNC (ri.itemnumber) > 1 AND
mr.totalcharge > (SELECT AVG (totalcharge)
FROM MOVIES.movierental
WHERE TOTALCHARGE >= 40) AND
ri.moviecopyid NOT IN (SELECT mc.moviecopyid
FROM MOVIES.moviecopy mc
WHERE
mc.copyformat = 'vhs' AND
mc.copycondition = 'new' AND
mc.movieid IN (SELECT mt.movieid
FROM MOVIES.movietitle mt
WHERE
mt.year < 1990 AND
mt.rating IN ('pg', 'r') AND
mt.categoryid IN (SELECT mc.categoryid
FROM MOVIES.moviecategory mc
WHERE mc.rentalprice = (SELECT MAX (rentalprice)
FROM MOVIES.moviecategory
WHERE categoryid = mc.categoryid)))) AND
mr.CUSTOMERID = cs.CUSTOMERID AND
ri.RENTALID = mr.RENTALID
Graphically, this would display as the following when the MOVIECOPY (MC) subquery is expanded: