SQL Advanced App (join, Inner join, left JOIN, right join, full join)

SQL JOIN

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

Sometimes in order to get the complete result, we need to get the result 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 goal is to cross-bind 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 in the Orders table links the two tables above.

Referencing two tables

You can get data from two tables by referencing two tables: Who ordered the products and what products they ordered?

SELECT Persons.lastname, Persons.firstname, Orders.ordernofrom Persons, Orderswhere persons.id_p=orders.id_p

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 methods above, you can use keyword join to get data from two tables

If you want to list everyone's subscriptions, 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

Different SQL JOIN

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

• JOIN: Returns a row if at least one match is in the table
• 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

SQL INNER JOIN keyword

When there is at least one match in the table, the INNER JOIN keyword returns a row, syntax

SELECT column_name (s) from Table_name1inner JOIN table_name2on table_name1.column_name=table_name2.column_name

Note: INNER join and join are the same

Also take the above persons and Orders table as an example, now, list everyone's subscription

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), with no matching rows in the right table (table_name2), and its syntax is

SELECT column_name (s) from Table_name1left JOIN table_name2on table_name1.column_name=table_name2.column_name

　　Note: In some databases, the left join is called the left OUTER join

Use the left JOIN to list all the people, and their order (if any), using the persons and Orders tables above.

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_name2, even if there are no matching rows in the left table (table_name1), whose syntax is as follows

SELECT column_name (s) from Table_name1right JOIN table_name2on table_name1.column_name=table_name2.column_name

Note: In some databases, the right join is called the OUTER join

Or take the above data sheet for example, list all the orders, and the person who ordered them-if any

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

As long as one of the tables has a match, the full JOIN keyword returns a row with the following syntax

SELECT column_name (s) from Table_name1full JOIN table_name2on table_name1.column_name=table_name2.column_name

Note: In some databases, full join is called full OUTER join

In the case of the Persons and Orders table above, now list all the people, their orders, and all the orders, and the people who ordered them.

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.

SQL Advanced App (join, Inner join, left JOIN, right join, full join)