Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
CREATE TABLE "ATABLE1"
  (
    "COLUMN1" VARCHAR2(20 BYTE),
    "COLUMN2" VARCHAR2(20 BYTE)
  );

CREATE TABLE "ATABLE2"
  (
    "COLUMN1" VARCHAR2(20 BYTE),
    "COLUMN2" VARCHAR2(20 BYTE)
  );

Insert into ATABLE1 (COLUMN1,COLUMN2) values ('A','1');
Insert into ATABLE1 (COLUMN1,COLUMN2) values ('B','2');

Insert into ATABLE2 (COLUMN1,COLUMN2) values ('A',null);
Insert into ATABLE2 (COLUMN1,COLUMN2) values ('A','1');
Insert into ATABLE2 (COLUMN1,COLUMN2) values ('A','2');

select ATABLE1.column1, count(ATABLE2.column1) 
    from ATABLE1 Left OUTER JOIN ATABLE2 on ATABLE1.column1 = atable2.column1
    GROUP BY ATABLE1.column1;

Result

COLUMN1              COUNT(ATABLE2.COLUMN1) 
-------------------- ---------------------- 
A                    3                      
B                    0    

This works as expected. The thing is that I always want all rows from ATABLE1 to be shown and also apply some restrictions.

select ATABLE1.column1, count(ATABLE2.column1) 
    from ATABLE1 Left OUTER JOIN ATABLE2 on ATABLE1.column1 = atable2.column1
    where atable2.column2 = '1'
    GROUP BY ATABLE1.column1;


COLUMN1              COUNT(ATABLE2.COLUMN1) 
-------------------- ---------------------- 
A                    1                      

Why are not all columns from ATABLE1 being shown even with left join? How can I make them appear?

Thanks a lot in advance.

share|improve this question
    
+1, especially for the effort you have put into setting up the test objects –  Jack Douglas Jan 18 '12 at 13:38
    
"Why are not all columns from ATABLE1 being shown even with left join?" - did you mean to say 'all rows'? –  Jack Douglas Jan 18 '12 at 16:44
    
@JackDouglas yes, that would make more sense. –  BryceAtNetwork23 Jan 18 '12 at 17:28

3 Answers 3

up vote 5 down vote accepted

When you add WHERE filters to the optional/outer table, then you change the query into an INNER JOIN. You need to add the condition into the join, or derived table, or CTE.

select ATABLE1.column1, count(ATABLE2.column1) 
    from ATABLE1 Left OUTER JOIN ATABLE2
         on ATABLE1.column1 = atable2.column1 AND atable2.column2 = '1'
    GROUP BY ATABLE1.column1;
share|improve this answer

Why are not all columns from ATABLE1 being shown even with left join? How can I make them appear?

That's because you're telling your query to only bring back ATABLE.column1. If you take gbn or Jack's queries, just indicate ATABLE1.* (or specifically name each of them) in your SELECT clause:

select ATABLE1.*, count(ATABLE2.column1) 
from ATABLE1 Left OUTER JOIN ATABLE2
     on ATABLE1.column1 = atable2.column1 AND atable2.column2 = '1'
GROUP BY ATABLE1.column1;
share|improve this answer
1  
Interesting, I sure hope the OP isn't confused as to how to list all columns. On the other hand that is what they asked. +1. –  Leigh Riffel Jan 18 '12 at 15:46

An alternative to adding the condition to the join is to test for null in the filter:

select ATABLE1.column1, count(ATABLE2.column1) 
    from ATABLE1 Left OUTER JOIN ATABLE2 on ATABLE1.column1 = atable2.column1
    where atable2.column2 is null or atable2.column2 = '1'
    GROUP BY ATABLE1.column1;

I prefer this variant but you may consider it less readable:

select ATABLE1.column1, count(ATABLE2.column1) 
    from ATABLE1 Left OUTER JOIN ATABLE2 on ATABLE1.column1 = atable2.column1
    where decode(atable2.column2,'1',1,null,1,0)=1
    GROUP BY ATABLE1.column1;

The only reason for doing this is if for some reason you can't put the condition in the filter (which is sometimes the case in a more complex query)

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.