loading..
   English
16:30

CROSS APPLY / OUTER APPLY page 1

CROSS APPLY is operator that appeared in SQL Server 2005. It allows two table expressions to be joined together in the following manner: each row from the left-hand table is being combined with each row from the right-hand table.

Let's try to find out the possibilities of this operator and what advantages we gain from usage of it.

The first example.

Console
Execute
  1. SELECT * FROM
  2. Product
  3. CROSS APPLY
  4. Laptop;

We got simply the cartesian product of Product and Laptop tables. The similar result we would obtain with aid of the following standard queries:

Console
Execute
  1. SELECT * FROM
  2. Product
  3. CROSS JOIN
  4. Laptop;
or

Console
Execute
  1. SELECT * FROM
  2. Product, Laptop;

Let's set more reasonable task.

For each laptop, get additional column with maker name.

This exercise we can solve with aid of ordinary join:

Console
Execute
  1. SELECT P.maker, L.* FROM
  2. Product P JOIN Laptop L ON P.model= L.model;

Usage of CROSS APPLY leads to the following solution:

Console
Execute
  1. SELECT P.maker, L.* FROM
  2. Product P
  3. CROSS APPLY
  4. (SELECT * FROM Laptop L WHERE P.model= L.model) L;

 "Does it give us any innovations"? - You could ask. The query does not become shorter in size. This is true. But yet here we could notice very important feature which distinguishes CROSS APPLY from other joins. Namely, we use correlated subquery in FROM clause and are passing into it the values from left-hand table expression. In the given example this value is P.model. I.e. each row from left-hand table will have its own right-hand table to join.

As soon as understanding it, we can use this feature. One more task.

For each laptop, find additionally out max price among all laptops which the maker of this laptop produces.

We can solve this task with aid of correlated subquery in the SELECT clause:

Console
Execute
  1. SELECT *, (SELECT MAX(price) FROM Laptop L2
  2. JOIN Product P1 ON L2.model=P1.model
  3. WHERE maker = (SELECT maker FROM Product P2 WHERE P2.model= L1.model)) max_price
  4. FROM laptop L1;

So far, the solution using CROSS APPLY is similar to previous one generally:

Console
Execute
  1. SELECT *
  2. FROM laptop L1
  3. CROSS APPLY
  4. (SELECT MAX(price) max_price FROM Laptop L2
  5. JOIN Product P1 ON L2.model=P1.model
  6. WHERE maker = (SELECT maker FROM Product P2 WHERE P2.model= L1.model)) X;

Next page

Bookmark and Share
Pages 1 2 3
Tags
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema date/time functions DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates equi-join EXCEPT exercise (-2) exercise 19 More tags
The book was updated
several days ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100