Presto/Mysql join validation



  • I have 2 tables, and the CASE statement below should only evaluate to 1/true if the record with the same lead_id exists in both tables. Here I have 2 ways of achieving this but just want to verify if its in fact the most optimal way of achieving this and which one is the better option here? The lead_id in t1 is the primary key and will always exist. I need to verify if its moved into the 2nd table

    Query 1

    SELECT
       CASE
          WHEN
             t1.lead_id = t2.lead_id 
          THEN
             1 
          ELSE
             0 
       END
       AS valid 
    FROM
       table1 t1 
       LEFT JOIN
          table2 t2 
          ON t1.lead_id = t2.lead_id
    

    Query 2

    SELECT
       CASE
          WHEN
             t2.lead_id IS NOT NULL 
             AND t2.lead_id != 0 
          THEN
             1 
          ELSE
             0 
       END
       AS valid 
    FROM
       table1 t1 
       LEFT JOIN
          table2 t2 
          ON t1.lead_id = t2.lead_id
    


  • You can try something like this:

    SELECT t2.lead_id IS NOT NULL AS '1'
    FROM table1 AS t1 
    LEFT JOIN table2 AS t2 ON t1.lead_id = t2.lead_id ;
    

    Tested on my server:

    SELECT vs.status  IS NOT NULL AS '1'
    FROM vicidial_log vl
    LEFT JOIN vicidial_statuses vs ON vl.status = vs.status
    WHERE campaign_id = 'my_campaign'
    

    Result:

    1
    1
    1
    1
    0
    1
    1
    0
    1
    1
    1
    1
    1
    1
    1
    1
    1
    1
    1
    0
    1
    1
    1
    1
    1
    0
    0
    



Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2