How to join result sets without using union or "or" in where condition.?

Question

I have a tool which can't accept union operator or " or " conditional operator in where function

I have two queries

First query

Select * from tableA A left outer join tableB B on A.a1=B.b1 where A.a1 =20200131 and B.b2= 'monkey'

2nd query

Select * from tableA where A.a1 =20200131 and   A.a3 ='Tom`

Combined query using "or" operator below

Select * from tableA A left outer join tableB B on A.a1=B.b1 where B.b2= 'monkey' or A.a3 ='Tom'

But I want a query without using "or" operator and union

Simple queries possibly involving joins or subqueries is expected

A tool that can't handle OR in a WHERE clause is not useable. Get another one. — juergen d, Jan 31 '20 at 07:41
If you want true LEFT JOIN result, move the monkey condition to the ON clause. (As it is now, you get regular INNER JOIN result.) — jarlh, Jan 31 '20 at 07:44
The tool generates the SQL and runs it in db. But designed such that or can't given as input in filter condition part. Any solution s? — amarender reddy jali, Jan 31 '20 at 07:45
What does 'and union' mean? Do you realize `select * from t where x or y` is `select * from t where x union select * from t where y`? Do you realize `t left join u on x where u.c=... and ...` is `t inner join u on x where u.c=... and ...`? Do you realize `t left join u on x` is `t inner join u on x union all` unmatched t rows extended by null? Also `x or y` is `not (not x and not y)`. Please be more specific about how your tool transforms & what syntax is allowed or you're just asking people to guess in the dark. PS `where` is a 'clause' taking a 'predicate'. — philipxy, Jan 31 '20 at 08:14
I get it. In my tool I have only option to create joins and I can write anything in joins conditions . But when u are creating filter conditions "where" , I can only give like a.col1 in (...) And a.col2 = 'simba' .. such conditions. And I need simplest query which can be inserted in between joins or something like that . My tool can not process into ,with any other — amarender reddy jali, Jan 31 '20 at 08:31
Please clarify via edits, not comments. PS Likely there are many more options for you to achieve your larger goals than what you are asking for in this post. I realize that this is a reasonable place to start. PS There are [other](https://stackoverflow.com/q/10900289/3404097) Q&As re such tools that might help you. Also search [dba.se]. — philipxy, Jan 31 '20 at 08:37
as currently written your first query will return all columns from both tableA and tableB, while the second query will return all columns from tableA; net result is the first query returns more columns ... so you can't really 'union/or' these 2x queries (as is) due to the mismatch in the number of columns — markp-fuso, Feb 01 '20 at 01:53

score 0 · Answer 1 · answered Jan 31 '20 at 07:46

Try inserting both the resultsets into new table and selecting the table

    Select * into tableB from tableA A left outer join tableB B on A.a1=B.b1 where 
    A.a1 
    =20200131 and B.b2= 'monkey'

    insert into tableB
    Select * from tableA where A.a1 =20200131 and   A.a3 ='Tom'


    select * from tableB

Naveen Kumar · Answer 2 · 2020-01-31T08:12:49.153

I think without the use of UNION/OR, will lead to more complex query. You can consider the following solution.

SELECT *
  FROM tableA a
  where a.a1=20200131
    and (   exists (select null from tableB b
                      WHERE a.a1 = b.b1
                        and b.b2='monkey')
         or exists (SELECT null FROM tableA c
                      WHERE a.a1 = c.a1
                        AND c.a3 = 'Tom'
                      )
        )

Or you can create temp table then insert the output of both the queries.

How to join result sets without using union or "or" in where condition.?

2 Answers2