This objective may include but is not limited to: CROSS APPLY, OUTER APPLY, all join types, UNION, UNION ALL, INTERSECT, EXCEPT
Datasets tend to reside in different tables. Usually you need to find some technique to create a unified dataset from the different tables. The combining functions provides just this.
We’ll discuss the following clauses here: JOIN, APPLY, UNION, INTERSECT, EXCEPT.
Let’s start with the simplest ones. A JOIN (the types are LEFT (OUTER) JOIN, RIGHT (OUTER) JOIN, (INNER) JOIN) is simply joins two or more tables, which have the same values in one or more of their columns. INNER JOIN returns those rows where both tables have the same values. LEFT OUTER returns all values from the left side of the operation, right does the same with the right side. There is also a CROSS JOIN, which results in the Cartesian product of the tables queried.
UNIONs can be used to merge two result sets into one, thus the products of more select statements results in one dataset. However, you must make sure that you define UNION between the same number of columns, with compatible data types. Be aware of that the column titles are from the first select statement, so you should define aliases in the first select query. The ALL keyword after the UNION specifies that even duplicate rows should be returned.
There are two more methods of combining datasets. The first one is using the INTERSECT and EXCEPT clauses. INTERSECT returns only those rows which are present in both result sets (defined as the intersection of them), EXCEPT returns those which are present in only one result set.
The last way to combine is the APPLY operator. It has two forms: OUTER APPLY and CROSS APPLY. These operators lets you use columns from the left-side table to be passed as parameters for a table-valued function of the right side. CROSS APPLY returns every row for which the TVF produces results, while OUTER APPLY, just like an OUTER JOIN, returns all rows, even those for which there’s no result.