How to find if data is missing from a table using LEFT JOIN

Question

I need to join two tables to determine if VINs are missing from a specific table.

I am working with LEFT JOIN. Unfortunately, I am only able to pull the data that is in both tables.

SELECT vin 
FROM   table1.vins 
       LEFT JOIN table2.tbl2_vins 
              ON table2.tbl2_vins.vin = table1.vins.vin 
WHERE  table2.tbl2_vins = ( "5yfbprhe2jp757613" );

I am trying to figure out how to check specific VINs that are in the table2 vin column, but not in the table1 vin column. I think my screwing up is with the '=', but can't determine best way to do this query.

Please provide sample data and desired results. What you are trying to do is unclear. Why not just query the second table if that is what you want to know about? — Gordon Linoff, Dec 31 '18 at 23:04
In your where clause "WHERE table2.tbl2_vins" you didn't specify the column. — Tarreq, Dec 31 '18 at 23:04

score 1 · Answer 1 · answered Dec 31 '18 at 23:07

1

SELECT vin 
FROM   table2.vins 
       LEFT JOIN table1.tbl1_vins 
              ON table2.tbl2_vins.vin = table1.vins.vin 
WHERE  table1.tbl1_vins = is null;

Other ways are to use "minus" and "not in (select ...)".

answered Dec 31 '18 at 23:07

Vladimir.V.Bvn

1,050
1
13
13

Gordon Linoff · Answer 2 · 2018-12-31T23:09:21.600

Normally, the answer to this type of question is to move the condition to the on clause.

However, all your columns appear to be the same. This may do what you want:

SELECT t1.vin, (t2.vin is not null) as present_in_t2
FROM table1.vins t1 LEFT JOIN
     table2.tbl2_vins t2
     ON t2.vin = t1.vin
WHERE t1.vin IN ( '5yfbprhe2jp757613' );

I am trying to figure out how to check specific VINs that are in the table2 vin column, but not in the table1 vin column.

If this is what you want, then just use not exists:

SELECT t2.vin
FROM table2.tbl2_vins t2
WHERE NOT EXISTS (SELECT 1
                  FROM table1.vins t1 
                  WHERE t2.vin = t1.vin
                 ) AND
      t2.vin IN ( '5yfbprhe2jp757613' );

How to find if data is missing from a table using LEFT JOIN

2 Answers2