SQL Query to Find Missing Rows Between Two Related Tables

SQL Query to find missing rows between two related tables

SELECT A.ABC_ID, A.VAL FROM A WHERE NOT EXISTS 
   (SELECT * FROM B WHERE B.ABC_ID = A.ABC_ID AND B.VAL = A.VAL)

or

SELECT A.ABC_ID, A.VAL FROM A WHERE VAL NOT IN 
    (SELECT VAL FROM B WHERE B.ABC_ID = A.ABC_ID)

or

SELECT A.ABC_ID, A.VAL LEFT OUTER JOIN B 
    ON A.ABC_ID = B.ABC_ID AND A.VAL = B.VAL FROM A WHERE B.VAL IS NULL

Please note that these queries do not require that ABC_ID be in table B at all. I think that does what you want.

Compare Two Tables to Find Missing Rows

SELECT DISTINCT
  i.inv_id, p.name, p.prod
FROM #inv i
JOIN #prod p
  ON p.prod = i.prod
LEFT JOIN #inv v
  ON v.inv_id = i.inv_id AND
     v.name = p.name AND
     v.prod = p.prod
WHERE v.inv_id IS NULL;

Compare two SQL tables and return missing ids?

There are several ways to skin this cat:

SELECT    table1.ID
FROM      table1
WHERE     table1.ID NOT IN(SELECT table2.ID FROM table2)

Or you could use a left outer join:

SELECT          table1.ID
FROM            table1
LEFT OUTER JOIN table2 ON table1.ID = table2.ID
WHERE           table2.ID IS NULL

Find missing records with grouping

cross join calendar to distinct items and left join the items table to get the missing rows.

select i.itemid,c.month_last_day
from calendar c 
cross join (select distinct itemid from items) i
left join items it on it.itemid = i.itemid and c.month_last_day = it.dt
where it.dt is null 

BigQuery SQL: How to find missing Values on comparing two tables over date range?

Using a calendar table approach:

SELECT s.Store AS MissingStore, d.Report_Date
FROM (SELECT DISTINCT Store FROM tab1) s
CROSS JOIN (SELECT DISTINCT Report_Date FROM tab1) d
INNER JOIN tab2 t2
    ON s.Store = t2.Store
LEFT JOIN tab1 t1
    ON t1.Store = s.Store AND t1.Report_Date = d.Report_Date
WHERE t1.Store IS NULL;

---

Related Topics