Can you define values in a SQL statement that you can join/union, but are not stored in a table outside of the statement?

Question

I'm trying to create a query and need to join against something that I can define values in without creating a table.

I'll attempt to describe what I'm trying to do:

table1
-------
fieldA 

is joined on fieldA with

table2 
-------
(titles for FK in table 1)

• Table1 has values outside of what exists in table2
• I want to add an additional 'table' to be unioned with table2 and then joined with table 1

Thanks

Answer 1

Sure, you can use a UNION ALL inside a subselect and join with the result of that. Something like this might do the trick:

SELECT *
FROM table1 T1
JOIN (
    SELECT titles, stuff 
    FROM table2
    UNION ALL
    SELECT 'foo' AS titles, 'foostuff' AS stuff
    UNION ALL
    SELECT 'bar' AS titles, 'barstuff' AS stuff
) T2
ON T1.id = T2.titles

Note that the columns in the UNION ALL must be of the same type and in the same order. The column names don't have to match though.

Answer 2

Looks like you want to add arbitrary results to your query?

select
    id,
    titles
from
    table1 t1
    inner join table2 t2
        on t2.titles = t1.titles
union (
    (select 100, 'Dogs' from dual)
    union
    (select 200, 'Pigs' from dual)
    union 
    (select 300, 'Sheep' from dual)
) 

That's an oracle flavour, for other RDBMS' there will be an equivalent to dual

If you're using a modern Oracle version, there is an even neater solution

WITH arbitrary_data AS (
              SELECT 100 id, 'Dogs' titles FROM DUAL
    UNION ALL SELECT 200, 'Pigs' FROM DUAL
    UNION ALL SELECT 300, 'Sheep' FROM DUAL
 )
 SELECT
    id,
    titles
 FROM
    table1 t1
    inner join table2 t2
        on t2.titles = t1.titles
    inner join arbitrary_data ad
        on  ad.titles = t1.titles