Presto/Mysql join validation

All we need is an easy explanation of the problem, so here it is.

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

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

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

Method 2

even shorter…

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

A boolean expression evaluates as 0 (false) or 1 (true).

If really want the ids that match instead of 0/1, even simpler:

SELECT t1.lead_id
    FROM table1 AS t1 
    JOIN table2 AS t2 ON t1.lead_id = t2.lead_id ;

In all cases, table2 needs an index starting with lead_id. (Remember that the PRIMARY KEY is an index.

Method 3

If you just want to have an extra field where you see if the entries from t1 are in t2, that is the easiest way. Personally I would have done it with an if statement like so:

SELECT
  t1.id,
  IF(t2.id IS NULL,0,1) 
FROM t1 
  LEFT JOIN t2 
  ON t1.id=t2.id;

If you just want to make sure a specific value is in a certain table, you can use the EXISTS() function like so:

SELECT EXISTS(SELECT * FROM t2 WHERE id=<value>);

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply