Sqljoinon join query statement usage

Sqljoin involves many types of queries, such as internal connection and external connection outer, which are roughly the same in usage, but the results are different. Let's take a look at these instances.

SQL join involves many types of queries, such as internal connections and external connections, which are roughly the same in usage, but the results are different. Let's take a look at these instances.

Join is used to combine rows in multiple tables. Join two or more tables listed in the FROM clause of an SQL statement.

There are different types of connections. Let's look at several examples.

Internal join (simple join)
The opportunity is that you have written an SQL statement using an internal join. This is the most common type. Multiple tables whose internal join conditions are met return all rows.

The Code is as follows:
SELECT suppliers. supplier_id, suppliers. supplier_name, orders. order_date FROM suppliers, orders WHERE suppliers. supplier_id = orders. supplier_id;

This SQL statement returns all rows from the supplier and order table that match the value of supplier_id in the vendor and order table.

Let's take a look at the internal connection of some data interpretation work:

We have two fields of supplier (supplier_id and supplier _ name) table.
It contains the following data:

Supplier_id	Supplier_name
10000	IBM
10001	Hewlett Packard
10002	Microsoft
10003	NVIDIA

We have another table calledOrdersWith three fields (order_id, supplier_id, and order_date ).
It contains the following data:

Order_id	Supplier_id	Order_date
500125	10000	2003/05/12
500126	10001	2003/05/13

Run the following code:

The Code is as follows:
SELECT suppliers. supplier_id, suppliers. supplier_name, orders. order_date FROM suppliers, orders WHERE suppliers. supplier_id = orders. supplier_id;

Result

Supplier_id	Name	Order_date
10000	IBM	2003/05/12
10001	Hewlett Packard	2003/05/13

Outer Join
First look at an instance

The Code is as follows:
Suppliers. supplier_id, suppliers. supplier_name, orders. order_date From suppliers, orders Where suppliers. supplier_id = orders. supplier_id (+ );

This SQL statement returns all rows from the supplier table, and only the joined fields of the rows from the order table are equal.

(+), After orders. supplier_id field indicates that if the value of the supplier_id field in the supplier table does not exist in the order table, each field in the order table will act The displayed result set.

The preceding SQL statement can also be written as follows:

The Code is as follows:
Select suppliers. supplier_id, suppliers. supplier_name, orders. order_date From suppliers, orders Where orders. supplier_id (+) = suppliers. supplier_id

View results

Supplier_id	Supplier_name
10000	IBM
10001	Hewlett Packard
10002	Microsoft
10003	NVIDIA

We have a second table calledOrdersWith three fields (order_id, supplier_id, and order_date ).
It contains the following data:

Order_id	Supplier_id	Order_date
500125	10000	2003/05/12
500126	10001	2003/05/13

Then call the query statement.

The Code is as follows:
Select suppliers. supplier_id, suppliers. supplier_name, orders. order_date From suppliers, orders Where suppliers. supplier_id = orders. supplier_id (+ );

Result:

Supplier_id	Supplier_name	Order_date
10000	IBM	2003/05/12
10001	Hewlett Packard	2003/05/13
10002	Microsoft
10003	NVIDIA