JOIN is used to combine records from two tables or more in relational database. There are 3 types of JOINs in SQL: CROSS JOIN, INNER JOIN, and OUTER JOIN. OUTER JOIN itself can be further divided into left OUTER JOIN, right OUTER JOIN and full OUTER JOIN. Example in this section use the following 2 tables.

Table: Employee

EmpID	FirstName	LastName	DeptID	City	Country
EMP001	Maria	Anders	ACC	Berlin	Germany
EMP002	Ernst	Handel	IT	Graz	Austria
EMP003	Helen	Bennett	ACC	Cowes	UK
EMP004	Paula	Wilson	IT	Albuquerque	USA
EMP005	Wong	Liu		Butte	USA

Table: Department

DeptID	DeptName
ACC	Account
HR	Human Resource
SALES	Sales & Marketing
IT	Information Technology

1) CROSS JOIN

CROSS JOIN is the foundation for INNER JOIN. The CROSS JOIN list the tables to be joined without putting any filtering criteria. Example of CROSS JOIN are as follows:

SELECT * FROM Employee    CROSS JOIN Department

SELECT * FROM Employee,     Department

Both SQL statements shown above will returns the same resultset. The only different is the syntax. The first format is not commonly used while the second format is quite common. A lot of people who are using the second format actually do not realize that they are using CROSS JOIN because it looks like the basic SELECT statement.

Resultset1

EmpID	FirstName	LastName	DeptID	City	Country	DeptID	DeptName
EMP001	Maria	Anders	ACC	Berlin	Germany	ACC	Account
EMP001	Maria	Anders	HR	Berlin	Germany	HR	Human Resource
EMP001	Maria	Anders	SALES	Berlin	Germany	SALES	Sales & Marketing
EMP001	Maria	Anders	IT	Berlin	Germany	IT	Information Technology
EMP002	Ernst	Handel	ACC	Graz	Austria	ACC	Account
EMP002	Ernst	Handel	HR	Graz	Austria	HR	Human Resource
EMP002	Ernst	Handel	SALES	Graz	Austria	SALES	Sales & Marketing
EMP002	Ernst	Handel	IT	Graz	Austria	IT	Information Technology
EMP003	Helen	Bennett	ACC	Cowes	UK	ACC	Account
EMP003	Helen	Bennett	HR	Cowes	UK	HR	Human Resource
EMP003	Helen	Bennett	SALES	Cowes	UK	SALES	Sales & Marketing
EMP003	Helen	Bennett	IT	Cowes	UK	IT	Information Technology
EMP004	Paula	Wilson	ACC	Albuquerque	USA	ACC	Account
EMP004	Paula	Wilson	HR	Albuquerque	USA	HR	Human Resource
EMP004	Paula	Wilson	SALES	Albuquerque	USA	SALES	Sales & Marketing
EMP004	Paula	Wilson	IT	Albuquerque	USA	IT	Information Technology
EMP005	Wong	Liu	ACC	Butte	USA	ACC	Account
EMP005	Wong	Liu	HR	Butte	USA	HR	Human Resource
EMP005	Wong	Liu	SALES	Butte	USA	SALES	Sales & Marketing
EMP005	Wong	Liu	IT	Butte	USA	IT	Information Technology

From the above example, can you see that CROSS JOIN is actually returning all the possible combination of records in Employee table and Department table. 

INNER JOIN

SELECT <column-list> FROM <table1> INNER JOIN <table2> ON table1.field = table2.field

INNER JOIN is the most commonly used JOIN and considered as the default JOIN. It takes all records from table1 and find matching records from table 2. If no matching is found, record in table1 will not be returned. One row of record will be returned for each matching found in table2. If 1 record in table1 match with 3 rows in table2, then 3 records will be returned. The following example will return records in resultset2.

SELECT * FROM Employee
    INNER JOIN Department
       ON Employee.DepartmentID = Department.DepartmentID

EmpID	FirstName	LastName	DeptID	City	Country	DeptID	DeptName
EMP001	Maria	Anders	ACC	Berlin	Germany	ACC	Account
EMP002	Ernst	Handel	IT	Graz	Austria	IT	Information Technology
EMP003	Helen	Bennett	ACC	Cowes	UK	ACC	Account
EMP004	Paula	Wilson	IT	Albuquerque	USA	IT	Information Technology

Notice that EMP005 is not included in the returned resultset because the DepartmentID to does not match with any record in Department table. LEFT OUTER JOIN LEFT OUTER JOIN is similar to INNER JOIN but it will return all records from table1 no matter it match any records in table2 or not. If no matching is found in table2, then each column in table2 will be returned as NULL values. FOR Example: SELECT * FROM Employee     LEFT OUTER JOIN Department        ON Employee.DepartmentID = Department.DepartmentID

EmpID	FirstName	LastName	DeptID	City	Country	DeptID	DeptName
EMP001	Maria	Anders	ACC	Berlin	Germany	ACC	Account
EMP002	Ernst	Handel	IT	Graz	Austria	IT	Information Technology
EMP003	Helen	Bennett	ACC	Cowes	UK	ACC	Account
EMP004	Paula	Wilson	IT	Albuquerque	USA	IT	Human Resource
EMP005	Wong	Liu		Butte	USA	NULL	NULL

RIGHT OUTER JOIN

RIGHT OUTER JOIN is similar to LEFT OUTER JOIN but this time, all the records in table2 or Department table will be returned. For example:

SELECT * FROM Employee
    RIGHT OUTER JOIN Department
       ON Employee.DepartmentID = Department.DepartmentID

EmpID	FirstName	LastName	DeptID	City	Country	DeptID	DeptName
EMP001	Maria	Anders	ACC	Berlin	Germany	ACC	Account
EMP002	Ernst	Handel	IT	Graz	Austria	IT	Information Technology
EMP003	Helen	Bennett	ACC	Cowes	UK	ACC	Account
EMP004	Paula	Wilson	HR	Albuquerque	USA	IT	Human Resource
NULL	NULL	NULL	NULL	NULL	NULL	HR	Human Resource
NULL	NULL	NULL	NULL	NULL	NULL	SALES	Sales & Marketing

FULL OUTER JOIN FULL OUTER JOIN is the combination of LEFT OUTER JOIN AND RIGHT OUTER JOIN. FULL JOIN shows results from both tables and missing match will be filled with NULL. SELECT * FROM Employee     FULL OUTER JOIN Department        ON Employee.DepartmentID = Department.DepartmentID

EmpID	FirstName	LastName	DeptID	City	Country	DeptID	DeptName
EMP001	Maria	Anders	ACC	Berlin	Germany	ACC	Account
EMP002	Ernst	Handel	IT	Graz	Austria	IT	Information Technology
EMP003	Helen	Bennett	ACC	Cowes	UK	ACC	Account
EMP004	Paula	Wilson	HR	Albuquerque	USA	IT	Human Resource
EMP005	Wong	Liu		Butte	USA	NULL	NULL
NULL	NULL	NULL	NULL	NULL	NULL	HR	Human Resource
NULL	NULL	NULL	NULL	NULL	NULL	SALES	Sales & Marketing

.