Oracle Foundation Note Four

Fourth Multi-table query

1. Descartes Set

The Cartesian assembly is produced under the following conditions:

Omit join condition

Invalid connection condition

All rows in all tables are connected to each other

To avoid a Cartesian set, a valid join condition can be added to the where.

2.Oracle Connection

Use joins to query data in multiple tables.

Select Table1.column,table2.column

From Table1,table2

where Table1.column = Table2.column

Writes the join condition in the WHERE clause.

When you have the same column in a table, precede the column name with the table name prefix

2.1 Equivalent connections:

Multiple join conditions and and operators

Distinguish between duplicate column names, use table name prefixes to differentiate the same columns in multiple tables, and columns with the same column names in different tables can be distinguished by the alias of the table.

Alias of table:

Use aliases to simplify queries.

Using the table name prefix can improve execution efficiency.

SELECT e.employee_id, E.last_name, e.department_id,

D.DEPARTMENT_ID, d.location_id

From Employees e, departments D

WHERE e.department_id = d.department_id;

Connect multiple tables: Connect n tables with at least n-1 connection conditions. For example: Connect three tables and require at least two connection conditions.

2.2 Non-equivalent connections:

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;

3. Internal and external connections:

Inner joins: Merges rows with more than two tables in the same column, and the result set does not contain rows that do not match one table to another

Outer joins: Two tables returns rows in the left (or right) table that do not meet the criteria in addition to the rows that satisfy the join condition, which 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 an outer join is similar to an inner join, but the column of a table with no matching rows in the join condition is appended with the outer join operator, which is a plus sign (+) enclosed in parentheses.

Outer JOIN Syntax:

Use an outer join to query for data that does not meet the join criteria.

The symbol for the outer join IS (+).

SELECTTable1.column, Table2.column

Fromtable1, table2

WHEREtable1.column (+) = table2.column;//right outer join

SELECTTable1.column, Table2.column

Fromtable1, table2

WHEREtable1.column = table2.column (+);//LEFT Outer connection

Self-connect:

Title: Query Employees table, return "XXX works for XXX"

SELECT Worker.last_name | | ' Works for '

|| Manager.last_name

From employees worker, employees manager

WHERE worker.manager_id = manager.employee_id;

4.

Fork Set (understanding):

Use the Cross JOIN clause to cause the joined table to produce a fork set.

The fork set and the Cartesian set are the same.

SELECT last_name, Department_name

From Employees

Cross JOIN departments;

Natural connections:

The NATURAL join clause creates an equivalent join with a column of the same name in two tables.

Query the table for data that satisfies the equivalent condition.

If only the column names are the same and the data types are different, an error is generated.

SELECT department_id, Department_name,

location_id, City

From departments

NATURAL JOIN locations;

To create a connection using a using clause:

When you create an equivalent connection in the natural JOIN clause, you can use the Using clause to specify the columns that are needed in the equivalent join.

Use a using to select when more than one column satisfies a condition.

Do not add a table name prefix or alias to the selected column.

JOIN and using clauses are often used concurrently.

SELECT e.employee_id, E.last_name, d.location_id

From Employees e JOIN Departments D

USING (department_id);

To create a connection using the ON clause (common):

In a natural connection, a join condition is listed with the same name.

You can use the ON clause to specify additional join conditions.

This connection condition is separate from other conditions.

The ON clause makes the statement more readable.

SELECT e.employee_id, E.last_name, e.department_id,

D.DEPARTMENT_ID, d.location_id

From Employees e JOIN Departments D

On (e.department_id = d.department_id);

To create a multi-table connection using the ON clause:

SELECT employee_id, City, Department_name

From Employees E

JOIN Departments D

On d.department_id = e.department_id

JOIN Locations L

on d.location_id = l.location_id;

Add:

Multiple-table query methods for SQL statements:

For example: Follow department_id query Employees (employee table) and departments (Department table)

The information.

Mode one (Universal type): SELECT ... From ... WHERE

SELECT E.last_name,e.department_id,d.department_name

From Employees e,departments D

where e.department_id = d.department_id

Mode two: SELECT ... From ... NATURAL JOIN ...

Limitations: Automatically connects the same columns in two tables (there may be multiple: department_id and manager_id)

SELECT Last_name,department_id,department_name

From Employees

NATURAL JOIN Departments

Way Three: SELECT ... JOIN ... USING ...

Limitations: Better than mode two, but this method is not appropriate if multiple tables have different column names

SELECT Last_name,department_id,department_name

From Employees

JOIN Departments

USING (department_id)

Mode four: SELECT ... From ... JOIN ... On ...

Common way, more than one way, easier to achieve outer joins (left, right, full)

SELECT Last_name,e.department_id,department_name

From Employees E

JOIN Departments D

On e.department_id = d.department_id

This article from the "Ah Cheng Blog" blog, reproduced please contact the author!

Oracle Foundation Note Four