Advanced query techniques with APPLY operators in SQL Server

SQL Server is one of the most popular database management systems, and for a good reason. It provides extensive functionality for data storage, retrieval, management, and analysis. One of the core functionalities in SQL Server is the ability to perform JOIN operations, which allow for combining rows from two or more tables based on related columns. JOIN operations come in various types, such as INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN, each serving specific purposes in data retrieval.

Although JOIN operations are helpful, they have limitations that can pose significant challenges when dealing with complex queries that require access to multiple rows from a related table or performing intricate operations. To address these limitations, SQL Server introduces the APPLY operator, which provides a more flexible and powerful way to handle such scenarios. In this article, we will talk about CROSS APPLY and OUTER APPLY and demonstrate their functionality with the help of dbForge Studio for SQL Server — the perfect GUI tool that can make any database experience better.

Understanding APPLY operators

For starters, let us take a closer look at the basics of the APPLY operator. Unlike traditional JOINs, which combine rows from two tables based on a specific condition, APPLY allows you to run a function for each row, handling more complex queries. The main difference is that APPLY can return different results for each outer table row, while JOINs cannot. CROSS APPLY performs functions similar to INNER JOIN, filtering out rows from the outer table that do not produce a result. At the same time, OUTER APPLY is akin to OUTER JOIN. It includes all rows from the outer table regardless of whether they produce any result at all.

In the upcoming sections of this article, we will take a closer look at the syntax and real-life applications of different APPLY types within the convenient dbForge Studio environment. So, stay tuned!

CROSS APPLY vs. OUTER APPLY

CROSS APPLY and OUTER APPLY operators can serve as perfect tools for different scenarios during your daily SQL Server database management routine:

Hands-on examples

In order to understand the topic better, let us walk through some hands-on examples of implementing the APPLY operator using dbForge Studio for SQL Server. For these purposes, we will use the Library database and fill it with test data:

1. Open dbForge Studio and click New SQL.

2. Copy and paste the following script into the SQL window that opens. Then, click Execute.

CREATE DATABASE Library
GO

USE Library;

CREATE TABLE Author (
  ID INT PRIMARY KEY
 ,AuthorName VARCHAR(50) NOT NULL
)

CREATE TABLE Book (
  ID INT PRIMARY KEY
 ,BookName VARCHAR(50) NOT NULL
 ,Price INT NOT NULL
 ,AuthorID INT NOT NULL
)

USE Library;

INSERT INTO Author
  VALUES (1, 'Jaheira Bronte'),
  (2, 'Dante Ancunin'),
  (3, 'Halsin de Balzac'),
  (4, 'Wyll Shakespeare'),
  (5, 'Gale Hemingway'),
  (6, 'Laezel Orwell'),
  (7, 'Jenevelle von Goethe'),
  (8, 'Minthara Christie'),
  (9, 'Karlach Twain'),
  (10, 'Minsc de Cervantes')

INSERT INTO Book
  VALUES (1, 'Circus of the Last Days', 500, 1),
  (2, 'Encyclopaedia Apotheca', 300, 3),
  (3, 'Fine at Home: A Cookery Book', 700, 5),
  (4, 'Flow of the Chionthar', 400, 4),
  (5, 'Gnomefather', 650, 9),
  (6, 'House of Hope', 400, 10),
  (7, 'Judge of the Damned', 400, 7),
  (8, 'Lunar Dark Lies', 400, 2),
  (9, 'One Night in Nashkel', 100, 10),
  (10, 'Queen of Ravens', 400, 8);

Using JOIN to join tables

With our test database ready, we can proceed with our experiments. First, let us see how the INNER JOIN operator retrieves matching rows from both Author and Book tables:

SELECT
  A.AuthorName
 ,B.ID
 ,B.BookName
 ,B.Price
FROM Author A
INNER JOIN Book B
  ON A.ID = B.AuthorID;

As expected, only the records from the Author table with a matching row in the Book table have been selected.

In order to retrieve everything, you can use a LEFT JOIN:

SELECT
  A.AuthorName
 ,B.ID
 ,B.BookName
 ,B.Price
FROM Author A
LEFT JOIN Book B
  ON A.ID = B.AuthorID;

Now, all of the records from Author are here before our eyes regardless of there being any matching rows in Book.

Joining table-valued functions

We just witnessed how JOIN operators combine the results from two tables. However, as useful as they are, you cannot use them to join a table function with a table.

The following script creates a function that accepts author ID as a parameter and retrieves all the books written by that author:

CREATE FUNCTION fnGetBooksByAuthorId (@AuthorId INT)
RETURNS TABLE
AS
  RETURN
  (
  SELECT
    *
  FROM Book
  WHERE AuthorID = @AuthorId
  );

To test the above function, let us select all the books by Minsc de Cervantes that goes by ID 10:

SELECT
  *
FROM fnGetBooksByAuthorId(10);

There will be two books in the output:

To join Author with fnGetBooksByAuthorId, use the below command:

SELECT
  A.AuthorName
 ,B.ID
 ,B.BookName
 ,B.Price
FROM Author A
INNER JOIN fnGetBooksByAuthorId(A.ID) B
  ON A.ID = B.AuthorID;

Here, the INNER JOIN operator is supposed to merge a physical table with the function. However, what we receive is an error.

Understanding CROSS APPLY operators

To make it work, we are going to substitute JOIN with the APPLY operator.

SELECT
  A.AuthorName
 ,B.ID
 ,B.BookName
 ,B.Price
FROM Author A
CROSS APPLY fnGetBooksByAuthorId(A.ID) B;

Now we are getting somewhere! All the IDs from Author are passed to fnGetBooksByAuthorId. For each author ID in the table, the function returns the corresponding books. Then, the results from the table function are joined with the ones retrieved from the table.

Understanding OUTER APPLY operators

Our last experiment for today will be adding the OUTER APPLY operator to the equation. Similarly to OUTER JOIN, it will retrieve all the rows from both the physical table and the output of the function.

SELECT
  A.AuthorName
 ,B.ID
 ,B.BookName
 ,B.Price
FROM Author A
OUTER APPLY fnGetBooksByAuthorId(A.ID) B;

Try it yourself with dbForge Studio

Even though SQL Server Management Studio (SSMS) is the most popular and familiar tool that allows you to work with SQL Server databases, it is not the only one. Moreover, in the continuously evolving world of database development, administration, and management, new GUIs keep appearing like mushrooms after the rain. How do you choose the tool that is perfect for you in this variety?

Let us compare dbForge Studio for SQL Server with SSMS so that you can make an informed decision on which solution best aligns with your daily requirements:

Take advantage of dbForge Studio for SQL Server by downloading a free fully-functional 30-day trial version and installing it on your computer. With a huge pack of advanced features and intuitive GUI, this all-in-one MSSQL tool can maximize productivity and make SQL Server database development, administration, and management process efficient. The Studio can also be of use when it comes to today's topic, from generating test data to performing advanced percentage calculations.

For a more visual comparison of the two solutions, watch the SSMS vs. dbForge Studio for SQL Server - Features Comparison video on the Devart YouTube channel.

Further learning

The following guides can be helpful when working with SQL queries:

• SQL Server Tutorials
• dbForge Studio Documentation
• dbForge Studio Video Tutorials
• Devart Academy