Inner join with if condition

问题:

I was trying to write a query with inner join only if RepID of Table1 exists in Table2, if not do not join table2. With the query that i used below, i do not get from both the tables if repID doesnot exist in Table2. How is it possible? I am using sql server 2005. Thank you in advance!

Select * from Table1
inner join Table2 on Table1.RepID = Table2.RepID
where Table1.Date = @Date
order by Table1.Date desc

回答1:

An inner join will only return a row if matches are found in both sides of the join. If you're looking for something that will return all rows from Table1 but only records from Table2 when a match is found, you want a left outer join:

select * from Table1 as t1
left outer join Table2 as t2
    on t1.RepID = t2.RepID
where t1.Date = @Date
order by t1.Date desc

回答2:

Try "LEFT JOIN" instead of "INNER JOIN".

The word "LEFT" means "Always include every record from the table on the LEFT of the join," in this case Table1, since you will write: Table1 LEFT JOIN Table2, and "Table1" is on the left of that pair! :-)

回答3:

it sounds like what you actually want is a left outer join, isnt it?

回答4:

SELECT * 
    FROM Table1
        LEFT JOIN Table2 
            ON Table1.RepID = Table2.RepID
    WHERE Table1.Date = @Date
    ORDER BY Table1.Date DESC;

回答5:

That's what outer joins are for.

Select * from Table1
left outer join Table2 on Table1.RepID = Table2.RepID
where Table1.Date = @Date
order by Table1.Date desc