Check out the solution in the video TSQL: Find Values In One Table Not In Another Table. In the example, we see how to identify data in one table that does not exist in another table. This may be extremely useful in downstream data where we find missing data (by counts). We may also use this in situations where one environment that must be a clone of another environment does not match.
Some questions and considerations that we look at in the video along with what we want to consider when using this:
- In most cases, we do not want to compare across different functional environments, such as development to production unless the table we're looking at would be a clone.
- When making a table clone for backup purposes, this can be extremely useful to quickly validate that we've made a successful clone.
- What are we still doing when comparing the tables as far as the basis? What would happen if our data don't have this type of identifier? How would we compare then?
For mastering data transformation from one form to another form, check out the highest-rated Automating ETL course on Udemy. For a coupon to the course, check out the trailer video on the channel SQL In Six Minutes.
In most cases, missing data may not come through filters. This may be appropriate, depending on what we'd expect or what our design is supposed to achieve. In the best case scenario, this is expected because we don't want every value in the second table that's in the first (our comparison would validate this). In the worst case scenario, we may find our development from the first table to the second table is erroneous (if they should match). If we're comparing a source to a destination, it may be that we apply data rules to the data from the source - this is an example where we may want different data in the two tables. In some cases, the source data may have erroneous data that we have to filter and while it's worth logging these data and discussing this with the source, we would expect the tables to differ (we could compare the erroneous data with a unique id to validate this).