Table of contents
TOC
Collapse the table of content
Expand the table of content

INNER JOIN (U-SQL)

Michael Rys|Last Updated: 5/15/2018
|
4 Contributors

An inner join will combine the selected columns from the two joined rowsets for every combination of rows that satisfy the join comparison predicate.

Example

Let’s assume the following rowsets are referenced by the respective rowset variables:

EmpNameDepID
Rafferty31
Jones33
Heisenberg33
Robinson34
Smith34
Williamsnull
DeptIDDepName
31Sales
33Engineering
34Clerical
35Marketing

Then the following inner join (the @departments.DepID has to be cast to (int?) since C# does not allow comparison of int with int?).

@employees = SELECT *  
               FROM (VALUES   
                      ("Rafferty", (int?) 31)  
                    , ("Jones", (int?) 33)  
                    , ("Heisenberg", (int?) 33)  
                    , ("Robinson", (int?) 34)  
                    , ("Smith", (int?) 34)  
                    , ("Williams", (int?) null)) AS E(EmpName, DepID);  

@departments = SELECT *  
                FROM (VALUES  
                       ((int) 31, "Sales")  
                     , ((int) 33, "Engineering")  
                     , ((int) 34, "Clerical")  
                     , ((int) 35, "Marketing")) AS D(DepID, DepName);  

@rs_inner =   
    SELECT e.DepID AS EmpDepID, d.DepID, e.EmpName, d.DepName       
    FROM @employees AS e  
         INNER JOIN (SELECT (int?) DepID AS DepID, DepName FROM @departments) AS d   
         ON e.DepID == d.DepID;  

OUTPUT @rs_inner   
TO "/output/rsInnerJoin.csv"  
USING Outputters.Csv();  

produces the following result. Note that it does not include employees that have no department nor does it include departments that have no employees.

EmpDepIDDepIDEmpNameDepName
3131RaffertySales
3333JonesEngineering
3333HeisenbergEngineering
3434RobinsonClerical
3434SmithClerical

See Also

© 2018 Microsoft