How to use subquery in USQL?

Question

I am getting complilation error while using follwowing query in u-sql:

@CourseDataExcludingUpdatedCourse = SELECT * FROM @CourseData AS cd 
WHERE cd.CourseID NOT IN (SELECT CourseID FROM @UpdatedCourseData);

It is not allowing me to use NOT IN Clause in subquery. I want to show all those records which are not present in @UpdatedCourseData. How can I achieve this in U-SQL?

score 3 · Answer 1 · answered Dec 14 '16 at 11:10

In U-SQL NOT EXISTS is implemented as ANTISEMIJOIN, something like this:

@CourseDataExcludingUpdatedCourse =
    SELECT cd.*
    FROM @CourseData AS cd
         ANTISEMIJOIN
             @UpdatedCourseData AS us
         ON cd.courseId == us.courseId;

See here for more info:

https://msdn.microsoft.com/en-us/library/azure/mt621330.aspx

score 2 · Accepted Answer · answered Feb 16 '17 at 02:43

@employees = 
    SELECT * FROM 
        ( VALUES
        (1, "Noah",   100, (int?)10000, new DateTime(2012,05,31)),
        (2, "Sophia", 100, (int?)15000, new DateTime(2012,03,19)),
        (3, "Liam",   100, (int?)30000, new DateTime(2014,09,14)),
        (4, "Amy",    100, (int?)35000, new DateTime(1999,02,27)),
        (5, "Justin", 600, (int?)15000, new DateTime(2015,01,12)),
        (6, "Emma",   200, (int?)8000,  new DateTime(2014,03,08)),
        (7, "Jacob",  200, (int?)8000,  new DateTime(2014,09,02)),
        (8, "Olivia", 200, (int?)8000,  new DateTime(2013,12,11)),
        (9, "Mason",  300, (int?)50000, new DateTime(2016,01,01)),
        (10, "Ava",   400, (int?)15000, new DateTime(2014,09,14))
        ) AS T(EmpID, EmpName, DeptID, Salary, StartDate);

@departments = 
    SELECT * FROM 
        ( VALUES
        (100, "Engineering"),
        (200, "HR"),
        (300, "Executive"),
        (400, "Marketing"),
        (500, "Sales"),
        (600, "Clerical"),
        (800, "Reserved")
        ) AS T(DeptID, DeptName);

/* T-SQL; Using a subquery with IN
SELECT * 
FROM @employees
WHERE DeptID IN 
    (SELECT DeptID FROM @departments WHERE DeptName IN ('Engineering', 'Executive'));
*/

// U-SQL; Using SEMIJOIN 
@result =
    SELECT *
    FROM @employees AS e
    LEFT SEMIJOIN (SELECT DeptID FROM @departments WHERE DeptName IN ("Engineering", "Executive")) AS sc
    ON e.DeptID == sc.DeptID;

OUTPUT @result
TO "/Output/ReferenceGuide/Joins/SemiJoins/SubqueryIN.txt"
USING Outputters.Tsv(outputHeader: true);

/* T-SQL; Using a subquery with NOT IN
SELECT * 
FROM @employees
WHERE DeptID NOT IN 
    (SELECT DeptID FROM @departments WHERE DeptName IN ('Engineering', 'Executive'));
*/

// U-SQL; Using ANTISEMIJOIN 
@result =
    SELECT *
    FROM @employees AS e
    LEFT ANTISEMIJOIN (SELECT DeptID FROM @departments WHERE DeptName IN ("Engineering", "Executive")) AS sc
    ON e.DeptID == sc.DeptID;

OUTPUT @result
TO "/Output/ReferenceGuide/Joins/AntiSemiJoins/SubqueryNOTIN.txt"
USING Outputters.Tsv(outputHeader: true);

// BONUS: Switch "LEFT" to "RIGHT" in the above examples and observe the results.

When giving an answer it is preferable to give [some explanation as to WHY your answer](http://stackoverflow.com/help/how-to-answer) is the one. — Stephen Rauch, Feb 16 '17 at 03:05

How to use subquery in USQL?

2 Answers2