CODEWITHSUNDEEP
sqlhanahana-sql-script
Mati
Mati

Reputation: 511

How to change Join type based on a parameter value?

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

Upvotes: 0

Views: 1235

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270793

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).

Upvotes: 0

Scratte
Scratte

Reputation: 3166

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))

Upvotes: 3

Related Questions