Comparison and creating new column Table A

    &gt; - Product#|Customer|state|Type   
      Glasses |mr. Xyz | MN |Instore    
      Glasses |mr. abc | MN |Instore    
  perishables|mr. abc12| TN |Instore 
 N-perishables|mr. abc122| GN |Instore


Table B

&gt; - Product#|Name|state|Type   
  Glasses |mr. fff | MN |online    
  Glasses |mr. abc | MN |online    
perishables|mr. abc12 | TN |online 
 Non-perishables|mr. abc122 | GN |online

    `


Trying to create new column and then do comparing?
Condition, if the type is in-store they are never new customer, if they are online they could be new customer only if their record do not exist in In-store Type.

o/p

Type|customer| New Customer?
Instore|mr. Xyz  |No
Instore|mr. abc  |No
Instore|mr. abc12|No
Instore|mr. abc122|No
Online| mr.fff    |Yes -"as it's record is not available  in-store type, hence it will be labelled as new customer"


Query tried:

select 
       `Customer`, 
    CASE
    WHEN `customer` in (select `Customer` from `Table B`)  THEN 'Yes'
    ELSE 'NO'
END AS 'new customer'
FROM Table A`


Please give me optimized query, as its huge data in millions.
IN ( SELECT ... ) is often an inefficient way to design a query.  There are two alternatives below.  Also, your IN was asking to fetch the entire array; this is probably causing terrible performance.  You only need to check for the "existence" of one customer.

Note that each formulation checks for one "customer" in Table B and does not fetch the entire table (as is done with your IN ( SELECT ... )).

Two approaches:

SELECT  Customer,
        IF(b.customer IS NOT NULL, 'Yes', 'No') AS "new customer"
    FROM  `Table A` AS a
    LEFT JOIN  `Table B` AS b USING(customer);

SELECT  Customer,
        IF(EXISTS (
                SELECT  1
                    FROM  `Table B`
                    WHERE  customer = a.customer ) , 'Yes', 'No'
          ) AS "new customer"
    FROM  `Table A` AS a;


LEFT implies that the row may be missing from Table B.  This is exactly what you want in order to produce "No".

EXISTS is a "semi-join".  It stops when it finds success; this can be handy if there could be multiple matching rows (which is not your case).