title: SQL Alias of joined tables
body: <p>I have a query like this:</p>
  
  <pre><code>select a1.name, b1.info 
   from (select name, id, status 
           from table1 a) as a1
  right outer join (select id, info 
                      from table2 b) as b1 on (a1.id = b1.id)
  </code></pre>
  
  <p>I only want to include everything where a1.status=1 and since I'm using an outer join, I can't just add a <code>where</code> constraint to table1, because all info from table2 that I want to be excluded will still be there, just without the name. I was thinking something like this:</p>
  
  <pre><code> select z1.name, z1.info 
     from ((select name, id, status 
              from table1 a) as a1
  right outer join (select id, info 
                      from table2 b) as b1 on (a1.id = b1.id)) as z1 
    where z1.status = 1
  </code></pre>
  
  <p>but I don't think that's legal. </p>
  
  <p>EDIT:
  As described below, an outer join actually doesn't make sense for what I'm trying to do. What if, for example, I want all the data from table2 where status!=1 in table1, inclusive of all data where a corresponding ID does not at all exist in table1. Thus I would need an outer join of all data from table2, but still want to exclude those entries where the status=1.</p>
  
  <p>Equivalent to this:</p>
  
  <pre><code> select z1.name, z1.info 
     from ((select name, id, status 
              from table1 a) as a1
  right outer join (select id, info 
                      from table2 b) as b1 on (a1.id = b1.id)) as z1 
    where z1.status != 1
  </code></pre>