CODEWITHSUNDEEP
sqloracle-database
snakile
snakile

Reputation: 54561

"with... as" in SQL Navigator

The following query works:

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

The following query is supposed to be equivalent to the 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 is wrong with the second query?

Upvotes: 6

Views: 1099

Answers (2)

John Woo
John Woo

Reputation: 263933

You can also join the tables for faster performance

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

Upvotes: 2

bw_üezi
bw_üezi

Reputation: 4574

the syntax is like this:

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

obviously this makes only sense if the select num from sometable part is a bit more complex or used several times later...

Upvotes: 7

Related Questions