Share via

INNER JOIN (U-SQL)

  • Article

Summary

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

Examples

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

EmpName DepID
Rafferty 31
Jones 33
Heisenberg 33
Robinson 34
Smith 34
Williams null
DeptID DepName
31 Sales
33 Engineering
34 Clerical
35 Marketing

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.

EmpDepID DepID EmpName DepName
31 31 Rafferty Sales
33 33 Jones Engineering
33 33 Heisenberg Engineering
34 34 Robinson Clerical
34 34 Smith Clerical

See Also