INNER JOIN

For this tutorial, we will need to introduce another table, the Orders table in the Northwind database.  Below is a snapshot of the Orders table. (we will use only the first 9 records)

Looking at the Orders table above, notice that in the third column, EmployeeID is used. 
While it wouldn't be tough to lookup who employeeID 5 is in the employees table, this will become tedious and inefficient over time.  What we need is a way to link the two tables together, this is where we would use a join.

In the SQL-92 standard, inner joins can be specified in either the FROM or WHERE clause. This is the only type of join that SQL-92 supports in the WHERE clause. Inner joins 
specified in the WHERE clause are known as old-style inner joins.

INNER JOIN (definition): A join that displays only the rows that have a match in both joined tables.

SELECT field1, field2, field3
FROM table1 INNER JOIN table2
           ON table1.field= table2.field

Example: We want to show which employee these orders belong to.
              (Note that in the SELECT section we must specify which table a field comes from.)

SELECT Employees.LastName, Employees.FirstName, Orders.OrderID
FROM Employees INNER JOIN Orders
           ON Employees.EmployeeID = Orders.EmployeeID

Result: (Notice that OrderID 10258 does not show up in the results.  This is due to no
             matching EmployeeID 99 in the employees table.)

Looking at the last SQL statement, it was somewhat long to type, especially if we want to select more columns.  One way to shorten the typing and make the script more readable is by using a table alias.

You define a table alias in the FROM clause.  We used e as the alias for employee and o as the alias for Orders.  Rewriting the above script using a table alias, we get:

SELECT e.LastName, e.FirstName, o.OrderID
FROM Employees e INNER JOIN Orders o
        ON e.EmployeeID = o.EmployeeID

Running the script again, we should get the same results.

LEFT OUTER JOIN

LEFT OUTER JOIN (definition): All rows from the first-named table (the "left" table, which appears leftmost in the JOIN clause) are included. Unmatched rows in the
right table do not appear.

An OUTER JOIN is a join that includes rows even if they do not have related rows in the joined table. You can create three variations of an outer join to specify the unmatched rows to be included:

A LEFT JOIN is the same as a LEFT OUTER JOIN depending on the database.

LEFT OUTER JOIN (usage)

SELECT field1, field2, field3
FROM table1 LEFT OUTER JOIN table2
        ON table1.field= table2.field

Example: This is basically the same query we used for the INNER JOIN example.
            

SELECT e.LastName, e.FirstName, o.OrderID
FROM Employees e LEFT OUTER JOIN Orders o
        ON e.EmployeeID = o.EmployeeID

Result: Notice that the LEFT OUTER JOIN vs the INNER JOIN

The LEFT OUTER JOIN returned results for ALL employees, even one without orders.

RIGHT OUTER JOIN

RIGHT OUTER JOIN (definition): All rows in the second-named table (the "right" table, which appears rightmost in the JOIN clause) are included. Unmatched rows in the left table are not included.

An OUTER JOIN is a join that includes rows even if they do not have related rows in the joined table. You can create three variations of an outer join to specify the unmatched rows to be included:

A RIGHT JOIN is the same as a RIGHT OUTER JOIN depending on the database.

RIGHT OUTER JOIN (usage)

SELECT field1, field2, field3
FROM table1 RIGHT OUTER JOIN table2
        ON table1.field= table2.field

Example: This is basically the same query we used for the INNER JOIN example.
            

SELECT e.LastName, e.FirstName, o.OrderID
FROM Employees e RIGHT OUTER JOIN Orders o
        ON e.EmployeeID = o.EmployeeID

Result: Notice that the RIGHT OUTER JOIN vs the INNER JOIN

The Right OUTER JOIN returned results for OrderID 10258 even though it did not have a corresponding employee.

The RIGHT and LEFT Joins are basically the same, the only thing matters is the order of the tables. 

FULL OUTER JOIN

FULL OUTER JOIN (definition): All rows in all joined tables are included, whether they
are matched or not.

An OUTER JOIN is a join that includes rows even if they do not have related rows in the joined table. You can create three variations of an outer join to specify the unmatched rows
to be included:

A FULL JOIN is the same as a FULL OUTER JOIN depending on the database.

FULL OUTER JOIN (usage)

SELECT field1, field2, field3
FROM table1 FULL OUTER JOIN table2
        ON table1.field= table2.field

Example: This is basically the same query we used for the INNER JOIN example.
            

SELECT e.LastName, e.FirstName, o.OrderID
FROM Employees e FULL OUTER JOIN Orders o
        ON e.EmployeeID = o.EmployeeID

Result: Notice that the FULL OUTER JOIN vs the INNER JOIN

CROSS JOIN (definition): A cross join returns the Cartesian product of the sets of rows from the joined tables.  Basically all the possible combinations from table1 and table2.

Take for example Table1,

CROSS JOIN with Table2.

The result would be:

Cross Joins are used to generate all possible combinations of records from tables that do not share a common element. If Table1 and Table2 are two sets then cross join = Table1 X Table2. 

Just remember that a CROSS JOIN with large tables could cause your database to come to a screeching halt.  Notice that table1 has 4 records and table2 has 3 records.  The cross join produced 12 rows (3x4=12).  Imagine if both tables had thousands or millions of records... The result would be very large.

For more imformation please click

http://sql.1keydata.com/cn/