Mysql – Find missing entries across several tables by SQL call

In addition to Rolando's answer, please note that a TIMESTAMP column is timezone-aware, while DATETIME is not.

If you don't have multiple time zones, then you're OK; but still, if ever you wanted to convert to other timezones, the TIMESTAMPDIFF calculation will provide with corrupted results.

Without table structure, it's not easy to write code but here is a simplified query:

SELECT 
    c.*, i.*,
    r.max_date
FROM 
    customer AS c
  LEFT JOIN                                        -- or just JOIN 
    ( SELECT customernumber, 
             MAX(revenue_date) AS max_date
      FROM revenue
      GROUP BY customernumber
    ) AS r
      ON  r.customernumber = c.customernumber
  LEFT JOIN                                        -- or just JOIN
    invoice AS i
      ON  i.customernumber = c.customernumber
      AND i.invoice_date >= r.max_date ;

The above is standard SQL and will work in MySQL as in all other DBMS. For more complex situations though, where LATERAL or similar syntax can be used to provide more elegant (or more efficient) SQL, MySQL is not helping. It lacks many of the standard SQL syntax (like common table expressions, table value constructors, window functions, LATERAL, CHECK constraints and probably some other I forget).

If you want to simulate (SQL-Server's) OUTER APPLY, (the ISO/ANSI) LATERAL or (DB2's) TABLE syntax, it becomes (bad news) quite complicated but (good news) usually quite efficient with proper indexing. This is mainly useful when you have a small "base" table (the customer in this case) or you want to limit the query to only a few customers with a WHERE clause. While the query above with the GROUP BY will have to find the MAX(revenue_date) for all customers (and that means a full table or a full index scan in revenue), the following one will only find the max for the few selected customers (so a few index seeks):

SELECT 
    c.*, i.*,
    r.revenue_date AS max_date
FROM 
    customer AS c
  LEFT JOIN
    revenue AS r
      ON  r.customernumber = .customernumber
      AND r.PK = 
          ( SELECT ri.PK
            FROM revenue AS ri
            WHERE ri.customernumber = c.customernumber
            ORDER BY ri.revenue_date DESC
              LIMIT 1
          ) 
  LEFT JOIN                                        
    invoice AS i
      ON  i.customernumber = c.customernumber
      AND i.invoice_date >= r.revenue_date 
WHERE
    <some conditions on the customer table> ;

• An advantage is that you can have any column of the revenue table in the final SELECT list.
• There are several improvements/variations, like if the (customernumber, revenue_date) is unique, you can use the revenue_date instead of the primary key for the join.
• If you don't want one row only but several (a TOP n query), it gets quite complicated but can still be done.