Left outer join and Right outer join
please help me on this. I have 2 tables: Table A (43 rows) and Table B (925) rows.
When i do table A left outer join B, how many rows shall i expect and when i do table A right outer join table B how many rows shall i expect? Lets say A and B are joined on fid and all rows in table b have a matching fid to table A. I am so confused please help me to understand left and right outer join and the number of rows expected to be returned. THanks
This will help
http://www.w3schools.com/sql/sql_join_left.asp --> Left outer join
http://www.w3schools.com/sql/sql_join_right.asp --> Right Outer join
Let's look at it step by step and start with an inner join. TableA has 43 rows and TableB has 925 rows. How many rows do you get when you join the tables? Answer: You cannot say, because this depends on your join criteria.
Let's make the example smaller: TableA = 3 records, TableB = 3 records. If you find no match on your join criteria (eg you join by department, but tableA only contains records for department 1 and 2, whereas tableB only contains records for departments 3 and 4) then your result contains zero records. If on the other hand all records match (eg you join by department only and both tables contain only records for department 1) then you get all combinations = 3 x 3 = 9 records.
Here is another example with 3 + 3 records:
tableA:
joinkey value 1 A1 2 A2 3 A3
tableB:
joinkey value 1 B11 2 B21 2 B22
select * from tableA inner join tableB on tableA.joinkey = tableB.joinkey;
This query gets you 3 records:
joinkey valueA valueB 1 A1 B11 2 A2 B21 2 A2 B22
A1 found one match in tableB, A2 found two, and A3 found none.
So what is the difference with an outer join? With an outer join you get all records from the inner join plus all records that have no match in the outer-joined table.
select * from tableA left outer join tableB on tableA.joinkey = tableB.joinkey;
select * from tableB right outer join tableA on tableA.joinkey = tableB.joinkey;
The outer-joined table in these two queries is tableB. The queries are equivalent.
Result:
joinkey valueA valueB 1 A1 B11 2 A2 B21 2 A2 B22 3 A3 null
Now you get a record for A3 which has no match in the outer-joined table.
Let's now look at the opposite join:
select * from tableB left outer join tableA on tableA.joinkey = tableB.joinkey;
select * from tableA right outer join tableB on tableA.joinkey = tableB.joinkey;
These queries again are equal. The outer-joined table is tableA now. But there is no record in tableB that has no match in the outer-joined tableA, so the result is just the same as it was with the inner join:
joinkey valueA valueB 1 A1 B11 2 A2 B21 2 A2 B22
Just one remark: You see that you can use both, the left outer join and the right outer join for the same query - simply swap tables. So in order not to get confused, always use one. I recommend you stay away from the right outer join and stick to the left outer join, which is what most people are used to. (Which also means you'll find more examples on it on the Internet :-)
链接地址: http://www.djcxy.com/p/24920.html下一篇: 左外连接和右外连接