(Transfer from W3school Tutorial: Http://www.w3school.com.cn,W3School is a good online tutorial, simple and efficient!) )
The different SQL join types are listed below, along with their differences:
JOIN: Indicates that if there is at least one match in the table, the row is returned
Left JOIN: Returns all rows from the table, even if there is no match in the right table
Right JOIN: Returns all rows from the correct table even if there is no match in the left table
Full JOIN: Returns a row if there is a match in one of the tables
First, INNER JOIN keyword
SELECT column_name (s) from table_name1 INNER JOIN on table_name1.column_name=table_name2.column_name
Note: Inner join is the same as join.
The original table (in the example):
"Persons" table
id_p |
LastName |
FirstName |
Address |
| City
1 |
Adams |
John |
Oxford Street |
London |
2 |
Bush |
George |
Fifth Avenue |
New York |
3 |
Carter |
Thomas |
Changan Street |
Beijing |
"Orders" table
Id_o |
OrderNo |
id_p |
1 |
77895 |
3 |
2 |
44678 |
3 |
3 |
22456 |
1 |
4 |
24562 |
1 |
5 |
34764 |
65 |
INNER JOIN (inner join) instance
Now, we want to list everyone's orders. You can use the following SELECT statement:
Select Persons.lastname, Persons.firstname, Orders.orderno from Persons Inner Join Orders on Persons.id_p=orders.id_porder by Persons.lastname
Result set:
LastName |
FirstName |
OrderNo |
Adams |
John |
22456 |
Adams |
John |
24562 |
Carter |
Thomas |
77895 |
Carter |
Thomas |
44678 |
The INNER JOIN keyword returns a row when there is at least one match in the table. If the rows in "Persons" do not match in "Orders," the rows are not listed.
Second, LEFT JOIN keyword
The LEFT JOIN keyword returns all rows from the table (TABLE_NAME1), even if there are no matching rows in the right table (table_name2).
SELECT column_name (s) from table_name1 Left JOIN on table_name1.column_name=table_name2.column_name
Note: In some databases, the left join is called the left OUTER join.
The original table (used in the example):
"Persons" table:
id_p |
LastName |
FirstName |
Address |
| City
1 |
Adams |
John |
Oxford Street |
London |
2 |
Bush |
George |
Fifth Avenue |
New York |
3 |
Carter |
Thomas |
Changan Street |
Beijing |
"Orders" table:
Id_o |
OrderNo |
id_p |
1 |
77895 |
3 |
2 |
44678 |
3 |
3 |
22456 |
1 |
4 |
24562 |
1 |
5 |
34764 |
65 |
Now, we want to list all the people and their orders (if any).
You can use the following SELECT statement:
SELECT Persons.lastname, Persons.firstname, Orders.orderno from Persons Left JOIN Orders on Persons.id_p=orders.id_pORDER by Persons.lastname
Result set:
LastName |
FirstName |
OrderNo |
Adams |
John |
22456 |
Adams |
John |
24562 |
Carter |
Thomas |
77895 |
Carter |
Thomas |
44678 |
Bush |
George |
|
The left JOIN keyword returns all rows from the table (Persons), even if there are no matching rows in the right table (Orders).
Third, right join keyword
The RIGHT join keyword returns all rows from the table_name2, even if there are no matching rows in the coordinates (TABLE_NAME1).
SELECT column_name (s) from table_name1 Right JOIN on table_name1.column_name=table_name2.column_name
Note: In some databases, the right join is called a OUTER join.
The original table (used in the example):
"Persons" table:
id_p |
LastName |
FirstName |
Address |
| City
1 |
Adams |
John |
Oxford Street |
London |
2 |
Bush |
George |
Fifth Avenue |
New York |
3 |
Carter |
Thomas |
Changan Street |
Beijing |
"Orders" table:
Id_o |
OrderNo |
id_p |
1 |
77895 |
3 |
2 |
44678 |
3 |
3 |
22456 |
1 |
4 |
24562 |
1 |
5 |
34764 |
65 |
Now, we want to list all the orders and the people who ordered them (if any).
You can use the following SELECT statement:
SELECT Persons.lastname, Persons.firstname, Orders.orderno from Persons Right JOIN Orders on Persons.id_p=orders.id_pORDER by Persons.lastname
Result set:
LastName |
FirstName |
OrderNo |
Adams |
John |
22456 |
Adams |
John |
24562 |
Carter |
Thomas |
77895 |
Carter |
Thomas |
44678 |
|
|
34764 |
The right JOIN keyword returns all of the rows from the table (Orders), even if there are no matching rows in the left table (Persons).
Iv. Full Join keyword
The full join keyword returns a row whenever there is a match in one of the tables.
SELECT column_name (s) from table_name1 Full JOIN on table_name1.column_name=table_name2.column_name
Note: In some databases, full join is called full OUTER join.
The original table (used in the example):
"Persons" table:
id_p |
LastName |
FirstName |
Address |
| City
1 |
Adams |
John |
Oxford Street |
London |
2 |
Bush |
George |
Fifth Avenue |
New York |
3 |
Carter |
Thomas |
Changan Street |
Beijing |
"Orders" table:
Id_o |
OrderNo |
id_p |
1 |
77895 |
3 |
2 |
44678 |
3 |
3 |
22456 |
1 |
4 |
24562 |
1 |
5 |
34764 |
65 |
Now, we want to list all the people, their orders, all the orders, and the people who ordered them.
You can use the following SELECT statement:
SELECT Persons.lastname, Persons.firstname, Orders.orderno from Persons Full JOIN Orders on Persons.id_p=orders.id_pORDER by Persons.lastname
Result set:
LastName |
FirstName |
OrderNo |
Adams |
John |
22456 |
Adams |
John |
24562 |
Carter |
Thomas |
77895 |
Carter |
Thomas |
44678 |
Bush |
George |
|
|
|
34764 |
The full JOIN keyword returns all rows from the left table (Persons) and the right table (Orders). If the rows in "Persons" do not match in the table "orders", or if the rows in "orders" do not have a match in the table "Persons", the rows are also listed.
How to use SQL join