Proper indexing for (hidden) tuples

If you have a query like this:

select * from zd
where z in
(select z from zh where y = 'A' and y = zd.y)

It is recommended to index on both y,z fields of both zh and zd tables

create index x_zh on zh(y,z)
create index x_zd on zd(y,z)

The query above internally gets translated to:

select * from zd
where exists (select * from zh where y = 'A' and y = zd.y and z = zd.z)

In Oracle and Postgres that query has intuitive form:

select * from zd 
where (y,z) in (select y,z from zh where y = 'A')

Here's the execution plan:

Whole code for proof of concept:

-- first pair of tables...

select * into zh
from(values
('A',1),
('B',2),
('C',3)) as x(y,z)


select * into zd
from(values
('A',1),
('B',2),
('C',3)) as x(y,z)

-- performant index
create index x_zh on zh(y,z)
create index x_zd on zd(y,z)

-- ...first pair of tables


-- second pair of tables...

select * into wh
from(values
('A',1),
('B',2),
('C',3)) as x(y,w)

select * into wd
from(values
('A',1),
('B',2),
('C',3)) as x(y,w)

-- these index doesn't get used
create index x_wh_y on wh(y)
create index x_wh_z on wh(w)
create index x_wd_y on wd(y)
create index x_wd_z on wd(w)

-- ...second pair of tables


-- execute this in execution planner, highlight them then press Ctrl+L:
select * from zd where z in (select z from zh where y = 'A' and y = zd.y)
select * from wd where w in (select w from wh where y = 'A' and y = wd.y)

Here's the output:

Here's the index seek predicates(I hope I have enough street cred to use the word predicate LOL):

As we can see from the seek predicates execution plan: zh.y,zh.z == 'A',zd.z ; even though it's from Sql Server, it has more affinity with this query style(i.e. Postgres and Oracle style):

select * from zd 
where (y,z) in (select y,z from zh where y = 'A')

Which internally gets translated to:

select * from zd 
where (y,z) in (select 'A',z from zh where y = 'A')

Let's just hope that future Sql Server will support tuples in subquery