Oracle table connection-No data is displayed in the other table during the connection process. oracle-related data
A data table is basically difficult to meet our query requirements. At the same time, it is not a good database design to save all the data in a table. To avoid data redundancy, an error occurred while deleting or updating data. We usually need to create a foreign key table and use table join to obtain the data we want. Therefore, in data search, table join is a frequently used operation. Let's take a look at how two or several tables can be connected.
Frequently encountered problems: The data in another table that is used for connection during table join is null, leading to the failure of some data. How can we solve this problem ?????
We will first begin with introducing the table connection method, and will be resolved during the introduction process.
Suppose we have the following two data tables::
See the "Persons" table:
Id_P |
LastName |
FirstName |
Address |
City |
1 |
Adams |
John |
Oxford Street |
London |
2 |
Bush |
George |
Th Avenue |
New York |
3 |
Carter |
Thomas |
Changan Street |
Beijing |
Note that the "Id_P" column is the primary key of the Persons table. This means that no two rows can have the same Id_P. Even if two people have the same name, Id_P can differentiate them.
Next, see 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 of the Orders table. At the same time, the "Id_P" column in the "Orders" table is used to reference people in the "Persons" table, instead of using their exact name.
Note that the "Id_P" column associates the two tables above.
Method 1: Based on the relationship between columns in two or more tables
We can obtain data from two tables by referencing two tables:
Who ordered the product and what product 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 |
This method looks intuitive and is often used, but it cannot solve the problem above, but we can use the join method below.
Method 2: join
Different SQL JOIN
In addition to the inner join (inner join) used in the preceding example, we can also use several other connections.
The JOIN types that you can use and their differences are listed below.
- JOIN: If the table has at least one match, the row is returned.
- Left join: returns all rows from the LEFT table even if no match exists in the right table.
- Right join: returns all rows from the RIGHT table even if no match exists in the left table.
- Full join: if one of the tables matches, the row is returned.
1) SQL INNER JOIN keyword
If at least one match exists in the table, the inner join keyword returns the row.
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 instance
Now, we want to list all 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 |
Inner join keywords in the tableAt least one matching row exists. If the rows in "Persons" do not match in "Orders", these rows are not listed.
2) SQL LEFT JOIN keyword
The left join keyword returns all rows from the LEFT table (table_name1,Even if no matched rows exist in the right table (table_name2. (The above problem can be solved)
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, left join is called left outer join.
Left join instance
Now we want to list all people and 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 is from the LEFT table (Persons ).Returns all rows.,Even if there are no matched rows in the right table (Orders).
3) SQL RIGHT JOIN keyword
The right join keyword returns all rows from the RIGHT table (table_name2), even if no matching row exists 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, right join is called right outer join.
Right join instance
Now, we want to list all the orders and the persons 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 is returned from the RIGHT table (Orders)All rows are returned.,Even if no matching row exists in the left table (Persons).
4) SQL FULL JOIN keyword
If a table matches, the full join keyword returns the row.
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.
Full join instance
Now, we want to list all people, their orders, all orders, and the people who order 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 right table (Orders.If the rows in "Persons" do not match in the table "Orders", or if the rows in "Orders" do not match in the table "Persons", these rows are also listed.
Thank you: Thank you for your patience!