ORACLE section 4th

ORACLE section 4th

Objective:

 

? Use the equi and non-equi join to query data in multiple tables in the SELECT statement. ? Use self-connection. Use external connection to query data that does not meet the connection conditions

 

Oracle connection

Equivalent connection:

Use a connection to query data in multiple tables.

 

SELECTTable1.column, table2.column

FROMTable1, table2

WHERETable1.column1=Table2.column2;

? Write the connection conditions in the WHERE clause. ? When the table has the same column, add the table name prefix before the column name.
Select e. employee_id, e. last_name, d. department_id
From employees e, departments d
Where e. department_id = d. department_id

Join of three tables
Select e. employee_id, e. last_name, d. department_id, l. city
From employees e, departments d, locations l
Where e. department_id = d. department_id and
D. location_id = l. location_id

 

? To connect n tables, at least n-1 join conditions are required. For example, connecting three tables requires at least two join conditions.
Auto join: select emp. last_name, manager. last_name
From employees emp, employees manager
Where emp. manager_id = manager. employee_id and emp. last_name = 'chen'

Non-equivalent join:

Select e. last_name, e. salary, j. grade_level
From employees e, job_grades j
Where e. salary between j. lowest_sal and j. highest_sal

 

Internal Connection and external connection:

 

Inner join: Merge rows of more than two tables with the same column. The result set does not contain the Outer Join of one table that does not match the other table: in addition to returning rows that meet the connection conditions, the two tables also return rows that do not meet the conditions in the left (or right) table. This connection is called a left (or right) Outer Join.

 

 

If no matching row exists, the corresponding column in The result table is NULL ). the WHERE clause condition for outer join is similar to an internal join, but the column of the table that does not match the row in the join condition must be followed by an outer join operator, that is, the plus sign (+) enclosed in parentheses ).

-- Left Outer Join
Select e. employee_id, e. last_name, d. department_name
From employees e, departments d
Where e. department_id = d. department_id (+)

-- Right Outer Join
Select e. employee_id, e. last_name, d. department_name
From employees e, departments d
Where e. department_id (+) = d. department_id

Connect using SQL: 1999 Syntax:
Use a connection to query data from multiple tables:
SELECT table1.column, table2.column
FROM table1
[CROSS JOINtable2] |
[NATURAL JOINtable2] |
[JOINtable2 USING (column_name)] |
[JOINtable2 ON (table1.column _ name = table2.column _ name)] |
[LEFT | RIGHT | full outer JOINtable2 ON (table1.column _ name = table2.column _ name)];
Natural connection:
? The NATURALJOIN clause creates an equijoin based on the conditions of the columns with the same name in the two tables.
? Query the data that meets the equivalence conditions in the table.
If the column names are the same and the data types are different, an error is returned.

Select e. employee_id, e. last_name, d. department_name
From employees e natural join orders ments d

UseUSINGClause to create a connection:

 

? When the natural join clause creates an equijoin, you can use the USING clause to specify the columns to be used in the equijoin. ? You can use USING to select when multiple columns meet the conditions.

JOIN and USING clauses are often used at the same time.

 

Select e. employee_id, e. last_name, d. department_name
From employees e join orders ments d
Using (department_id)

This method has limitations: If the column names in the two tables (one is department_id and the other is id) are different, this method will become invalid.

UseONClause to create a connection(Commonly used ):

 

? In a natural connection, a join condition is a column with the same name. ? You can use the ON clause to specify additional connection conditions. ? This connection condition is separate from other conditions. The ON clause makes the statement more readable.

 

Select e. employee_id, e. last_name, d. department_name
From employees e
Join orders ments d
On e. department_id = d. department_id

This is very similar to the equijoin.

Left Outer Join

Select e. employee_id, e. last_name, d. department_name
From employees e
Left outer join orders ments d
On e. department_id = d. department_id

Outer right connection

 

Select e. employee_id, e. last_name, d. department_name
From employees e
Right outer join orders ments d
On e. department_id = d. department_id

Full outer connection

Select e. employee_id, e. last_name, d. department_name
From employees e
Full outer join orders ments d
On e. department_id = d. department_id