How can I use all the selected values of a column, one by one, in another query?

Question

Use column values returned from the statement below:

SELECT DISTINCT(column_name) FROM Table1;

into the following query:

SELECT dif_col_1, 
       dif_col_2 
  FROM Table2 
 WHERE key_in_table = (SELECT key_in_table 
                         FROM Table3 
                        WHERE UPPER(some_column) LIKE '%each_value_from_the_first_query%');

Answer 1

Let's start from the inner query and work our way out. You want to return rows from Table3 for which a column matches (using the LIKE operator) any column from Table1, right? I would use the EXISTS operator for that. We can reword your problem as "return all rows from table3 for which there exists at least one matching row from Table1". Here's one implementation:

SELECT key_in_table 
FROM Table3 
WHERE EXISTS
(    
    SELECT 1
    FROM Table1
    WHERE UPPER(Table3.some_column) LIKE '%' || Table1.column_name || '%'
);

You can run that query separately so you can be satisfied that it returns the results that you're after. The final query is as simple as integrating that subquery into what you already had:

SELECT dif_col_1, dif_col_2
FROM Table2 
WHERE key_in_table = 
(
    SELECT key_in_table 
    FROM Table3 
    WHERE EXISTS
    (    
        SELECT 1
        FROM Table1
        WHERE UPPER(Table3.some_column) LIKE '%' || Table1.column_name || '%'
    )
);

Answer 2

You could use your first query as a derived table and join it to Table3 using the LIKE predicate in the joining condition:

SELECT
  dif_col_1,
  dif_col_2
FROM
  Table2
WHERE
  key_in_table IN
  (
    SELECT
      Table3.key_in_table
    FROM
      Table3
      INNER JOIN (SELECT DISTINCT column_name AS column_name from Table1) t1
        ON UPPER(Table3.some_column) LIKE '%' || t1.column_name || t1.'%'
  )
;

Note the change of key_in_table = ... to key_in_table IN .... As the subquery is matching against a set of values, it can produce a set (multiple rows) of its own. Comparing a single value to a set of two or more values would produce an error in Oracle, hence using a different predicate (IN).