Writing SQL query where a value maps to all possible values

Question

I have a table

A

name    num
------------
A        1
B        0
C        3

B

num      marks
---------------
1        90
3        40

Query: ??

Result:

name    marks
--------------
A        90
C        40
B        90
B        40

So number "0" in table A corresponds to all num column values. Is there a way this query can be written

I know to get this

name    marks
--------------
A       90
C       40

we can do this

select A.name, B.marks from A,B where A.num = B.num;

Edit: In this case 0 maps to num values 1 and 3

Answer 1

How about:

SELECT A.name, B.marks FROM A, B WHERE A.num = 0 OR A.num = B.num

Answer 2

select A.name, B.marks from A,B where A.num = B.num or A.num=0;

maybe?

Answer 3

select a.name, b.marks from a join b on a.num = b.num
union all
select a.name, b.marks from a, b where a.num = 0

You may find this works better than a where clause with a "OR". Although, to be honest, you may not, there's no real way to get round having to scan the entirety of A and B one way or another. But it's often helpful to query planners for you to split up "OR" conditions into unions.

EDIT:

Just wanted to expand on this a bit. The reason it may be advantageous to do this is if the two parts of the unions benefit from being done in quite different ways. For instance, the second part could start by finding all rows in a with (num=0), whereas the first part is maybe more suitable for doing a full scan of a and index lookups/hash lookups into b, or merging indices on a(num) and b(num).

I'll also say that this query arises from dubious design. You can't have a foreign key constraint from a(num) onto b(num), even though it would make sense to, because (a.num=0) would not match any rows in b. If you added rows in b with (num=0,marks=90) and (num=0,marks=40) then your query could be written easily with a simple inner join. While it's true this means maintaining two rows for each possible mark, that can be done using a trigger that detects inserts with a non-zero value for num, and does an additional insert with num=0. I suppose there are pros and cons to each approach. But I really really like putting fkey constraints in databases, and not having them in a case like this would make me nervous-- what happens if a record in a gets a num that isn't in b? what happens if a row in b gets deleted?

Answer 4

SELECT  A.name
       ,B.marks
FROM    dbo.so913112_A AS A
INNER JOIN dbo.so913112_B AS B
        ON A.num = B.num
           OR A.num = 0

gives:

name marks
---- -----------
A    90
B    90
B    40
C    40

(4 row(s) affected)