Basically I have 2 tables and I was to do a left join and pull out all rows which has a value in the right hand table or doesn't exist.

eg.

SELECT a.a, a.b, b.a, b.c FROM a LEFT JOIN b ON a.a = b.a WHERE b.c = 'A';

So basically I want it to return.

a b a c 1 1 null null 2 2 2 A

The problem here is that if b.c = 'A', it can't be NULL ;) So you should use something like this WHERE NVL(b.c, 'A') = 'A' (oracle Syntax, for MySQL it's IFNULL )

Gordon Heydon wrote:

Hi,

I have been trying to work out a small sql issue, and I was wondering if someone might be able to help me.

Basically I have 2 tables and I was to do a left join and pull out all rows which has a value in the right hand table or doesn't exist.

eg.

SELECT a.a, a.b, b.a, b.c FROM a LEFT JOIN b ON a.a = b.a WHERE b.c = 'A';

So basically I want it to return.

a b a c 1 1 null null 2 2 2 A

etc.

Or do I need to 2 selects and merge them. When I do this now I get more of an inner join instead of a left join. I am not sure how I say "or doesn't exist".

Thanks in advance. Gordon.

...WHERE b.c = 'A' OR b.c IS NULL;

On 2/2/06, Gordon Heydon <gordon@heydon.com.au> wrote:

SELECT a.a, a.b, b.a, b.c FROM a LEFT JOIN b ON a.a = b.a WHERE b.c = 'A';

Another way to write this is: SELECT a.a, a.b, b.a, b.c FROM a LEFT JOIN b ON a.a = b.a and 'A' = b.c; Basically you put the condition into the left outer join so that when the values are present, it qualifies them on "='A'" but if there are none it will just show an empty row as you expect from the left outer join. I'm not sure if that syntax works in postgresql or mysql as I didn't test it, but I have some certainty about other sql engines. Regards, Greg