A Subquery that uses the ALL Keyword

SELECT vendor_name, invoice_number, invoice_total

FROM invoices i JOIN vendors v ON i.vendor_id = v.vendor_id

WHERE invoice_total > ALL

(SELECT invoice_total

FROM invoices

WHERE vendor_id = 34)

ORDER BY vendor_name

You have discovered that one of your Vendors (Vendor 34) is overcharging you, you determine that the practice could be discovered in other vendors by looking for other vendors with a really high invoice amount

Here is our subquery it will return a list of all of vendor 34’s invoices

The “> ALL” will reduce the list returned by the subquery to the largest invoice from Vendor 34

As a result this query will look for other vendors with a invoice amount larger than Vendor 34’s largest invoice