CTE with TEMP table

Question

What are the other ways to achieve the below results?

-- Run pre-results and store it in temp table

CREATE TABLE #TEMP_SAVE (USERID VARCHAR(100))  
INSERT INTO  #TEMP_SAVE  
SELECT USERID FROM IMSIDENTITYPRE  
EXCEPT  
SELECT USERID FROM PPPRETEST  
  

-- Run Post results and filter results out with TEMP_Save results

;WITH CTE(USERID)  
AS   
(SELECT ims.USERID FROM IMSIdentityPOST ims  
EXCEPT  
SELECT pp.USERID FROM PPposttest pp)  
SELECT * FROM CTE WHERE  USERID  IN (SELECT USERID FROM #TEMP_SAVE)  

Answer 1

Rather than using the temp table, you could have two CTEs:

   ;WITH CTE(USERID) AS (  
      SELECT ims.USERID FROM IMSIdentityPOST ims  
      EXCEPT  
      SELECT pp.USERID FROM PPposttest pp  
   ), TEMP_SAVE(USERID) AS (  
      SELECT USERID FROM IMSIDENTITYPRE  
      EXCEPT  
      SELECT USERID FROM PPPRETEST  
   )  
   SELECT * FROM CTE WHERE USERID IN (SELECT USERID FROM TEMP_SAVE)  

You could also make use of the INTERSECT operator:

   (SELECT ims.USERID FROM IMSIdentityPOST ims  
    EXCEPT  
    SELECT pp.USERID FROM PPposttest pp  
   )  
   INTERSECT  
   (  
      SELECT USERID FROM IMSIDENTITYPRE  
      EXCEPT  
      SELECT USERID FROM PPPRETEST  
   )  

Answer 2

Hi @Santhosh Kunder

In addition to the two methods proposed by ErlandSommarskog, I have come up with the following solutions.

In addition to CTE, you can also use derived tables.

select * from (SELECT ims.USERID FROM IMSIdentityPOST ims  
               EXCEPT  
               SELECT pp.USERID FROM PPposttest pp) as D  
   WHERE USERID IN (  
   SELECT USERID FROM (SELECT USERID FROM IMSIDENTITYPRE  
                       EXCEPT  
                       SELECT USERID FROM PPPRETEST) as P);  

You can also use in and not in after the where condition.

select * from (SELECT ims.USERID FROM IMSIdentityPOST ims  
               EXCEPT  
               SELECT pp.USERID FROM PPposttest pp) as D  
   WHERE USERID IN (SELECT USERID FROM IMSIDENTITYPRE)   
     and USERID not IN (SELECT USERID FROM PPPRETEST);  

Best regards,
Percy Tang

----------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.