Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2009
    Posts
    14

    Question Unanswered: Performance : INTERSECT or join ?

    Will INTERSECT option provide better performance as opposed to the 'join' shown below ?

    Code:
    SELECT COUNT(COL1)
      FROM
      (SELECT A.COL1
         FROM XXL_TABLE A,
              XL_TABLE B
        WHERE A.SOME_COL1 = B.SOME_COL1
          AND MONTH(A.DATE_COL) = 8
          AND A.SOME_COL2 BETWEEN 100 AND 200
       INTERSECT
       SELECT A.COL1
         FROM XXL_TABLE A,
              XL_TABLE B
        WHERE A.SOME_COL1 = B.SOME_COL1
          AND MONTH(A.DATE_COL) = 9
          AND A.SOME_COL2 > 200
       )
    Code:
    SELECT COUNT(DISTINCT COL1)
      FROM
      (SELECT A.COL1
         FROM XXL_TABLE A,
              XL_TABLE B
        WHERE A.SOME_COL1 = B.SOME_COL1
          AND MONTH(A.DATE_COL) = 8
          AND A.SOME_COL2 BETWEEN 100 AND 200
       ) X,
      (SELECT A.COL1
         FROM XXL_TABLE A,
              XL_TABLE B
        WHERE A.SOME_COL1 = B.SOME_COL1
          AND MONTH(A.DATE_COL) = 9
          AND A.SOME_COL2 > 200
       ) Y
    WHERE X.COL1 = Y.COL1
    Maybe, the above information is not enough. But, I am interested in knowing the difference between the INTERSECT and join options.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    If you cogito hard enough, you can use the explain facility to answer that question.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Oct 2009
    Posts
    14
    ok, will cogito some more

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •