How to use SQL join

Source: Internet
Author: User
Tags how to use sql

(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

City
id_p LastName FirstName Address
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:

City
id_p LastName FirstName Address
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:

City
id_p LastName FirstName Address
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:

City
id_p LastName FirstName Address
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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.