I write SQL queries (and joins) every once in a while, but never had to deal with such things as “left outer joins” or “full outer joins” and so forth. I always found this a little confusing and could not remember the differences after reading about them.
Today I had a crazy idea – why not actually TRY using the different queries and see what happens? Consider these tables:
Table A
| id | common | name |
| 1 | a | Name 1 |
| 2 | b | Name 2 |
Table B
| id | common | title |
| 1 | b | Title 1 |
| 2 | c | Title 2 |
Here is what I found when I do various joins of table A to table B on a common column:
- Inner Joins are the same as the regular joins you have been doing for a while. I replaced my query’s ‘join’ statement with ‘inner join’ and got the same result (as well as reading that these were the same). Note that this means that any rows from either table which do not intersect are not part of the result. The remaining joins address this.
The query: select * from table_a inner join table_b where table_a.common = table_b.common;
Inner Join A and B
| a.id | a.common | a.name | b.id | b.common | b.title |
| 2 | b | Name 2 | 1 | b | Title 1 |
- Left Outer Joins are the same as an Inner Join of A and B, PLUS all the rows from A that did not match a row from B, with all the fields from the B side set to NULL for those rows. Think of A as being on the left side.
The query: select * from table_a left join table_b on table_a.common = table_b.common;
Left Outer Join A and B
| a.id | a.common | a.name | b.id | b.common | b.title |
| 1 | a | Name 1 | NULL | NULL | NULL |
| 2 | b | Name 2 | 1 | b | Title 1 |
- Right Outer Joins are the same as an Inner Join of A and B, PLUS all the rows from B that did not match a row from A, with all the fields from the A side set to NULL for those rows. Think of B as being on the right side.
The query: select * from table_a right join table_b on table_a.common = table_b.common;
Right Outer Join A and B
| a.id | a.common | a.name | b.id | b.common | b.title |
| 2 | b | Name 2 | 1 | b | Title 1 |
| NULL | NULL | NULL | 2 | c | Title 2 |
- MySQL does not support Full Outer Joins natively (there are some work-arounds). However, it is roughly like the union of Left Outer Joins and Right Outer Joins according to what I have read. Seehttp://www.xaprb.com/blog/2006/05/26/how-to-write-full-outer-join-in-mysql/
Full Outer Join A and B
| a.id | a.common | a.name | b.id | b.common | b.title |
| 1 | a | Name 1 | NULL | NULL | NULL |
| 2 | b | Name 2 | 1 | b | Title 1 |
| NULL | NULL | NULL | 2 | c | Title 2 |
No comments:
Post a Comment