0

In some of my procedures I'm passing parameter that should determine whether I need INNER or LEFT join but unfortunately I don't have an idea how can I implement it.

The easiest solution of course is to have an IF statement and check for the parameters value and use according query on it but it does seem like a bit of overkill -> my query joins over 30 tables so it would be a bummer if I had to copy it just to have one word changed.

What I need is something like (yes, I know it doesn't work):

SELECT a.ID,
    a.NAME,
    b.TYPE
FROM A
CASE 
    WHEN :ip_param = 'I' THEN INNER JOIN B ON A.ID2 = B.ID
    ELSE LEFT JOIN B ON A.ID2 = B.ID
END

Would be grateful for any hints/ideas how to solve it differently then having:

IF :ip_param = 'I' THEN
    select with inner join 
ELSE
    select with left join
END IF
Oleg Valter is with Ukraine
  • 9,619
  • 8
  • 37
  • 59
Mati
  • 389
  • 3
  • 6
  • 16

2 Answers2

3

An inner join can be seen as a left join where where there are no null rows allowed, so you can use:

SELECT a.ID,
    a.NAME,
    b.TYPE
FROM A
LEFT JOIN B ON A.ID2 = B.ID
WHERE ((:ip_param = 'I' AND b.id IS NOT NULL)
      OR (:ip_param != 'I' AND 1 = 1))
Scratte
  • 3,056
  • 6
  • 19
  • 26
  • Yeah, that's what I was thinking of but assuming the SQL execution order FROM & JOINs are always before WHERE so I assume that having it as JOIN would be a bit faster? – Mati Sep 08 '21 at 09:57
  • I'm not sure how the optimization work on HANA. However I'm pretty sure that there's no rule that FROM and JOINs are always executed before the WHERE. Else it would take just as long to do a SQL even if you specify the ID of the row you want. I would probably test the time for both inner and outer and this proposal and compare them. Note: Don't use the first execution in your test. Use a cached one :) – Scratte Sep 08 '21 at 10:05
  • Sure, will do. Thank you! I though that engine in SQL is "smart enough" to move those things around for the optimalizations but basing on things like https://sqlbolt.com/lesson/select_queries_order_of_execution it seems that FROM and JOINS are before WHERE. – Mati Sep 08 '21 at 10:06
  • 1
    If you un-accept, you may get a better Answer later on today when the West wakes up. It's my impression that people tend to not Answer Questions with an accepted Answer on it. (Not that I mind that it's accepted. Just to increase your change of some alternative options. You can always accept an Answer later :-) – Scratte Sep 08 '21 at 10:10
  • Sure, done as you propose! FYI. I did something called APPLY_FILTER (Hana specific) https://help.sap.com/viewer/de2486ee947e43e684d39702027f8a94/2.0.05/en-US/a09d584807f84477a64d7625ca45b089.html but it seems from the SQL Execution plan that it does exactly what your Query does. – Mati Sep 08 '21 at 10:11
  • 1
    About the order of execution, there is an Answer to [Order Of Execution of the SQL query](https://stackoverflow.com/a/4596739/12695027) by [Remus Rusanu](https://stackoverflow.com/users/105929/remus-rusanu), which states "The optimizer is free to choose any order it feels appropriate to produce the best execution time". I tend to look at the execution plan to find out what the optimizer wants to do. The actual execution order isn't the same as the logical order where you can obviously not get the column name without knowing about the table. – Scratte Sep 08 '21 at 10:25
  • @Mati Modern DBMS optimizers is smart enough to identify the join type. If it was not true, then join over `where` condition (like `from a, b where a.id = b.id`) should perform cartesian product and then filter, which is not in real world. You may also perform Visualize plan -> Execute to see the actual execution plans in both cases – astentx Sep 08 '21 at 13:02
0

You can do this as a LEFT JOIN with filtering (as the other answer points out). I would suggest:

SELECT a.ID, a.NAME, b.TYPE
FROM A LEFT JOIN
     B
     ON A.ID2 = B.ID
WHERE :ip_param <> 'I' OR B.ID IS NOT NULL;

In terms of performance, you want an index on B(ID).

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • 1
    HANA does not need single column index for join, because for column tables it performs join by vertor operators and does not use index. – astentx Sep 08 '21 at 13:05