ORACLE section 4th Multi-table Query

Learning Goals:

? usingequivalentAndnot equivalentThe connection queries the data in multiple tables in the SELECT statement. Useself-connect。 UseExternal ConnectionQuerying for data that does not meet the join criteria

Oracle Connectivity

Equivalent connections:

Use joins to query data in multiple tables.

SELECT Table1.column, Table2.column

from table1, table2

WHERE Table1.column1 =  Table2.column2;

?in theWHEREclause to write the join condition. ?when you have the same column in a table, precede the column name with the table name prefix
Two tables of connections

Select E.employee_id,e.last_name,d.department_idfrom Employees e,departments dwhere e.department_id = d.department_id

Connection of three tables

Select E.employee_id,e.last_name,d.department_id,l.cityfrom Employees e,departments d,locations Lwhere E.department_ id = d.department_id andd.location_id = l.location_id

?ConnectionNa table,at leastneed ton-1a connection condition。 For example: Connect three tables and require at least two connection conditions.
Non-equivalent connections:

Select E.last_name,e.salary,j.grade_levelfrom Employees e,job_grades jwhere e.salary between J.lowest_sal and J.highest _sal

internal and external connections:

Inner joins: Merges rows of more than two tables with the same column,rows in the result set that do not contain a table that does not match another tableOuter joins: Two tables in addition to returning rows that meet the conditions of the join during a connectionalso returns rows in the left (or right) table that do not meet the criteria, this connection is called a left (or right) outer join。

When there are no matching rows, the corresponding column in the result table is empty (null). The WHERE clause condition of the outer join is similar to the inner join, but the column of the table with no matching row in the join condition is appended with the outer join operator . That is, the plus sign (+)enclosed in parentheses.

--Left outer connection

Select E.employee_id,e.last_name,d.department_namefrom Employees e,departments dwhere e.department_id = D.department_ ID (+)

--Right outer connection

Select E.employee_id,e.last_name,d.department_namefrom Employees e,departments dwhere e.department_id (+) = d.department_id

Connect using sql:1999 Syntax

To query data from multiple tables using a connection:

SELECT Table1.column, Table2.column

from table1

[CrossJOINtable2] |

[NATURAL JOINtable2] |

[ JOIN table2  USING ( column_name )] |

[ JOIN table2  on ( table1.column_name = Table2.column_name )] |

[Left | Right | Full OUTER JOIN table2 On (table1.column_name =table2.column_name)];

Natural connections: ? Naturaljoin clause,An equivalent connection is created with a column of the same name in two tablesIn the table, query for data that satisfies the equivalent condition. If only the column names are the same,Different data types, an error is generated.

Select E.employee_id,e.last_name,d.department_namefrom Employees E natural Join departments D

Use USING clause to create a connection:

? When you create an equivalent connection in the natural JOIN clause, you canUseUSINGclause to specify the columns to use in the equivalent join.? Use a using to select when more than one column satisfies a condition.

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

Select E.employee_id,e.last_name,d.department_namefrom Employees E join Departments dusing (DEPARTMENT_ID)

This approach has limitations: if the column names in the two tables (one called department_id, another called ID) are different, this method fails.

Use on clause to create a connection (Common):

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

<pre name= "code" class= "SQL" >select E.employee_id,e.last_name,d.department_namefrom employees E Join Departments Don e.department_id = d.department_id

This is very similar to the equivalent connection.

Select E.employee_id,e.last_name,d.department_name,l.cityfrom Employees e <strong>join Departments d</ Strong>on e.department_id = d.department_id<strong>join locations L</strong>on d.location_id = l.location_id

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

ORACLE section 4th Multi-table Query