SQL Joins

A relational database has a number of tables containing data and those tables are linked together with some relationship. The JOIN statement is used to query data from two or more tables whose columns share some relationship. There are basically four types of JOIN statements:

INNER JOIN or simply JOIN

LEFT JOIN or LEFT OUTER JOIN

RIGHT JOIN or RIGHT OUTER JOIN

FULL JOIN

Inner Join or Join

The INNER JOIN keyword returns rows when there is at least one match in both tables. The syntax is:

SELECT column_name1,column_name2,....
FROM table_name1
INNER JOIN table_name2 
ON table_name1.column_name=table_name2.column_name;

Let's see an example. Take the two tables, "sailors" and "boats" given below:
sailors

sid	sname	age
22	Dustin	45
31	Lubber	55
15	Rustin	38
21	Akins	35
50	Fowler	49

boats

bid	bname	sid
101	Interlake	15
102	Clipper	21
103	Interlake	31
104	Marine	21
105	Clipper	68

Now, suppose we want to see which boat is bought by which sailor. The SQL for the same is given below:

SELECT sailors.sname, boats.bnanme
FROM sailors
INNER JOIN boats
ON sailors.sid=boats.sid
ORDER BY sailors.sname;

The result-set will be:

sname	bname
Akins	Clipper
Akins	Marine
Lubber	Interlake
Rustin	Interlake

Left Join or Left Outer Join

The LEFT JOIN statement returns all rows from the left table, even if there are no matches in the right table. The syntax is as follows:

SELECT column_name1, column_name2,....
FROM table_name1
LEFT JOIN table_name2 
ON table_name1.column_name=table_name2.column_name;

Now, we take the example of the two tables, "sailors" and "boats" given above. Suppose, we want to select all the sailors and the purchases that they made. The SQL is given below:

SELECT sailors.sname, boats.bname
FROM sailors
LEFT JOIN boats
ON sailors.sid=boats.sid
ORDER BY sailors.sname;

The result of the above query will be:

sname	bname
Akins	Clipper
Akins	Marine
Dustin
Fowler
Lubber	Interlake
Rustin	Interlake

Right Join or Right Outer Join

The RIGHT JOIN statement returns all rows from the right table, even if there are no matches in the left table. The syntax for RIGHT JOIN statement is given below:

SELECT column_name1, column_name2,....
FROM table_name1
RIGHT JOIN table_name2
ON table_name1.column_name=table_name2.column_name;

Considering the two tables, "sailors" and "boats" as examples again, suppose we want to know which boat is bought by which sailor. The SQL would be:

SELECT sailors.sname, boats.bname
FROM sailors
RIGHT JOIN boats
ON sailors.sid=boats.sid;

Let's have a look at the result-set:

sname	bname
Rustin	Interlake
Akins	Clipper
Lubber	Interlake
Akins	Marine
	Clipper

Full Join

The FULL JOIN statement returns a row when there is a match in any one of the tables. First all the matches from the left table will be returned and then all the matching rows from the right table will be returned. The syntax of the FULL JOIN statement will be:

SELECT column_name1, column_name2, ....
FROM table_name1
FULL JOIN table_name2
ON table_name1.column_name=table_name2.column_name;

Now we want to see which sailor bought which boat and vice-versa. The SQL will be:

SELECT sailors.sname, boats.bname
FROM sailors
FULL JOIN boats
ON sailors.sid=boats.sid
ORDER BY sailors.sname;

The result-set will be what we need and will look like this:

sname	bname
Akins	Clipper
Akins	Marine
Dustin
Fowler
Lubber	Interlake
Rustin	Interlake
	Clipper

SQL Tutorial

SQL Joins

Inner Join or Join

Left Join or Left Outer Join

Right Join or Right Outer Join

Full Join

'w3reference : Learn by examples ... Advanced to beginner's tutorials ...'