Home » Microsoft » MB6-886 » Which join predicate should you use?
You are a database developer. You plan to design a database solution by using SQL Server 2008. You have a database that contains a table and a table-valued function. The table-valued function accepts the primary key from the table as a parameter.
You plan to write a query that joins the table to the results of the table-valued function. You need to ensure that only rows from the table that produce a result set from the table-valued function are returned.
Which join predicate should you use?
A. CROSS APPLY
B. OUTER APPLY
C. INNER JOIN
D. LEFT OUTER JOIN
Correct Answer: A
Explanation/Reference:
The APPLY operator allows you to invoke a table-valued function for each row returned by an outer table expression of a query. The table-valued function acts as the right input and the outer table expression acts as the left input. The right input is evaluated for each row from the left input and the rows produced are combined for the final output. The list of columns produced by the APPLY operator is the set of columns in the left input followed by the list of columns returned by the right input.
There are two forms of APPLY: CROSS APPLY and OUTER APPLY. CROSS APPLY returns only rows from the outer table that produce a result set from the table-valued function. OUTER APPLY returns both rows that produce a result set, and rows that do not, with NULL values in the columns produced by the tablevalued function.
