Re: Interview Question on Subqueries vs Regular joins

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Mon, 11 Feb 2008 09:48:31 -0800 (PST)
Message-ID: <1df0981c-4efd-4f73-84bb-6c52b308b227@u10g2000prn.googlegroups.com>

On Feb 11, 11:44 am, leonard.reinst..._at_gmail.com wrote:

> Hi!
>
> I was asked the following question at a technical interview: when
> would you use subqueries and when would you use regular joins (pros
> and cons of each approach in terms of design and performance)?
>
> I have read several articles on the subject but could not find a good
> answer to this question.
>
> Any help would be appreciated.
>
> Thanks!

This technical interview question does not make sense. Oracle _may_ automatically transform a subquery into an inline view, which then is joined to the rest of the query as a "regular join".

Consider the following example, which may be thought of as a customer order (T1), the lines for the customer order (T2), and a table containing a list of parts that are not in stock (T3): CREATE TABLE T1(
  MY_ID VARCHAR2(30),
  MY_DATE DATE,
  CUSTOMER_ID VARCHAR2(15),
  PRIMARY KEY (MY_ID)); CREATE TABLE T2(
  T1_MY_ID VARCHAR2(30),
  LINE_NO NUMBER(10),
  PART_ID VARCHAR2(30),
  ORDER_QTY NUMBER(22,4),
  LINE_DATE DATE,
  PRIMARY KEY (T1_MY_ID,LINE_NO)); CREATE TABLE T3(
  PART_ID VARCHAR2(30),
  BACK_ORDER_DATE DATE,
  PRIMARY KEY (PART_ID)); INSERT INTO T1 VALUES(
'001',

  TO_DATE('01-JAN-2006','DD-MON-YYYY'),
'C001');

INSERT INTO T1 VALUES(
'002',

  TO_DATE('01-JAN-2006','DD-MON-YYYY'),
'C002');

INSERT INTO T1 VALUES(
'003',

  TO_DATE('01-JAN-2006','DD-MON-YYYY'),
'C003');

INSERT INTO T1 VALUES(
'004',

  TO_DATE('01-JAN-2006','DD-MON-YYYY'),
'C001');

INSERT INTO T2 VALUES(
'001',

  1,
'ABC',

  5,
  TO_DATE('01-JAN-2006','DD-MON-YYYY')); INSERT INTO T2 VALUES(
'001',

  2,
'ABC',

  5,
  TO_DATE('05-JAN-2006','DD-MON-YYYY')); INSERT INTO T2 VALUES(
'001',

  3,
'ABC',

  10,
  TO_DATE('10-JAN-2006','DD-MON-YYYY')); INSERT INTO T2 VALUES(
'002',

  1,
'ABCD',

  20,
  TO_DATE('05-JAN-2006','DD-MON-YYYY')); INSERT INTO T2 VALUES(
'004',

  1,
'ABCE',

  5,
  TO_DATE('15-JAN-2006','DD-MON-YYYY')); INSERT INTO T2 VALUES(
'004',

  2,
'ABC',

  10,
  TO_DATE('20-JAN-2006','DD-MON-YYYY')); INSERT INTO T3 VALUES(
'ABCD',

  TO_DATE('15-FEB-2006','DD-MON-YYYY')); INSERT INTO T3 VALUES(
'ABCE',

  TO_DATE('20-FEB-2006','DD-MON-YYYY')); INSERT INTO T3 VALUES(
'ABCF',

  TO_DATE('25-FEB-2006','DD-MON-YYYY')); A standard query to combine the header record with the line detail record (note that header record 003 will not be returned), a "regular join".
SELECT

  T1.MY_ID,
  T1.MY_DATE ORDER_DATE,
  T1.CUSTOMER_ID,
  T2.LINE_NO,
  T2.PART_ID,
  T2.ORDER_QTY,
  T2.LINE_DATE

FROM
  T1,
  T2
WHERE
  T1.MY_ID=T2.T1_MY_ID;

Now, let's add a subquery to return only those line detail records that are not back ordered:
SELECT

  T1.MY_ID,
  T1.MY_DATE ORDER_DATE,
  T1.CUSTOMER_ID,
  T2.LINE_NO,
  T2.PART_ID,
  T2.ORDER_QTY,
  T2.LINE_DATE

FROM
  T1,
  T2
WHERE
  T1.MY_ID=T2.T1_MY_ID
  AND T2.PART_ID NOT IN (
    SELECT
      PART_ID
    FROM
      T3);

Oracle may automatically transform the above into something like this: SELECT

  T1.MY_ID,
  T1.MY_DATE ORDER_DATE,
  T1.CUSTOMER_ID,
  T2.LINE_NO,
  T2.PART_ID,
  T2.ORDER_QTY,
  T2.LINE_DATE

FROM
  T1,
  T2,
  (SELECT
    PART_ID
  FROM
    T3) T3
WHERE
  T1.MY_ID=T2.T1_MY_ID
  AND T2.PART_ID=T3.PART_ID(+)
  AND T3.PART_ID IS NULL; Or, it may transform it into something else.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Mon Feb 11 2008 - 11:48:31 CST