Multi-column IN / NOT IN subquery on Azure SQL data warehouse

Question

Does Azure SQL Data Warehouse support a multi-column IN/NOT IN with subquery construct?

When running queries like:

select
   *
from 
  schema_name.calendar
where
    gregorian_date > '1998-01-01'
and gregorian_date < '1999-01-01'
and (yr_wk, day_of_wk) not in ( select yr_wk, day_of_wk from schema_name.calendar where week = 25 )
;

select
  *
from 
  schema_name.calendar
where
    gregorian_date > '1998-01-01'
and gregorian_date < '1999-01-01'
and (yr_wk, day_of_wk) in ( select yr_wk, day_of_wk from schema_name.calendar where week = 25 )

;

An error is received.

SQL Error [103010] [S0001]: Parse error at line: 7, column: 14: Incorrect syntax near ','.
com.microsoft.sqlserver.jdbc.SQLServerException: Parse error at line: 7, column: 14: Incorrect syntax near ','.

Is the workaround to rewrite the query into derived tables with inner or outer joins?

Single columns with IN / NOT IN subqueries do work:

select
   *
from 
  schema_name.calendar
where
    gregorian_date > '1998-01-01'
and gregorian_date < '1999-01-01'
and (yr_wk) not in ( select yr_wk from schema_name.calendar where week = 25 )
;

select
   *
from 
  schema_name.calendar
where
    gregorian_date > '1998-01-01'
and gregorian_date < '1999-01-01'
and (yr_wk) in ( select yr_wk from schema_name.calendar where week = 25 )
;

Answer 1

SQL Server has never supported this (handy) syntax. The workaround is to use an EXISTS/NOT EXISTS subquery.

eg

   select   *
from 
  schema_name.calendar c
where
    gregorian_date > '1998-01-01'
and gregorian_date < '1999-01-01'
and not exists
( 
  select * 
  from schema_name.calendar 
  where week = 25 
  and yr_wk = c.yr_wk
  and day_of_wk = c.yr_wk
)
;

David