Get the difference between two SELECT statements

I am trying to get the difference between 2 select statements.

Here is the first select:

SELECT 1

SELECT *
FROM h_log_material
LEFT JOIN h_log_stkpnl
ON  h_log_stkpnl.开发者_JAVA百科MODULE_UNIQUE_ID = h_log_material.MODULE_UNIQUE_ID 
where 
h_log_material.WO_ID='E11TMB7M05'
and h_log_material.mtrl_type='BL'

Output is 4917

SELECT 2

Here is second select:

SELECT *
FROM h_log_material
LEFT JOIN h_log_stkpnl
ON  h_log_stkpnl.MODULE_UNIQUE_ID = h_log_material.MODULE_UNIQUE_ID 
where 
h_log_material.WO_ID='E11TMB7M05'
and h_log_material.mtrl_type='BL'
and h_log_stkpnl.op_id like '%STOCK-IN%'

Output is 4870

I would like to get the difference SELECT 1 - SELECT 2

As output I need rows which are not in select 2.

I tried this but it returns 0.

SELECT *
FROM h_log_material
LEFT JOIN h_log_stkpnl
ON  h_log_stkpnl.MODULE_UNIQUE_ID = h_log_material.MODULE_UNIQUE_ID 
where 
h_log_material.WO_ID='E11TMB7M05'
and h_log_material.mtrl_type='BL'
and not exists
(SELECT *
FROM h_log_material
LEFT JOIN h_log_stkpnl
ON  h_log_stkpnl.MODULE_UNIQUE_ID = h_log_material.MODULE_UNIQUE_ID 
where 
h_log_material.WO_ID='E11TMB7M05'
and h_log_material.mtrl_type='BL'
and h_log_stkpnl.op_id like '%STOCK-IN%')

The 2nd SELECT looks like a subset of the first so you can do

SELECT COUNT(*) - COUNT(CASE
                          WHEN h_log_stkpnl.op_id LIKE '%STOCK-IN%' THEN 1
                        END)
FROM   h_log_material
       LEFT JOIN h_log_stkpnl
         ON h_log_stkpnl.MODULE_UNIQUE_ID = h_log_material.MODULE_UNIQUE_ID
WHERE  h_log_material.WO_ID = 'E11TMB7M05'
       AND h_log_material.mtrl_type = 'BL'  

Edit

Following your clarification that you need columns not COUNT try this one out

SELECT *
FROM   h_log_material
       LEFT JOIN h_log_stkpnl
         ON h_log_stkpnl.MODULE_UNIQUE_ID = h_log_material.MODULE_UNIQUE_ID
            AND h_log_stkpnl.op_id LIKE '%STOCK-IN%'
WHERE  h_log_material.WO_ID = 'E11TMB7M05'
       AND h_log_material.mtrl_type = 'BL'
       AND h_log_material.MODULE_UNIQUE_ID IS NULL  

Your first query is doing a LEFT JOIN but the WHERE clause on the second one converts it into an inner join effectively. So the difference between the queries should be those rows where either there is no match in h_log_stkpnl or the match isn't LIKE '%STOCK-IN%' (I think!)

Have you tried

and h_log_stkpnl.op_id not like '%STOCK-IN%'

Unless there are null values (and those can be compensated for) that should be the difference.

You don't specify your platform but if you're running SQL 2008 you can use EXCEPT and INTERSECT to compare the results of two queries.

try it

   SELECT h_log_material.field1,h_log_material.field2
    FROM h_log_material
    LEFT JOIN h_log_stkpnl
    ON  h_log_stkpnl.MODULE_UNIQUE_ID = h_log_material.MODULE_UNIQUE_ID 
    where 
    h_log_material.WO_ID='E11TMB7M05'
    and h_log_material.mtrl_type='BL'

    minus 

    SELECT h_log_material.field1,h_log_material.field2
    FROM h_log_material
    LEFT JOIN h_log_stkpnl
    ON  h_log_stkpnl.MODULE_UNIQUE_ID = h_log_material.MODULE_UNIQUE_ID 
    where 
    h_log_material.WO_ID='E11TMB7M05'
    and h_log_material.mtrl_type='BL'
    and h_log_stkpnl.op_id like '%STOCK-IN%'