APPLY operator is very useful feature that is not very common, so I would like to introduce you into one of the most powerful tools in TSQL.
So let’s begin by showing some theory about JOIN operators. INNER JOIN and OUTER JOINS (RIGHT,LEFT, FULL) are very common and they work in very similar manner, as you can see in the picture below, JOINS must have two sets that are predefined – query engine have to know about rows from both sides before it executes the query.
APPLY operator doesn’t need to have predefined table from both sides! Image below can help you to understand how it works: