Mysql – LEFT JOIN DEFAULT NULL VALUE

Storing a string of comma-separated values in a column is not an ideal design; ideally you would have a table of keywords and a 2-column map table with a row for every (E_HEADER.id,keyword_id).

However, given your current structure, the FIND_IN_SET() function should work, although this will not be the best possible performance, since a function in the where clause that takes a column as its argument will generally prevent the use of indexes for evaluating that condition -- but given your structure, you don't have an alternative.

FIND_IN_SET() returns the ordinal position of the first argument within the comma-separated string that is the second argument, or in this case "something true" if a match is found; no match returns 0 ("false") and if either argument is null, the function returns NULL.

Also, it's difficult to say without a better understanding of your structure exactly what you want the query to do, but the derived table seems unnecessary.

SELECT Q.question_id, Q.school_id
  FROM Q_MASTER Q
  LEFT JOIN E_HEADER X on X.school_id = Q.school_id AND X.low_q = 1 AND X.school_id = 6
 WHERE NOT FIND_IN_SET(Q.question_id, X.keyword);

This would select every question_id and school_id from Q_MASTER where there exists a row in E_HEADER with a matching school_id and low_q is equal to 1 and school_id is equal to 6 and where the value of Q.question_id is not found in the comma-separated string X.keyword.

Based on my interpretation of the original query, only Q_MASTER records with school_id = 6 will be returned in spite of the left join, because the join requires school_id to be identical, and will project a NULL for X.keyword otherwise; FIND_IN_SET() returns NULL if either argument is null, and the expression "NOT (NULL)" returns evaluates to NULL which will be considered untrue by the WHERE clause. The query will perform much better as an inner JOIN than a LEFT [outer] JOIN because fewer rows will need to be considered, but the ultimately correct approach requires a valid logical expression of what you actually need the server to do.

All this approach does in to make your original query a derived table then group by pub_id.

SET hive.auto.convert.join = TRUE;

SELECT max(DEAL_ID) as DEAL_ID
     , EXCHANGE_ID
     , PUB_ID
     , sum(INCREMENTAL) as INCREMENTAL
     , sum(FIRST_IMPS) as FIRST_IMPS
     , sum(SECOND_IMPS) as SECOND_IMPS
  FROM (

    SELECT First.deal_id
         , COALESCE( First.exchange_id, Second.exchange_id ) as exchange_id
         , COALESCE( First.pub_id, Second.pub_id ) as pub_id
         , COUNT(DISTINCT(case when Second.user_id is null then First.user_id else null END)) AS Incremental
        , SUM(First.imps) AS First_imps
        , SUM(Second.imps) AS Second_imps
    FROM (SELECT a.deal_id
               , a.exchange_id
               , a.pub_id
               , a.user_id
               , 1 AS imps
            FROM logs a 
           WHERE a.deal_id >= 1
            AND a.event_type = 'TRUE'
         ) First 
FULL JOIN (SELECT a.exchange_id
                , a.pub_id
                , a.user_id
               , 1 AS imps
            FROM logs a
           WHERE a.deal_id = 0
             AND a.event_type = 'TRUE'
          ) Second
      ON (   First.exchange_id = Second.exchange_id
         AND First.pub_id = Second.pub_id
         AND First.user_id = Second.user_id
         )
   GROUP BY First.deal_id
          , COALESCE( First.exchange_id, Second.exchange_id )
          , COALESCE( First.pub_id, Second.pub_id )

  ) group by pub_id, exchange_id

;