Performance : INTERSECT or join ?

CogitoErgoSum · #1 (**permalink**) 10-01-09, 09:55

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.