After graduating for more than half a year, have not learned to join before the joins have been to take the data from a table and then take the data from the other table, and then write a function loop format data.
Let's write about what you learned first.
Reprinted from W3school
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:
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
|
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?
SELECT Persons.lastname, Persons.firstname, Orders.orderno from Persons, Orders WHERE =
Result set:
LastName |
FirstName |
OrderNo |
Adams |
John |
22456 |
Adams |
John |
24562 |
Carter |
Thomas |
77895 |
Carter |
Thomas |
44678 |
SELECT column_name (s) from table_name1 Left JOIN on table_name1.column_name=table_name2.column_name
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 |
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
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 |
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.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)
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_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 |
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.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).