Thursday, 5 November 2015

INNER JOIN vs. CROSS APPLY

INNER JOIN is the most used construct in SQL: it joins two tables together, selecting only those row combinations for which a JOIN condition is true.
This query:

SELECT  * FROM    table1 JOIN    table2 ON      table2.b = table1.a 
reads:
For each row from table1, select all rows from table2 where the value of field b is equal to that of field a
Note that this condition can be rewritten as this:


CROSS APPLY is a Microsoft's extension to SQL, which was originally intended to be used with table-valued functions (TVF's).
The query above would look like this:
 
SELECT  * FROM    table1 CROSS APPLY( 
SELECT  TOP (table1.rowcount) *
FROM    table2
ORDER BY Id
) t2
For each from table1, select first table1.rowcount rows from table2 ordered by id

No comments:

Post a Comment

Row based styling for Primeng DataTable using Angualr 2,4

------------------------------------Component.Html---------------- < p -dataTable id = "Tbl" [ rowStyleClass ]= " Fu...