Differences in writing Oracle sql99 external connections

1: Cartesian Product
Returned result set:
Generally, in the order of the from table, all records are extracted from T1 and combined with the first record of T2.
Then, all records are extracted from T1 and combined with the second record of T2.
Then extract all records from T1 and combine the records in T2.
Sql99:
Select t1.department _ id, t2.employee _ id
From t_department T1 cross joint_employee T2;
Keyword: cross join

 

ORACLE:
Select t1.department _ id, t2.employee _ id
From t_department T1, t_employee T2;

2: jion (inner join)
2.1 equijoin: equivalent join
Sql99:
Select * From t_department;
Department_id location_id department_name
------------- Begin --------------------------------------------------------------------------------------------------------
D_1 L_1 D_n
D_2 L_2 d_n2
SQL> select * From t_employee;

Employee_id department_id employee_name
----------- Begin --------------------------------------------------------------------------------------------------------
E_3 d_4 e_n4
E_1 D_1 e_n1
E_2 D_3 e_n2
SQL> select t1.department _ id, t2.employee _ id
2 fromt_department T1
3 joint_employee T2 on t1.department _ id = t2.department _ id;

Department_id employee_id
------------------------
D_1 E_1

ORACLE:
Select t1.department _ id, t2.employee _ id
2 fromt_department T1, t_employee T2
3 where t1.department _ id = t2.department _ id;

Department_id employee_id
------------------------
D_1 E_1

2.2: non_equijoin: Non-equivalent join
It is also a Cartesian product to find out the conditions that meet the connection conditions.
Sql99:
Select t1.department _ id, t2.employee _ id
2 fromt_department T1
3 joint_employee T2 on t1.department _ id <> t2.department _ id;

Department_id employee_id
------------------------
D_1 E_3
D_2 E_3
D_2 E_1
D_1 E_2
D_2 E_2

ORACLE:
Select t1.department _ id, t2.employee _ id
2 fromt_department T1, t_employee T2
3 where t1.department _ id <> t2.department _ id;

Department_id employee_id
------------------------
D_1 E_3
D_2 E_3
D_2 E_1
D_1 E_2
D_2 E_2

2.3 Outer Join

2.3.1 left Outer Join
Add records to the employee table
Employee_id department_id employee_name
E_4 D_1 e_n8

Locate all employees in the department. If the Department does not have any employees, the employee information is null.
Sql99:
Select t1.department _ id, t2.employee _ id
2 fromt_department T1
3 left outerjoin t_employee T2 on t1.department _ id = t2.department _ id;

Department_id employee_id
------------------------
D_1 E_4
D_1 E_1
D_2 null (not displayed)

ORACLE: equal sign on the left of (+)
Select t1.department _ id, t2.employee _ id
2 fromt_department T1, t_employee T2
3 where t1.department _ id = t2.department _ ID (+ );

Department_id employee_id
------------------------
D_1 E_4
D_1 E_1
D_2 null (not displayed)

2.3.2 right Outer Join
Locate the department information of all employees. If the employee Department does not have information required to display the employee information, the Department is null.
Sql99:
Select t1.department _ id, t2.employee _ id
2 fromt_department T1
3 right Outer Join t_employee T2 ont1.department _ id = t2.department _ id;

Department_id employee_id
------------------------
D_1 E_1
D_1 E_4
E_2
E_3
ORACLE:
Select t1.department _ id, t2.employee _ id
2 fromt_department T1, t_employee T2
3 where t1.department _ ID (+) = t2.department _ id;

Department_id employee_id
------------------------
D_1 E_4
D_1 E_1
E_2
E_3

2.3.3 full external Association
Find information about all employees and employees of all departments. If the information does not exist, it is displayed as null.
SQL 99:
Select t1.department _ id, t2.employee _ id
2 fromt_department T1
3 full outerjoin t_employee T2 on t1.department _ id = t2.department _ id;

Department_id employee_id
------------------------
D_1 E_4
D_1 E_1
D_2
E_2
E_3

ORACLE:
Does not exist

3. Other cases
3.1. Join with the Same Name field
Automatic equivalent join for fields with the same name
Select department_id, t2.employee _ id -- fields with the same name cannot be written as "table name. Field name"
From t_department T1 natural
Join t_employee T2;
Department_id employee_id
------------------------
D_1 E_1

Add the "test" field to t_department and t_employee respectively, assign the value "2" to t_department, and assign "1" to t_employee"
Select department_id, test, t2.employee _ id
2 fromt_department T1 natural
3 joint_employee T2;

Department_id test employee_id
----------------------------
No result set

When more than 3.2 fields with the same name are specified, specify the Same Name field join
Select department_id, t1.test, t2.employee _ id
2 fromt_department T1
3 joint_employee T2
4 using (department_id );

Department_id test employee_id
----------------------------
D_1 2 E_4
D_1 2 E_1

Select t1.department _ id, test, t2.employee _ id
2 fromt_department T1
3 joint_employee T2
4 using (test );

Department_id test employee_id
----------------------------
No result set

4. seft join)
I have not come up with a good example.

5. Add filter conditions
Sql99
Select department_id, t2.employee _ id
2 fromt_department T1
3 joint_employee T2
4 using (department_id)
5 where t2.employee _ id> = 'e _ 0 ';

Department_id employee_id
------------------------
D_1 E_4
D_1 E_1

ORACLE:
Add the and condition to the end.

6. Multi-table join
Find the data with valid dependency relationships in the three tables.
Select t1.department _ id, t2.employee _ id, t3.location _ id
2 fromt_department T1
3 joint_employee T2 on t1.department _ id = t2.department _ id
4 joint_location T3 on t1.location _ id = t3.location _ id;

Department_id employee_id location_id
-----------------------------------
D_1 E_4 L_1
D_1 E_1 L_1