Create a view that lets you add conditions to the LEFT JOIN ... ON?

Question

I am creating a database (using Oracle 12c) that will be used by a number of client applications. To simplify, I try to keep as much of the logic as possible inside the database by creating different views, so that the clients can just ask simple question without more complicated constructs like JOIN or GROUP BY. They should just have to do a simple SELECT with some WHERE conditions from a view, and let the view do the heavy lifting.

Now my problem is that I want to ask questions on the form

SELECT
    -- Some fields.
FROM a
LEFT JOIN b ON a.id = b.id AND b.type = x
LEFT JOIN c ON a.id = c.id AND c.type = y
LEFT JOIN d ON a.id = d.id AND d.type = z

where x, y and z are input from the client. I do not want the clients to have to construct that query. I would much rather have them do something more similar to this:

SELECT * FROM a_view WHERE b_type = x AND c_type = y AND d_type = z

Of course I could create one view for every possible combination of x, y and z, but that would be a lot of views. Is there any better way to solve this problem, or should I just give up and let the clients write the query with the JOIN?

The use of `left join` makes this quite difficult. I might suggest that you ask another question with better examples of the tables and the types of `where` clauses you want to support. There may be a way to accomplish this. Just switching to `inner join` is one possible solution. — Gordon Linoff, Nov 13 '15 at 15:54
Could you please advice me as to what would make the examples better? I have considered using `INNER JOIN`, but since I always want to return all rows from the left table (`a`), I think I need a `LEFT JOIN`. — Anders, Nov 13 '15 at 16:00
In MSSQL this is possible with table valued functions. I am not sure about Oracle. — Adish, Nov 13 '15 at 16:02
You might be able to do what you want with pipelines functions. See the answer to this question: http://stackoverflow.com/questions/2059299/table-valued-functions-in-oracle-11g-parameterized-views. — Gordon Linoff, Nov 13 '15 at 16:02
I will look into the pipeline functions and return with a report. It looks promising. — Anders, Nov 13 '15 at 16:27
What if there is a mach for `a` in the `c` table where `type` is `w`. Then that row would not be included in the question, since `c.type` would be `w` and not `null` or `x`. I need every row in `a` to be included in the result. — Anders, Nov 13 '15 at 20:23

KevinKirkpatrick · Accepted Answer · 2015-11-13T20:44:24.647

This can be done, but you must have a source of all valid types and assume user/application will only query view for a valid type. In other words, assume table T is your "type table" and all your B, C, and D tables have (or could have) FK defined on T.

In this case your view def. would be:

CREATE OR REPLACE VIEW V AS
SELECT A.ID, BT.TYPE BTYPE, CT.TYPE CTYPE, DT.DTYPE DTYPE, ... 
FROM A 
CROSS JOIN T BT
CROSS JOIN T CT
CROSS JOIN T DT
LEFT JOIN B ON A.ID = B.ID AND B.TYPE = BT.TYPE
LEFT JOIN C ON A.ID = C.ID AND C.TYPE = CT.TYPE
LEFT JOIN D ON A.ID = D.ID AND D.TYPE = DT.TYPE;

Of course, if there were a fixed set of valid types in the model, you could replace T with (SELECT 'X' TYPE FROM DUAL UNION ALL SELECT 'Y' FROM DUAL UNION ALL ...)

The only difference between:

SELECT
    -- Some fields.
FROM a
LEFT JOIN b ON a.id = b.id AND b.type = x
LEFT JOIN c ON a.id = c.id AND c.type = y
LEFT JOIN d ON a.id = d.id AND d.type = z

and

SELECT * FROM V WHERE B_TYPE = X AND C_TYPE = Y AND D_TYPE = Z

is if X, Y, or Z weren't "valid" types (in which case, view def. would return no rows).

EDIT: Clarifying per comment; I'd assumed "type" was a common domain to tables B, C, and D. If B.type is different from C.type (i.e. B.TYPE is NUMERIC(9) and C.type is VARCHAR(2) and D.TYPE is NUMERIC(1)) then the cross joins need to refer to each "set of valid type values" independently:

CREATE OR REPLACE VIEW V AS
SELECT A.ID, BT.TYPE BTYPE, CT.TYPE CTYPE, DT.DTYPE DTYPE, ... 
FROM A 
CROSS JOIN (--SELECT ALL DISTINCT VALID B.TYPE VALUES--) BT
CROSS JOIN (--SELECT ALL DISTINCT VALID C.TYPE VALUES--) CT
CROSS JOIN (--SELECT ALL DISTINCT VALID D.TYPE VALUES--) DT
LEFT JOIN B ON A.ID = B.ID AND B.TYPE = BT.TYPE
LEFT JOIN C ON A.ID = C.ID AND C.TYPE = CT.TYPE
LEFT JOIN D ON A.ID = D.ID AND D.TYPE = DT.TYPE;

That said, you do have the same restriction: built into the view def. must be some finite source of "all valid B types", "all valid C types" and "all valid D types". Barring that, this is not doable in pure SQL (in fact, in terms of pure SQL, it becomes an intractable problem - a view that supports filtering by any conceivable combination of values, with no filters, should return all conceivable combinations of values...)

Thank you for your answer. It is a very interesting alternative. The limitation is, as you mention, that all the types have to be discrete. If one of them were say a `NUMBER(9)` things would brake down. — Anders, Nov 13 '15 at 20:22
Not at all - if B types are different from C types and different from D types, then you'd use `CROSS JOIN BT` instead of `CROSS JOIN T BT` and so on. The original limiation still applies though: if B types, C types, and D types are from 3 different sets of values, you'd still need *some* source of valid B types, some source of valid C types, and some source of valid D types. Otherwise, though, nothing says the types must be the same (that was an assumption I probably shouldn't have made) — KevinKirkpatrick, Nov 13 '15 at 20:30
Sorry, I am expressing myself unclear. What I was trying to say is this: If there are many, many different values for one of the types (say a thousand), then the view would return an absurdly large number of rows and I guess there would be performance problems? — Anders, Nov 13 '15 at 20:58
Nope - so long as view is filtered by discrete specifications of which B_TYPE, C_TYPE, and D_TYPE is desired, then those specifications will be pushed into view definition... in other words, no *actual* cartesian product will be performed, unless user doesn't specify which combination of parameters they want returned — KevinKirkpatrick, Nov 13 '15 at 21:04

score 0 · Answer 2 · answered Nov 13 '15 at 17:25

Probably, you can use "old style join syntax":

SELECT
    -- Some fields.
FROM a, b, c, d
WHERE a.id = b.id(+) AND b.type(+) = x
  AND a.id = c.id(+) AND c.type(+) = y
  AND a.id = d.id(+) AND d.type(+) = z

For example:

SQL> with
     t  as (select rownum r from dual connect by level < 6),
     t1 as (select rownum r from dual connect by level < 5),
     t2 as (select rownum r from dual connect by level < 4)
select *
  from t, t1, t2
 where t.r = t1.r(+) and t1.r(+) = 3
   and t.r = t2.r(+) and t2.r(+) = 2
order by 1, 2, 3;

     R      R          R
---------- ---------- ----------
     1
     2             2
     3      3
     4
     5

Thank you for your answer. My goal here would be that the clients should only have to know about one view, and not worry about the multiple tables and their relation to each other. If I understand you right, the client would still have to know about all the tables. — Anders, Nov 13 '15 at 20:19

Create a view that lets you add conditions to the LEFT JOIN ... ON?

2 Answers2