Joins are fundamental concepts in SQL and they are prefer by professionals as they are faster then sub-queries. These 3 easy MySQL examples below are a little effort to let visitors understand difference among Inner join, Left Outer join and Full outer join.

Assuming we’re joining on columns with no duplicates (which is a very common case), an inner join of P and Q gives the result of P intersect Q, i.e. the inner part of a venn diagram intersection and an outer join of P and Q gives the results of P union Q, i.e. the outer parts of a venn diagram union.

### Easy Examples

Suppose we have two tables P and Q, with a single column each, and data as follows:

0 1 2 3 4 5 6 |
P Q - - 1 3 2 4 3 5 4 6 |

Here you can clearly sense that (1,2) are unique to P, (3,4) are common in both tables, and (5,6) are unique to Q.

### Inner Join

**An inner join gives the rows they have in common** means using either of the equivalent queries gives the intersection of the two tables (the two rows they have in common in our example).

0 1 2 3 4 5 6 7 |
select p.*,q.* from p,q where p.p = q.q; select * from p INNER JOIN q on p.p = q.q; p | q --+-- 3 | 3 4 | 4 |

### Left outer join

A left outer join will give all rows in P, plus any common rows in Q.

0 1 2 3 4 5 6 7 8 |
select * from p LEFT OUTER JOIN q on p.p = q.q; p | q --+----- 1 | null 2 | null 3 | 3 4 | 4 |

### Full outer join

All the rows in P and all the rows in Q, union of P and Q is given by full outer join. If something in P doesn’t have a corresponding datum in Q, then the Q portion is null, and vice versa.

0 1 2 3 4 5 6 7 8 9 10 |
select * from p FULL OUTER JOIN q on p.p = q.q; p | q -----+----- 1 | null 2 | null 3 | 3 4 | 4 null | 6 null | 5 |

Further to parse accurately, an outer join will give the results of A intersect B in addition to one of the following: all of P (left join), all of Q (right join) or all of P and all of Q (full join).

Also remember that Join does not guarantee an order, you would need to add an ORDER BY clause.