ADS

Featured

What is Cross Apply - SQL Server

In SQL Server, you can do an INNER JOIN to join in a query result of several tables. However, when consulting another table with many
results, the query can take a long time.

With this, in SQL Server, there is the CROSS APPLY command, where you can insert a customized query to perform the query.

See the following example:
SELECT * FROM Clientes CROSS APPLY ( SELECT COUNT(*) AS Total, MAX(Data) AS DataMaxima FROM Utilizacao WHERE Utilizacao.ID_Usuario = Clientes.ID GROUP BY Utilizacao.ID_Usuario HAVING COUNT(*) > 5 ) CROSS_APELIDO
The result of this is the joining of the Customers table plus the columns of Total and DataMaxima of all customers that appear more than 5 times in the second table.

With INNER JOIN, there would be no way to insert HAVING, nor perform GROUP BY, requiring SQL Server to bring and gather all the data before the process, and probably perform several queries or sub-queries requiring more construction time.

3 comments: