oracle - SQL - 'Where exists' clause without equality check -

can explain syntax doing:

where exists(select * table(source.sources))  

i dont understand how comparison takes place. syntax used

select x y exists (select 1 z z.id = y.id) 

in first exists statement, y.id = x.id comparison take place? can see example of how first syntax can used?

thanks

it looks sources nested table in source table. here's example:

create type sources_type table of number /  create table source (id number, sources sources_type) nested table sources store sources_tab;  insert source (id, sources) values (1, null); insert source (id, sources) values (2, sources_type()); insert source (id, sources) values (3, sources_type(1, 2, 3)); 

the 3 rows 3 possible states of sources; null, empty table (which not same thing), , populated table. if want select rows table not empty, can't check if it's null half story.

select * source sources null;          id sources                                 ---------- ----------------------------------------          1                                            select * source sources not null;          id sources                                 ---------- ----------------------------------------          2 stackoverflow.sources_type()                       3 stackoverflow.sources_type(1,2,3)         

you might not want row id=2 included. can @ nested table contents instead:

select * source not exists (select * table(sources));          id sources                                 ---------- ----------------------------------------          1                                                    2 stackoverflow.sources_type()               select * source exists (select * table(sources));          id sources                                 ---------- ----------------------------------------          3 stackoverflow.sources_type(1,2,3)         

so final query ignoring empty nested tables, nulls.

in second example you're doing correlated subquery (or @ least assume if pseudocode wan't bit mangled), looking matching data in table not part of main query, , (usually) need equality test; in first example you're checking existence of data within same table, there nothing compare against.

you can check specific values in nested table - i'm not sure if clarifies or confuses issue:

select * source exists (select * table(sources) column_value = 2);