Oracle table connection-No data is displayed in the other table during the connection process. oracle-related data

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!