Home » SQL Server Basics » SQL Server CROSS APPLY

SQL Server CROSS APPLY

Summary: in this tutorial, you will learn how to use the SQL Server CROSS APPLY clause to perform an inner join a table with a table-valued function or a correlated subquery.

Introduction to the SQL Server CROSS APPLY clause #

The CROSS APPLY clause allows you to perform an inner join a table with a table-valued function or a correlated subquery.

In SQL Server, a table-valued function is a user-defined function that returns multiple rows as a table.

The CROSS APPLY clause works like an INNER JOIN clause. But instead of joining two tables, the CROSS APPLY clause joins a table with a table-valued function or a correlated subquery.

Here’s the basic syntax of the CROSS APPLY clause:

SELECT
  select_list
FROM
  table1
  CROSS APPLY table_function(table1.column) AS alias;Code language: SQL (Structured Query Language) (sql)

In this syntax:

  • table1 is the main table from which you want to join.
  • table_function: is the table-valued function to apply to each row. Alternatively, you can use a correlated subquery.
  • column: is the column from table1 that will be passed as a parameter to the table_function.
  • alias is the alias for the result set returned by the table_function.

The CROSS APPLY clause will apply the table_function to each row from the table1. If you use a correlated subquery, the CROSS APPLY clause will execute it for each row from the table1.

In practice, you should use the CROSS APPLY clauses when you cannot use INNER JOIN clauses.

SQL Server CROSS APPLY clause examples #

Let’s explore some useful use cases of the CROSS APPLY clause.

We’ll use the production.categories and production.products tables from the sample database for the demonstration: