Join for SQL uses

Source: Internet
Author: User

SQL join is used to query data from these tables based on the relationship between the columns in two or more tables.

Join and Key

Sometimes in order to get the complete result, we need to get the results from two or more tables. We need to execute the join.

Tables in the database can be linked by keys. The primary key (Primary key) is a column, and the value of each row in the column is unique. In the table, the value of each primary key is unique. The purpose of this is to cross-bind the data between tables without repeating all the data in each table.

Please see the "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

Note that the "id_p" column is the primary key in the Persons table. This means that no two lines can have the same id_p. Even if the names of two people are exactly the same, id_p can distinguish them from each other.

Next, look at the Orders table:

Id_o OrderNo id_p
1 77895 3
2 44678 3
3 22456 1
4 24562 1
5 34764 65

Note that the "id_o" column is the primary key in the Orders table, and the "id_p" column in the Orders table is used to refer to people in the "Persons" table without using their exact names.

Please note that the "id_p" column links the above two tables.

Referencing two tables

We can get the data from two tables by referencing two tables:

Who ordered the products and what products they ordered?

Result set:

LastName FirstName OrderNo
Adams John 22456
Adams John 24562
Carter Thomas 77895
Carter Thomas 44678
SQL join-Using Join

In addition to the above method, we can also use keyword JOIN to get data from two tables.

If we want to list everyone's orders, you can use the following SELECT statement:

SELECT Persons.lastname, Persons.firstname, Orders.ordernofrom Persons on INNER JOIN Orders 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
Different SQL JOIN

In addition to the INNER join (inner join) we used in the example above, we can also use several other connections.

The following lists the JOIN types that you can use, and the differences between them.

    • Join (Join): Returns a row if there is at least one match in the table
    • Left join: Returns all rows from the left table even if there is no match in the right table
    • Right join: Returns all rows from the right table even if there is no match in the left table
    • Full join: Returns a row whenever there is a match in one of the tables
SQL INNER JOIN keyword

The INNER JOIN keyword returns a row when there is at least one match in the table.

INNER JOIN keyword Syntax
SELECT column_name (s) from Table_name1inner JOIN table_name2 on Table_name1.column_name=table_name2.column_name

Note: INNER join is the same as 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
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.ordernofrom personsinner JOIN Orderson 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.

SQL 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).

Left JOIN keyword syntax
SELECT column_name (s) from Table_name1left JOIN table_name2 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
Left JOIN connection (left JOIN) instance

Now we want to list all the people as well as their orders-if any.

You can use the following SELECT statement:

SELECT Persons.lastname, Persons.firstname, Orders.ordernofrom personsleft JOIN Orderson 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).

SQL Right JOIN keyword

The right JOIN keyword returns all rows from the table (table_name2), even if there are no matching rows in the left table (table_name1).

Right JOIN keyword syntax
SELECT column_name (s) from Table_name1right JOIN table_name2 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
Right Join instance

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.ordernofrom personsright JOIN Orderson 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).

SQL Full JOIN keyword

The full JOIN keyword returns a row whenever there is a match in one of the tables.

Full JOIN keyword syntax
SELECT column_name (s) from Table_name1full JOIN table_name2 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
Fully connected (full join) instances

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.ordernofrom personsfull JOIN Orderson 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.

Join for SQL uses

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.