MySQL joins - no full outer joins - visualization

http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html

USE LEFT OUTER JOIN.

SELECT * FROM TableA
LEFT OUTER JOIN TableB
ON TableA.name = TableB.name

id  name       id    name
--  ----       --    ----
1   Pirate     2     Pirate
2   Monkey     null  null
3   Ninja      4     Ninja
4   Spaghetti  null  null

Left outer join produces a complete set of records from Table A, with the matching records (where available) in Table B. If there is no match, the right side will contain null.

http://www.khankennels.com/blog/index.php/archives/2007/04/20/getting-joins/

LEFT JOIN
A left join returns all the records in the “left” table (T1) whether they have a match in the right table or not.

If, however, they do have a match in the right table – give me the “matching” data from the right table as well. If not – fill in the holes with null.

In MySQL LEFT JOIN and LEFT OUTER JOIN are identical. The OUTER keyword is optional. 

1
technically, what you need is a LEFT JOIN, not a 'OUTER' type of join.



2
Thus the 'real' left OUTER join: use a WHERE clause. To exclude the 'null' b_id's.

SELECT * FROM TableA
LEFT OUTER JOIN TableB
ON TableA.name = TableB.name
WHERE TableB.id IS null

id  name       id     name
--  ----       --     ----
2   Monkey     null   null
4   Spaghetti  null   null

To produce the set of records only in Table A, but not in Table B, we perform the same left outer join, thenexclude the records we don't want from the right side via a where clause.

http://www.wellho.net/mouth/2790_Joining-a-MySQL-table-from-within-a-Python-program.html

http://stackoverflow.com/questions/7409472/mysql-python-combine-results