CODEWITHSUNDEEP
sqlsql-server
Mike
Mike

Reputation: 6050

Cartesian join two tables with no records

I have to join Table A (tax related) to Table B (customer related)

I pull at most 1 record but sometimes no record.

Now I need to return the combined record to the user

I though doing a simple Cartesian product would have work

SELECT * FROM TableA, TableB

but that does not work if TableA or TableB is empty

I would do a full outer join but right now do not have anything to join on. I could create temp tables with identity columns and then join on them (since 1 = 1)

But I was looking for a different way?

Thank you

Upvotes: 5

Views: 2001

Answers (1)

Andomar
Andomar

Reputation: 238246

Per your own suggestion, you could use a full outer join to guarantee a row:

select  *
        TableA a
full outer join
        TableB b
on      1=1

To always return at least one row, even if TableA and TableB are emtpy, you could use a fake table:

select  *
from    (
        select  1 as col1
        ) fake
left join
        TableA a
on      1=1
left join
        TableB b
on      1=1

Upvotes: 6

Related Questions