How can I select a data from another column from rows that have been selected?-CodePudding

I tried my best to figure and google this out, but couldn't really find a solid answer to it.

The problem I'm facing is that

Table 1:

ID    Value 1
1     a
2     b
3     c

Table 2:
ID    Value 2
1     4a
3     5b
4     6c

and I'd basically have to select the value from Table 1 that doesn't exist on Table 2 (Thus, 'b')

I can select and identify the ID that I want by using minus function between the tables, but can't seem to figure out a way to call a query to instead call the data.

CodePudding user response：

Use the MINUS as a subquery (i.e. an inline view) (lines #14 - 16):

Sample data:

SQL> with
  2  table1(id, value1) as
  3    (select 1, 'a' from dual union all
  4     select 2, 'b' from dual union all
  5     select 3, 'c' from dual
  6    ),
  7  table2 (id, value2) as
  8    (select 1, '4a' from dual union all
  9     select 3, '5b' from dual union all
 10     select 4, '6c' from dual
 11    )

Query begins here:

 12  select a.*
 13  from table1 a
 14  where a.id in (select t1.id from table1 t1
 15                 minus
 16                 select t2.id from table2 t2
 17                );

        ID VALUE1
---------- ----------
         2 b

SQL>

Alternatively, use not exists:

<snip>
 12  select a.*
 13  from table1 a
 14  where not exists (select null
 15                    from table2 b
 16                    where b.id = a.id
 17                   );

        ID VALUE1
---------- ----------
         2 b

SQL>