SQL – Joins

SQL joins are used to combine rows from more than one table.  I will be setting up a code page and will provide a link when this has been completed.

The different JOINs are as follows:

LEFT (or LEFT OUTER) JOIN: returns all rows from the first or left hand side table, even if there are no matches in the right table.  This is the main JOIN used when querying SQL.

INNER JOIN: returns rows when there is a match in both tables - produces a result set that is limited to the rows where there is a match in both tables for what you are looking for.

RIGHT (or RIGHT OUTER) JOIN: returns all rows from the right table, even if there are no matches in the left table.

FULL (or FULL OUTER) JOIN: returns rows when there is a match in one of the tables.  All rows from both tables are returned.

CROSS JOIN: returns a table with a potentially very large number of rows.  The row count of the result is equal to the number of rows in the first table times the number of rows in the second table.

SELF JOIN: is used to join a table to itself as if the table were two tables, temporarily renaming at least one table in the SQL statement.

CARTESIAN JOIN: no-one uses this – see CROSS JOIN.  Returns the Cartesian product of the sets of records from the two or more joined tables.

This is the best visual I’ve seen from www.codeproject.com (http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins)