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?

Answer 1

@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.