In some cases, we need to combine the result of two tables where data types may be similar or identical. We could combine the data in one table using INSERT syntaxes, but with UNION ALL or UNION, we can achieve the same result instead of creating a new table. In the video, SQL Basics: How To Use UNION ALL and Why we see several examples of this in action. One example we union tables with the same data type and in another example, we union two columns that have different data types.
Note the tables that we're using and what values are identical based on the column names and what values differ based on the column names. We'll be using these tables throughout these videos. We'll notice that if we union two data types that are not similar (varchar and int), we get a clash. This is because these are not like data types; we can union an int and a smallint because they are like data types, but data types that do not fall into these categories will clash and cause us to get errors. What if we need to union two different data types? We can cast or convert the data types to match each other, such as casting an int to a varchar so that we can union two varchars. Keep in mind that we don't need to do this if two data types fall into the same category (ie: different lengths of ints, varchars, etc). In some cases, we may do the casting or converting of data types in the application layer; however, this means that we won't be able to use UNION or UNION ALL as we'll still have a clash.
One applied example of using unions is error logs. Often we have error logs for the database, application and other possible layers. It's useful to identify when an error happened and how that error translated across layers. Unioning tables with the errors by combining the error and date can be useful when we order by the time of the error. We can often find out where the error originated and how it impacted all the layers of our application or service.