[SQL] CROSS and OUTER Apply

The APPLY clause let’s you join a table to a table-valued-function.

 

The APPLY clause acts like a JOIN without the ON clause comes in two flavours: CROSS and OUTER.

The OUTER APPLY clause returns all the rows on the left side table whether they return any rows in the table-valued-function or not, and thus similar to a left join).n

The columns that the table-valued-function returns are null if no rows are returned.

 

The CROSS APPLY only returns rows from the left side table if the table-valued-function returns rows.

 

  • OUTER APPLY acts like a LEFT JOIN.
  • CROSS APPLY acts like a RIGHT JOIN.

http://msdn.microsoft.com/en-us/library/ms175156.aspx

http://www.sqlteam.com/article/using-cross-apply-in-sql-server-2005

http://www.sqlusa.com/articles2005/crossapplyversusouterapply/

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s