Uncorrelated subqueries SQL

 
create table data_that_i_only_wanted_rows(all_the_rows integer, pk integer);
create table this_table_here (i_forgot_to_join_to_dtiowr varchar2(1), fk integer, matching_values integer);

insert into data_that_i_only_wanted_rows
  select rownum, rownum
  from   dual
  connect by level <= 10;
  
insert into this_table_here values ('Y', 1, 1);

-- Find us all the rows in DTIOWR if there's any rows in TTH with value 'Y'
-- Which is probably not what we wanted
select all_the_rows
from   data_that_i_only_wanted_rows dtiowr
where  exists (
         select matching_values
         from   this_table_here tth
         where  i_forgot_to_join_to_dtiowr = 'Y');
         
-- Get us just the rows in DTIOWR that have a matching value in TTH
-- which probably is what we wanted!
select all_the_rows
from   data_that_i_only_wanted_rows dtiowr
where  exists (
         select matching_values
         from   this_table_here tth
         where  i_forgot_to_join_to_dtiowr = 'Y'
         and    tth.fk = dtiowr.pk);

drop table data_that_i_only_wanted_rows purge;
drop table this_table_here purge;

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

(required)

(required)