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

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 table开发者_如何转开发2 and then joined with table 1

Thanks

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.

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