CODEWITHSUNDEEP
sqloracle-databaseoracle12c
user2206329
user2206329

Reputation: 2842

oracle how to return a list and join to a table?

In oracle is it possible to join a static list to a table? The list I have is something like this

ID
1
2
3
4
5
6

I don't want to create a table for this list

But then I want to join the list to an existing table that has the ID's in it... hoping to do a left join with the list

Is this possible?

Upvotes: 0

Views: 627

Answers (2)

GMB
GMB

Reputation: 222632

You are lookig for a WITH clause that contains UNIONs of SELECT FROM DUAL.

Like :

WITH my_list AS (
    select 'A' my_value from dual
    UNION ALL select 'B' my_value from dual
    UNION ALL select 'C' my_value from dual
)
SELECT
    *
FROM 
    my_list
    LEFT JOIN my_table ON my_table.my_field = my_list.my_value 
;

Upvotes: 1

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60292

You can generate the ID list in a CTE and then join it to whatever you want.

with id_list as (
  select rownum as id
  from dual
  connect by level <= 6
)
select * from id_list;

ID
1
2
3
4
5
6

https://livesql.oracle.com/apex/livesql/s/hm2mczgx5udiig9vhryo86mfm

Upvotes: 0

Related Questions