Presto/Mysql join validation

by

The question:

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

The Solutions:

Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.

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>);


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 Comment