"with ... how" in SQL Navigator

Question

The following query is executed:

select count(*) from everything where num not in (select num from sometable) 

The following query should be equivalent to the one above, but results in an "invalid identifier" error:

 with unwanted as (select num from sometable) select count(*) from everything where num not in unwanted 

What happened to the second request?

Answer 1

The syntax is as follows:

 with unwanted as (select num from sometable) select count(*) from everything where num not in (select * from unwanted) 

obviously this only makes sense if the select num from sometable little more complicated or used a few times later ...

Answer 2

You can also join tables for faster work.

 WITH unwanted AS ( SELECT num FROM sometable ) SELECT COUNT(*) FROM everything a LEFT JOIN unwanted b ON a.num = b.num WHERE b.num IS NULL