Conditional Join in SQL query

Question

I need a single SQL query that would satisfy the below criteria.

I have 2 tables, say Table1 with these values:

ID	Code	Description
1	1000	abc
2	1000	pqr
3	1000	efg
4	2300	rst
5	2300	uvw
6	2930	xyz
7	3500	aaa

And I have another table Table2 with these values:

ID	FromCode	ToCode	IsExcluded
1	1000	1000	0
2	2000	2999	0
3	2930	2930	1

I need a single query that shall give me the results like:

If exists (Select 1 From Table2 where IsExcluded = 0)
    -- Get the records that match the criteria
    Select * 
    From table1 t1
    Inner Join table2 t3 on (t1.Code between t3.FromCode and t3.ToCode) and t3.IsExcluded = 0
    Left Join table2 t2 on (t1.Code between t2.FromCode and t2.ToCode ) and t2.IsExcluded = 1
    Where t2.ID is Null
Else
    Select * 
    From table1 t1
    Left Join table2 t2 on (t1.Code between t2.FromCode and  t2.ToCode) and t2.IsExcluded = 1  
    Where t2.ID is Null

So, basically return rows from Table1 that have:

all the matching rows when IsExcluded = 0. This should be true only if rows exists in Table2 with condition isExcluded = 0, otherwise return all the rows from Table1
exclude those rows from Table1 where IsExcluded = 1. Again, here also if rows exists with the condition IsExcluded = 1, then exclude the rows that meet the condition, otherwise return all the rows. This has been taken care of using left join with IsExcluded = 1
please note that rows may or may not exists in Table2. If they do not exists, then return all the rows from Table1

In other words, return the rows from Table 1 that satisfy the ranges defined in Table 2 when IsExcluded = 0, and ignore rows with condition IsExcluded = 1. If there is no condition (i.e. no rows are present in Table 2), then display all the rows from Table 1.

Any help would be greatly appreciated.

Thanks.

Please note the editor has a code formatting option to make your code easier to read. — Dale K
– Dale K, Commented Oct 30 at 1:29
What would be the expected output for the sample data given, please show. And what's wrong with the code you have? — Charlieface
– Charlieface, Commented Oct 30 at 1:37
Maybe a UNION ALL, it's really not clear what output columns you want dbfiddle.uk/0R5YFyfY — Charlieface
– Charlieface, Commented Oct 30 at 1:44
@Charlieface, I need a single query, so that I can create a view out of it. — Amit Nerurkar
– Amit Nerurkar, Commented Oct 30 at 1:49

Charlieface · Accepted Answer · 2025-10-30 02:25:57Z

3

You can use UNION ALL to combine the two halves. And instead of joining, which will duplicate in case of multiple matches, instead just use EXISTS clauses

SELECT *
FROM table1 t1
WHERE NOT EXISTS (SELECT 1
    FROM table2 t2
    WHERE t2.IsExcluded = 0)

UNION ALL

SELECT *
FROM table1 t1
WHERE EXISTS (SELECT 1
  FROM table2 t2
  WHERE t1.Code BETWEEN t2.FromCode AND t2.ToCode
  AND t2.IsExcluded = 0)
AND NOT EXISTS (SELECT 1
    FROM table2 t2_x
    WHERE t1.Code BETWEEN t2_x.FromCode AND t2_x.ToCode
    AND t2_x.IsExcluded = 1);

You can also use a GROUP BY in the EXISTS to combine the NOT EXISTS into it.

SELECT *
FROM table1 t1
WHERE NOT EXISTS (SELECT 1
    FROM table2 t2
    WHERE t2.IsExcluded = 0)

UNION ALL

SELECT *
FROM table1 t1
WHERE EXISTS (SELECT 1
  FROM table2 t2
  WHERE t1.Code BETWEEN t2.FromCode AND t2.ToCode
  HAVING COUNT(CASE WHEN t2.IsExcluded = 0 THEN 1 END) > 0
     AND COUNT(CASE WHEN t2.IsExcluded = 1 THEN 1 END) = 0
);

db<>fiddle

answered Oct 30 at 2:25

Charlieface

78.4k8 gold badges35 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Amit Nerurkar Oct 30 at 3:41

This gives me multiple rows. It should return the records from table 1. Have a look at my results dbfiddle.uk/Bdd8p2r3. Try commenting the entire insert on Table 2 and, comment individual insert records on table 2. Please do let me know if you have any further questions.

Amit Nerurkar Oct 30 at 4:06

my bad, it doesn't return multiple rows. However, it does not exclude the record with code 2930. when insert on first 2 records in table 2 are commented, or in other words there are no records with IsExcluded = 0, it should return all records from table 1 other than 2930.

Jonas Metzler Oct 30 at 5:29

@Amit Nerurkar The queries in this answer are simple, clean and correct according to the logic you requested in the question. To your comment about code 2930: I don't understand this comment. The row with this code is not selected, see the fiddle. I suggest to accept the answer as it obviously is correct. If you expected something else, you described it incorrectly. In this case, also accept the answer, then write a new, better question to point out what you expect.

p3consulting Oct 30 at 9:02

First part of the UNION ALL doesn't check the condition on the t1.code being in the t2 interval... don't see how this could be correct and if the query gives the correct result then it means only part 2 is useful.

Charlieface Oct 30 at 12:39

@AmitNerurkar Agreed, it seems you have some different intention regarding the "nothing found", which is why I kept asking for expected output. You wrote "rows may or may not exists in Table2. If they do not exists, then return all the rows from Table1" but it seems there may be conditions attached to that.

Guillaume Outters · Accepted Answer · 2025-10-30 02:46:41Z

0

If I understand your logic (yet to be clarified) well, you want to get every row of Table1, joined to Table2 once, by choosing Table2's row with the least IsExcluded value if multiple rows from Table2 match the row from Table1.

Thus:

rows with code 2930, which match both 2 (2000 - 2999) and 3 (2930), will prefer 2 because it has IsExcluded = 0.
code 3500 has no match, so it is output with null values for columns of Table2.
and you present no example where only an IsExcluded = 1 would match

You can write you query in one join and a subquery, the join condition including an and IsExcluded = (select min(IsExcluded) from Table2 /* + additional correlation criteria */):

select *
from table1 t1
left join table2 t2
on t1.Code between t2.FromCode and t2.ToCode
and t2.IsExcluded =
(
  select min(t2m.IsExcluded)
  from table2 t2m
  where t1.Code between t2m.FromCode and t2m.ToCode
);

which given your example data returns:

ID	Code	Description	ID	FromCode	ToCode	IsExcluded
1	1000	abc	1	1000	1000	0
2	1000	pqr	1	1000	1000	0
3	1000	efg	1	1000	1000	0
4	2300	rst	2	2000	2999	0
5	2300	uvw	2	2000	2999	0
6	2930	xyz	2	2000	2999	0
7	3500	aaa	null	null	null	null

(see it running in a db<>fiddle)

answered Oct 30 at 2:46

Guillaume Outters

7,5041 gold badge22 silver badges28 bronze badges

2 Comments

Amit Nerurkar Oct 30 at 3:37

Guillaume, your query does not give me the required output. Have a look at dbfiddle.uk/Bdd8p2r3. Try commenting the entire insert for Table 2 and individual Include and Exclude records and have a look at the results. Coming back to your query, 2930 and 3500 should not be included in the result as 2930 has IsExcluded = 1 (hence should be ignored) and 3500 does not satisfy IsExcluded = 0 condition.

Guillaume Outters Oct 30 at 20:17

Thanks for the feedback. I think we (Charlieface and I) still had questions about what you precisely expect. As I now understand your question, it is totally different from what I thought initially, so I have put it in another answer.

Thorsten Kettner · Accepted Answer · 2025-10-30 11:37:23Z

Your requirement:

In other words, return the rows from Table 1 that satisfy the ranges defined in Table 2 when IsExcluded = 0, and ignore rows with condition IsExcluded = 1. If there is no condition (i.e. no rows are present in Table 2), then display all the rows from Table 1.

That simply means: select all table1 rows except for those that are explicitly excluded in table2:

select *
from table1 t1
where not exists
(
  select null
  from table2 t2
  where t1.code between t2.fromcode and t2.tocode
  and t2.isexcluded = 1
);

Or, if you want to include the table2 data in your result:

select *
from table1 t1
left join table2 t2 on t1.code between t2.fromcode and t2.tocode
where t2.isexcluded = 0 or t2.isexcluded is null;

Guillaume Outters · Accepted Answer · 2025-10-30 20:06:37Z

After re-trying to understand your need, I think it is much simpler that all the (unanswered) comments have made us think.

I would then re-express your need as:

a) "exclusion rule": Under no circumstance should a row from table1 be output if it matches a rule from table2 where IsExcluded = 1
("if it matches" = t1.Code between t2.FromCode and t2.ToCode)
b) if table2 contains rows with IsExcluded = 0:
- b)1) "necessary explicit inclusion rule": then table1 will be output only it it matches at least 1 rule from table2 where IsExcluded = 0

We can then simply write the matching SQL, mapping inclusions to the exists SQL keyword and exclusions to not exists:

select *
from table1 t1
-- a) exclusion rule:
where not exists (select 1 from table2 t2 where t2.IsExcluded = 1 and t1.Code between t2.FromCode and t2.ToCode)
-- b)
and
(
  -- b)0) table t2 with no IsExcluded = 0 rule is a "pass all":
  not exists (select 1 from table2 t2 where t2.IsExcluded = 0)
  or
  -- b)1) else we need at least 1 explicit inclusion rule for this t1.Code is necessary:
  exists (select 1 from table2 t2 where t2.IsExcluded = 0 and t1.Code between t2.FromCode and t2.ToCode)
);

Which returns (demo in this db<>fiddle):

ID	Code	Description
1	1000	abc
2	1000	pqr
3	1000	efg
4	2300	rst
5	2300	uvw

p3consulting · Accepted Answer · 2025-10-31 18:29:14Z

0

I think you can do it without an UNION:

SELECT *
FROM table1 t1
WHERE 
    NOT EXISTS
    (
        SELECT 1
        FROM table2 t2
        WHERE t1.Code BETWEEN t2.FromCode AND t2.ToCode
        HAVING 
            COUNT(CASE WHEN t2.IsExcluded = 0 THEN 1 END) = 0
            OR 
            COUNT(CASE WHEN t2.IsExcluded = 1 THEN 1 END) > 0
    )
    OR NOT EXISTS(
        SELECT 1 FROM table2 t2 WHERE t1.Code BETWEEN t2.FromCode AND t2.ToCode
    )

    ;

https://dbfiddle.uk/9LhX-2xN

edited Oct 31 at 18:29

answered Oct 30 at 16:37

p3consulting

5,4212 gold badges14 silver badges15 bronze badges

2 Comments

Guillaume Outters Oct 31 at 9:34

As I finally understood OP's need (have 2 ways of working: 1. if there is no IsExcluded = 0 rule, table2 only mentions exclude rule (rows from table1 are by default included in the resultset; 2. if there is at least 1 row with IsExcluded = 0, then it is an exclusion and inclusion rules table, where a row from table1 cannot be in the result if it has not at least 1 inclusion rule matching). Your query perfectly answers 2., however it lacks the case for 1. "when table2 has no inclusion rule.

Guillaume Outters Oct 31 at 9:39

To that effect you could either add a UNION like Charlieface did, or make the = 0 test conditional, see this new fiddle with 2 blocks: first table2 in mode 2., with your original query then the modified one (= CASE WHEN EXISTS(SELECT 1 FROM table2 WHERE IsExcluded = 0) THEN 0 END instead of = 0); then another block with table2 in mode 1. Feel free to include it in your answer: I find it elegant already, very different from mine, and that additional bit is better suited to yours than mi

Amit Nerurkar · Accepted Answer · 2025-11-05 15:13:53Z

Eventually, I ended up splitting my query into 2 parts. In the first part, I got all the records that satisfy my exclude condition and then in the later part I got the records that satisfy my include conditions.


Select *
From
(
    -- This will give all records other than the excluded
    Select * 
    From Table1 t1
    Left Join Table2 t2 on t1.Code between t2.FromCode and t2.ToCode
        and t2.IsExcluded = 1 
        Where t2.Id is NULL
) tx
-- get the records that satisfy Exclude = 0
Left join Table2 ti on tx.FundCode between ti.FromCode and ti.ToCode
    and ti.IsExcluded = 0
where 1=1 and 
-- if no records with Exclude = 0, then return all the records with Exclude = 1
    IsNULL(ti.ID, 99999) =
    case when exists (Select 1 From Table2 where IsExcluded = 0) then ti.Id 
    else 99999 end

Dale K · Accepted Answer · 2025-10-30 09:43:06Z

-1

Thank you Charlieface, for the query, you gave me what I wanted. I altered a bit to get the results I wanted. I have to test this further, and this is just the part of the problem. I will have to check this with entire query and test it. This is just 1 code, in reality there are 5 types of code in table t1 with individual include and exclude conditions in ranges for each of the codes. I believe this is what I wanted:

SELECT *
FROM table1 t1
LEFT JOIN table2 t2_x ON t1.Code BETWEEN t2_x.FromCode AND t2_x.ToCode AND t2_x.IsExcluded = 1
WHERE t2_x.ID IS NULL 
    AND NOT EXISTS (SELECT 1
    FROM table2 t2
    WHERE t2.IsExcluded = 0)

UNION ALL

SELECT *
FROM table1 t1
WHERE EXISTS (SELECT 1
  FROM table2 t2
  WHERE t1.Code BETWEEN t2.FromCode AND t2.ToCode
  AND t2.IsExcluded = 0)
AND NOT EXISTS (SELECT 1
    FROM table2 t2_x
    WHERE t1.Code BETWEEN t2_x.FromCode AND t2_x.ToCode
    AND t2_x.IsExcluded = 1);

edited Oct 30 at 9:43

Dale K

28.1k15 gold badges59 silver badges85 bronze badges

answered Oct 30 at 4:33

Amit Nerurkar

113 bronze badges

1 Comment

Jonas Metzler Oct 30 at 5:07

This query is invalid because the first query selects all columns from table1 and from table2, the second those from table1 only. That's not allowed. You should test queries that you propose as an answer!

Collectives™ on Stack Overflow

Conditional Join in SQL query

7 Answers 7

5 Comments

2 Comments

Comments

Comments

2 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

5 Comments

2 Comments

Comments

Comments

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related