A data table is basically difficult to meet our query requirements, at the same time, all the data are saved in a table is obviously not a good database design, in order to avoid data redundancy, delete, update the exception, we usually need to create a foreign key table, through the table connection, to obtain the data we want to get, So in a data lookup, table joins are a frequently used operation, let's take a look at two or several tables in what ways they can be connected.
frequently encountered problems : We may be in the process of table connection to join the other table data is empty, resulting in some data is not available. How are we going to solve it?????
We start with the introduction of table connection, in the process of introduction, will be resolved.
If we have the following two data sheets :
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.
Method 1: Based on the relationship between the columns in two or more 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 |
This method looks very intuitive, also we often more commonly used, but there is no way to solve the above problem, but we can use the following join method to solve.
Way 2:join
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: Returns a row if there is at least one match 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
1), 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.
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.
2), 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). (Can solve the above problem)
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.
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) .
3), 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.
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) .
4), 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.
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 .
Oracle Table Connection--another table in the process of connection does not have the relevant data does not display the problem