O'Reilly logo

Oracle Database 11gR2 Performance Tuning Cookbook by Ciro Fiorillo

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Using subqueries

We often use subqueries in our SQL statements to nest more queries in one statement, using the results from an "inner" query to calculate other values.

In this recipe, we will see the use of subqueries for getting only a subset of records, demonstrating the constructs (NOT) EXISTS and (NOT) IN, highlighting the semantic difference between them (and when to choose one type of statement or the other).

How to do it...

The following steps will demonstrate the use of subqueries:

  1. Connect to the SH schema:
    CONNECT sh@TESTDB/sh
    SET AUTOT TRACE EXP STAT
    
  2. Select a table using the IN operator:
    SET AUTOT TRACE EXP STAT SELECT AMOUNT_SOLD FROM sh.SALES S WHERE S.CUST_ID IN ( SELECT C.CUST_ID FROM sh.CUSTOMERS C WHERE C.CUST_CREDIT_LIMIT IN (10000, ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required