Visual Explanation of SQL Joins

     SQL Joins are used to relate information in different tables. In short and crisp, there are 4 types of SQL joins. Inner, Left Outer, Right Outer and Full joins. Let’s see the differences between these four using Venn diagrams and then can see the SQL commands.

     Let’s say we have two sets of data in our relational database. Table A and Table B with some sort of relation specified by primary and foreign keys. The result of joining these tables together can be visually represented by following diagram:

See how each of the joins are represented.

Select all records from Table A and Table B, where the join condition                                                   is met.

Select all records from Table A, along with records from Table B for which                                                                 the join condition is met.

Select all records from Table B, along with records from Table A for which                                                                  the join condition is met.

Select all records from Table A and Table B, regardless of whether the join                                                                 condition is met or not.

Let's see them now in Tables & SQLs representation. If table A and table B exist like below.

Inner Join:

SQL Query is:

SELECT * FROM TableA 

INNER JOIN TableB ON TableA.Name = TableB.Name;

Result will something look like...

Left Outer Join:

SQL Query is:

SELECT * FROM TableA 

LEFT OUTER JOIN TableB ON TableA.name = TableB.name;

Result will something look like...

Right Outer Join:

SQL Query is:

SELECT * FROM TableA 

RIGHT OUTER JOIN TableB ON TableA.name = TableB.name;

Result will something look like...

Full Join:

SQL Query is:

SELECT * FROM TableA 

FULL JOIN TableB ON TableA.Name = TableB.Name;

Result will something look like...

     Likewise, you can create different types of joins just by adding few additional conditions to the SQL. For example, you can make Full Join as Full Outer Join just by adding a condition. 

     SQLs mentioned here are in ANSI 92/99 syntax. One of the good reasons to use ANSI syntax over the old conventional(+) join syntax is that, there are nil chances of accidentally creating a cartesian product. With more number of tables, there is a chance to miss an implicit join with older join syntax, however, with ANSI syntax you cannot miss any join as you must explicitly mention them. 

Happy Learning.. :)