Recibo un error de compilación al usar la siguiente consulta en u-sql:

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

No me permite usar NOT IN Clause en la subconsulta. Quiero mostrar todos esos registros que no están presentes en @UpdatedCourseData. ¿Cómo puedo lograr esto en U-SQL?

4
Jai 14 dic. 2016 a las 13:46

2 respuestas

La mejor respuesta

En U-SQL, NOT EXISTS se implementa como ANTISEMIJOIN, algo como esto:

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

Vea aqui para mas informacion:

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

3
wBob 14 dic. 2016 a las 11:10
@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.
2
David Paul Giroux 16 feb. 2017 a las 02:43