SQL: JOIN with nested queries

Question

I am trying to perform this join operation. As I am new to sql I am finding problems understanding the syntax and stuff.

What do you think is wrong with the following query:

select top 1 * 
from 
    (select * 
     from dbo.transaction_unrated 
        where transaction_date >= '2012/05/01' 
            and transaction_date < '2012/06/01' 
            and content_provider_code_id in (1)
    )   FULL OUTER JOIN 
    (select * 
     from dbo.transaction_rated 
        where transaction_date >= '2012/05/01' 
            and transaction_date < '2012/06/01' 
            and entity_id in (1) 
            and mapping_entity_id = 1)
    ) 
    ON dbo.transaction_unrated.cst_id = dbo.transaction_rated.unrated_transaction_id

Answer 1

You need to alias your derived tables.

select top 1 * 
from 
(
     select * 
     from dbo.transaction_unrated 
        where transaction_date >= '2012/05/01' 
            and transaction_date < '2012/06/01' 
            and content_provider_code_id in (1)
) rsQuery1  
FULL OUTER JOIN 
(
     select * 
     from dbo.transaction_rated 
        where transaction_date >= '2012/05/01' 
            and transaction_date < '2012/06/01' 
            and entity_id in (1) 
            and mapping_entity_id = 1)
) rsQuery2 ON rsQuery1.cst_id = rsQuery2.unrated_transaction_id

FULL OUTER JOIN is also unusual (in my experience). Are you sure that's what you want? Typically you will do an INNER JOIN which brings back rows that match on your criteria in both tables, or you will let one table be the driver and do a LEFT or RIGHT OUTER JOIN which will bring back all the rows in the driving table whether or not there is a match in the other table. A FULL OUTER JOIN will bring back all the rows in both tables regardless of whether they match.