INNER JOIN (U-SQL)

 

Updated: March 10, 2017

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:

@employees
EmpName stringDepID int?
Rafferty31
Jones33
Heisenberg33
Robinson34
Smith34
Williamsnull
@departments
DeptID intDepName string
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.

@rs_inner
EmpDepID int?DepID int?EmpName stringDepName string
3131RaffertySales
3333JonesEngineering
3333HeisenbergEngineering
3434RobinsonClerical
3434SmithClerical

See Also

Community Additions

ADD
Show: