Oracle inner join

Join multiple table creation record sets using inner join syntax

Author: 2 Source: Internet [] Abstract:

In this tutorial, We will link five tables. If you want to, you can join more tables in a similar way ~

Step 1: Use the access software to create a database named member. five tables are created in the database: member information data table Member, member identity table memberidentity, member permission table memberlevel, member category table membersort, and member marital status table wedlock.

● Member information table member:
Memberid: Automatic ID, primary key (ID)
Membersort: Number (member type)
Membername: Text, member name
Password: Text (member password)
Memberlevel: Number (member permission)
Memberidentity: Number (membership)
Wedlock: Number (marital status)
Memberqq: Text (QQ number)
Memberemail: Text (member email)
Memberdate: date/time (Member registration date)

● Member identity table memberidentity:
Memberidentity: automatic number, primary key (ID)
Identityname: Text (member name)

● Member permission table memberlevel:
Memberlevel: automatic number, primary key (ID)
Levelname: Text (member permission name)

● Member category table membersort:
Membersort: automatic number, primary key (ID)
Sortname: Text (member category name)

● Wedlock
Wedlock: automatic number, primary key (ID)
Wedlockname: Text (member marital status category)
Note: after creating the five tables, you can set the desired category. For example, you can set two categories: "unpaid members" and "paid members ", the numbers are "1" and "2" respectively. If you set three options, the number of the third option is "3.
What we want to do below is to display numbers such as "1" and "2" as "unpaid members" and "paid members". Otherwise, who knows that "1" represents "unpaid members" and "2" represents "paid members "?

Step 2: Create a DSN data source and a record set
● Run the Dreamweaver MX software and create a DSN data source named connmember (you can also start other names) on the member registration information display page.

● Click "bind" on the server behavior panel to create a data set named membershow, select connmember for "connection", select Member for "table", select all columns, and select memberdate for "sorting, in descending order. Click "advanced" to modify the automatically generated code in the SQL box:
The original code is:
Select *
From Member
Order by memberdate DESC

Modify the code:
Select *
From (member inner join membersort on member. membersort = membersort. membersort) Inner join memberlevel on member. memberlevel = memberlevel. memberlevel) Inner join memberidentity on member. memberidentity = memberidentity. memberidentity) Inner join wedlock on member. wedlock = wedlock. wedlock
Order by memberdate DESC
After modifying the code, click "OK!
Now, you can open the record set and check that all the fields in the five tables are integrated in the membershow record set. You only need to bind the fields to the cells to be displayed. Now, all the numbers are changed to the corresponding names. For example, the membership permissions are no longer in the "1" and "2" numeric forms, it is changed to the corresponding name "unpaid member" and "paid member ". Other numbers are also displayed as text names. Are you happy?

Note:
● During the process of inputting letters, you must use the English half-width punctuation marks and leave a half-width space between words;
● When creating a data table, if a table is connected to multiple tables, the fields in this table must be of the "Number" data type, and the same fields in multiple tables must be primary keys, the data type is "auto-numbered. Otherwise, it is difficult to connect successfully.
● Code nesting quick method: for example, if you want to connect five tables, you only need to add a bracket to the Code connecting the four tables (the Front Bracket is added after the from clause, add the parentheses at the end of the Code), and then add "inner join table name X on table 1. field number = Table X. field number "code, so that you can join the data table without limit :)

Syntax format:
In fact, inner join ...... The syntax format of on can be summarized:
From (Table 1 inner join table 2 on table 1. field number = table 2. field number) Inner join table 3 on table 1. field number = table 3. field number) Inner join table 4 on member. field number = table 4. field number) Inner join Table X on member. field number = Table X. field number
You only need to apply this format.

Example of ready-to-use format:
Although I have already understood it, to take care of beginners, I still use the Membership registration system as an example to provide some examples of existing syntax formats, you only need to modify the data table name and field name.

How to connect two data tables:
From Member inner join membersort on member. membersort = membersort. membersort
The syntax format can be summarized as follows:
From table 1 inner join table 2 on table 1. Field number = TABLE 2. Field number

How to connect three data tables:
From (member inner join membersort on member. membersort = membersort. membersort) Inner join memberlevel on member. memberlevel = memberlevel. memberlevel
The syntax format can be summarized as follows:
From (Table 1 inner join table 2 on table 1. Field number = TABLE 2. Field number) Inner join table 3 on table 1. Field number = TABLE 3. Field number

Usage of connecting four data tables:
From (member inner join membersort on member. membersort = outer. Outer) Inner join memberlevel on member. memberlevel = memberlevel. memberlevel) Inner join memberidentity on member. memberidentity = memberidentity. memberidentity
The syntax format can be summarized as follows:
From (Table 1 inner join table 2 on table 1. field number = table 2. field number) Inner join table 3 on table 1. field number = table 3. field number) Inner join table 4 on member. field number = table 4. field number

Usage of Connecting Five data tables:
From (member inner join membersort on member. membersort = membersort. membersort) Inner join memberlevel on member. memberlevel = memberlevel. memberlevel) Inner join memberidentity on member. memberidentity = memberidentity. memberidentity) Inner join wedlock on member. wedlock = wedlock. wedlock
The syntax format can be summarized as follows:
From (Table 1 inner join table 2 on table 1. field number = table 2. field number) Inner join table 3 on table 1. field number = table 3. field number) Inner join table 4 on member. field number = table 4. field number) Inner join table 5 on member. field number = table 5. field number

Usage of connecting six Data Tables: omitted, similar to the preceding join method, let's take a look at the opposite: [reprint] Typical application of public table expressions ========================== ========================================== [reprint] Typical public table expressions application

Author: ningoo (http://ningoo.itpub.net)
Posted on: 2006.11.18
Category: Oracle
Source: http://ningoo.itpub.net/post/2149/229942
---------------------------------------------------------------

Reproduced from Asa SQL user guide

Common table expression (CTE): common table expression

You can use the with prefix of a SELECT statement to define a common table expression. You can use these public table expressions in queries like using temporary views.

========================================================== ======================================

Ningoo:Although this article is based on Sybase, the usage and syntax of common table expressions in Oracle are basically the same, but the common table expressions in Oracle do not support column aliases, that is, the following syntax is valid in Sybase and an error is reported in Oracle:

With test_table (col1, col2)
As (select col1, col2 from test)
Select * From test_table;

ORA-32033: column alias not supported

Therefore, in Oracle, the syntax is as follows:

With test_table
As (select col1, col2 from test)
Select * from test;

Ms SQL server2005 also supports CTE: http://msdn2.microsoft.com/zh-cn/library/ms190766.aspx

========================================================== ======================================

The common table expression is defined by the with clause. This clause is prior to the select keyword in the SELECT statement. The content of a clause defines one or more temporary views that may be referenced elsewhere in the statement. The syntax of this clause simulates the syntax of the create view statement. You can use a public table expression to express the previous query, as shown below.

WITH CountEmployees(dept_id, n) AS  ( SELECT dept_id, count(*) AS n    FROM employee GROUP BY dept_id )SELECT dept_id, nFROM CountEmployeesWHERE n = ( SELECT max(n)            FROM CountEmployees )

To search for departments with the minimum number of employees, this query can return multiple rows.

WITH CountEmployees(dept_id, n) AS  ( SELECT dept_id, count(*) AS n    FROM employee GROUP BY dept_id )SELECT dept_id, nFROM CountEmployeesWHERE n = ( SELECT min(n)            FROM CountEmployees )

In the example database, the two departments share a minimum of nine employees.

In general, as long as the table expression must appear multiple times in a query, the common table expression is very useful. The following typical cases are applicable to common table expressions.

• Queries involving multiple set functions.

• The process must contain a view that references program variables.

• Use the temporary view to store queries for a group of values.

This list is incomplete. You may encounter many other situations where you want to use a public table expression.

Multiple set functions

A public table expression is useful as long as multiple levels of sets must be displayed in a query. This is the case in the previous example. The task is to retrieve the Department ID of the Department with the most employees. Therefore, you need to use the Count collection function to calculate the number of employees in each department, and use the max function to select the largest department.

When you write a query to determine which department has the highest salary, a similar situation occurs. The sum aggregate function is used to calculate the salary of each department, and the max function is used to determine which department is the largest. The two functions appear simultaneously in the query, indicating that the common table expression may be useful.

WITH DeptPayroll( dept_id, amt ) AS    ( SELECT dept_id, sum(salary) AS amt      FROM employee GROUP BY dept_id )SELECT dept_id, amtFROM DeptPayrollWHERE amt = ( SELECT max(amt)              FROM DeptPayroll )

Reference Program variable View

Sometimes, it is very convenient for a public table expression to create a view that contains references to program variables. For example, you can define variables that identify specific customers during the process. You want to query the purchase history of this customer. If you want to access similar information multiple times or use multiple collection functions, you need to create a view that contains information about this specific customer.

You cannot create a view that references program variables because the view scope cannot be limited to the scope of your process. Once a view is created, it can be used in other environments. However, you can use a public table expression in your queries. Because the scope of common table expressions is limited to statements, variable reference does not cause any ambiguity. Therefore, you can use variable reference.

The following statement selects the total sales of different sales representatives in the example database.

SELECT emp_fname || ' ' || emp_lname AS sales_rep_name,       sales_rep AS sales_rep_id,       sum( p.unit_price * i.quantity ) AS total_salesFROM employee LEFT OUTER JOIN sales_order AS o              INNER JOIN sales_order_items AS i              INNER JOIN product AS pWHERE '2000-01-01' <= order_date                  AND order_date < '2001-01-01'GROUP BY sales_rep, emp_fname, emp_lname

The above query is the basis of the common table expression that appears in the following process. The ID of the sales representative and the year discussed are the parameters to be used. As shown in this process, process parameters and any declared local variables can be referenced in the with clause.

CREATE PROCEDURE sales_rep_total (  IN rep  INTEGER,  IN yyyy INTEGER )BEGIN  DECLARE start_date DATE;  DECLARE end_date   DATE;  SET start_date = YMD( yyyy,  1,  1 );  SET   end_date = YMD( yyyy, 12, 31 );  WITH total_sales_by_rep ( sales_rep_name,                            sales_rep_id,                            month,                            order_year,                            total_sales ) AS  ( SELECT emp_fname || ' ' || emp_lname AS sales_rep_name,           sales_rep AS sales_rep_id, month( order_date),           year(order_date),           sum( p.unit_price * i.quantity ) AS total_sales    FROM employee LEFT OUTER JOIN sales_order o                       INNER JOIN sales_order_items i                       INNER JOIN product p    WHERE start_date <= order_date AND                        order_date <= end_date AND          sales_rep = rep    GROUP BY year(order_date), month(order_date),             emp_fname, emp_lname, sales_rep )  SELECT sales_rep_name,            monthname( YMD(yyyy, month, 1) ) AS month_name,            order_year,            total_sales  FROM total_sales_by_rep  WHERE total_sales =    ( SELECT max( total_sales) FROM total_sales_by_rep )  ORDER BY order_year ASC, month ASC;END;

The following statement describes how to call the above process.

CALL sales_rep_total(129, 2000);

View of stored values

Sometimes, a common table expression can be used to store a specific set of values in a select statement or process. For example, assume that a company needs to analyze the results of its sales staff for 1/3 rather than quarterly. Since it does not represent the built-in date portion of 1/3 (although it represents the built-in date portion of the quarter), it is necessary to store these dates in the process.

WITH thirds (q_name, q_start, q_end) AS( SELECT 'T1', '2000-01-01', '2000-04-30' UNION  SELECT 'T2', '2000-05-01', '2000-08-31' UNION  SELECT 'T3', '2000-09-01', '2000-12-31' )SELECT q_name,       sales_rep,       count(*) AS num_orders,       sum( p.unit_price * i.quantity ) AS total_salesFROM thirds LEFT OUTER JOIN sales_order AS o    ON q_start <= order_date AND order_date <= q_end                   INNER JOIN sales_order_items AS i                   INNER JOIN product AS p GROUP BY q_name, sales_rep ORDER BY q_name, sales_rep

Be careful when using this method, because the value may need to be regularly maintained. For example, to use the preceding statement for any other year, you must modify it.

You can also apply this technology in the process. The following example declares a process in which the year discussed is used as the parameter.

CREATE PROCEDURE sales_by_third ( IN y INTEGER )BEGIN  WITH thirds (q_name, q_start, q_end) AS  ( SELECT 'T1', YMD( y, 01, 01), YMD( y, 04, 30) UNION    SELECT 'T2', YMD( y, 05, 01), YMD( y, 08, 31) UNION    SELECT 'T3', YMD( y, 09, 01), YMD( y, 12, 31) )  SELECT q_name,         sales_rep,         count(*) AS num_orders,         sum(p.unit_price * i.quantity) AS total_sales  FROM thirds JOIN sales_order AS o    ON q_start <= order_date AND order_date <= q_end            KEY JOIN sales_order_items AS i            KEY JOIN product AS p  GROUP BY q_name, sales_rep  ORDER BY q_name, sales_rep;END;

CALL sales_by_third (2000);

(Need to reference, please indicate the source: http://ningoo.itpub.net)

 

Oracle 9i table connection

【]

0

Address: http://blog.csdn.net/xuanxingmin/archive/2006/10/31/1358159.aspxTag:Oracle 9i, table connection, connection ,...Author:Xuan xingminSource:Csdn.netPosted 29 days ago Normal equal join (internal join): select. *, B. * From a, B where. id = B. ID; or select * from a, B where. id = B. ID; the above two are equivalent. Generally, equal connections can be written as follows: Select * from a inner join B on. id = B. id outer join: "(+)" can be used in Oracle, 9i can use left/right/full outer join left Outer Join: left Outer Join select e. last_name, E. department_id, D. department_name from employees e left Outer Join orders ments D on (E. department_id = D. department_id); equivalent to select e. last_name, E. department_id, D. department_name from employees e, orders ments d Where Number of clicks: 6

 

It takes up to 30 minutes for the following SQL statements to run on the server:
Select DBO. comflow. comflowcode, DBO. comflow. comcode, DBO. comflow. custcode, DBO. comflow. response code, DBO. comflow. salecode,
DBO. comflow. empcode, DBO. comflow. Quantity * DBO. Commodity. convertrate as quantity, convert (datetime, datename (yyyy,
DBO. comflow. flowdate) + '-' + datename (mm, DBO. comflow. flowdate) + '-' + datename (DD, DBO. comflow. flowdate) as flowdate,
DBO. comflow. saltype, DBO. employee code as departin, DBO. Sale. employee code as departout,
DBO. comflow. Quantity * DBO. Commodity. tradeprice * DBO. Commodity. Discount/100 as total, DBO. Department,
Department1.kgcode as maid
From DBO. comflow inner join
DBO. Customer on
DBO. comflow. saltype in (N 'adprecat', N 'flow to return', N 'multi-level stream') and DBO. comflow. custcode = DBO. Customer. custcode
Or comflow_1.saltype in (N 'natural Stream', N 'natural streaming return') and comflow_1.outcustcode = customer_1.custcode
Inner join
DBO. customerrelation on DBO. comflow. comcode = DBO. customerrelation. comcode and
DBO. customerrelation. custcode = DBO. Customer. custcode inner join
DBO. employee on DBO. customerrelation. empcode = DBO. employee. empcode inner join
DBO. Sale on DBO. comflow. salecode = DBO. Sale. salecode inner join
DBO. Department on DBO. Department. employee code = DBO. employee code inner join
DBO. Department as department1 on department1.mongocode = DBO. Sale. mongocode and
DBO. Department. kgcode <> department1.kgcode inner join
DBO. Commodity on DBO. comflow. comcode = DBO. Commodity. comcode
Where (not (DBO. comflow. saltype = n' ') or
(Not (DBO. Customer. type = n' pharmaceutical company '))
Although the application we use this statement is a bi application with low real-time requirements, I think there is no reason that it will run for so long and there should be a way to optimize it.

Step 1: I looked at the index and it seems there is no problem.
Step 2: Check the link. Is there any error? No error. It is consistent with the application requirements, in particular, the calculated results are consistent with those calculated by colleagues using another method (colleagues use multiple views to accumulate step by step ).
Step 3: Check whether this statement is special?
I noticed that, in particular, the section marked with the pink background color:
DBO. comflow. saltype in (N 'adprecat', N 'flow to return', N 'multi-level stream') and DBO. comflow. custcode = DBO. Customer. custcode
Or comflow_1.saltype in (N 'natural Stream', N 'natural streaming return') and comflow_1.outcustcode = customer_1.custcode

Is this an or link? Is that the problem?
By analyzing this statement, we can see that this or statement can be broken down into a Union statement, so it becomes the following:

Select DBO. comflow. comflowcode, DBO. comflow. comcode, DBO. comflow. custcode, DBO. comflow. response code, DBO. comflow. salecode,
DBO. comflow. empcode, DBO. comflow. Quantity * DBO. Commodity. convertrate as quantity, convert (datetime, datename (yyyy,
DBO. comflow. flowdate) + '-' + datename (mm, DBO. comflow. flowdate) + '-' + datename (DD, DBO. comflow. flowdate) as flowdate,
DBO. comflow. saltype, DBO. employee code as departin, DBO. Sale. employee code as departout,
DBO. comflow. Quantity * DBO. Commodity. tradeprice * DBO. Commodity. Discount/100 as total, DBO. Department,
Department1.kgcode as maid
From DBO. comflow inner join
DBO. customer on DBO. comflow. saltype in (n' sales promotion ', n' flow to return', n' multi-level flow direction ') and DBO. comflow. custcode = DBO. customer. custcode inner join
DBO. customerrelation on DBO. comflow. comcode = DBO. customerrelation. comcode and
DBO. customerrelation. custcode = DBO. Customer. custcode inner join
DBO. employee on DBO. customerrelation. empcode = DBO. employee. empcode inner join
DBO. Sale on DBO. comflow. salecode = DBO. Sale. salecode inner join
DBO. Department on DBO. Department. employee code = DBO. employee code inner join
DBO. Department as department1 on department1.mongocode = DBO. Sale. mongocode and
DBO. Department. kgcode <> department1.kgcode inner join
DBO. Commodity on DBO. comflow. comcode = DBO. Commodity. comcode
Where (not (DBO. comflow. saltype = n' ') or
(Not (DBO. Customer. type = n' pharmaceutical company '))
Union all
Select comflow_1.comflowcode, comflow_1.comcode, comflow_1.custcode, comflow_1.20.code, comflow_1.salecode, comflow_1.empcode,
Comflow_1.quantity * commodity_1.convertrate as quantity, convert (datetime, datename (yyyy, comflow_1.flowdate) + '-' + datename (mm,
Comflow_1.flowdate) + '-' + datename (DD, comflow_1.flowdate) as flowdate, comflow_1.saltype, employee_1.20.code as departin,
Sale_1.shortcode as departout, comflow_1.quantity * commodity_1.tradeprice * commodity_1.discount/100 as total,
View
From DBO. comflow as comflow_1 inner join
DBO. Customer as customer_1 on comflow_1.saltype in (N 'natural Stream', N 'natural streaming return') and
Comflow_1.outcustcode = customer_1.custcode inner join
DBO. customerrelation as customerrelation_1 on comflow_1.comcode = customerrelation_1.comcode and
Customerrelation_1.custcode = customer_1.custcode inner join
DBO. employee as employee_1 on customerrelation_1.empcode = employee_1.empcode inner join
DBO. sale as sale_1 on comflow_1.salecode = sale_1.salecode inner join
DBO. Department as department_1 on department_1.20.code = employee_1.20.code inner join
DBO. Department as department1 on department1.20.code = sale_1.20.code and department_1.kgcode <> department1.kgcode inner join
DBO. Commodity as commodity_1 on comflow_1.comcode = commodity_1.comcode
Where (not (comflow_1.saltype = n' flow return ') or
(Not (customer_1.type = n' pharmaceutical company '))
I did not expect that the effect was too obvious. The statement that took 30 minutes to complete was completed in just 30 seconds.

Here we can see that the or statement may damage the index function. Although the logic of using or for association is clear, it is inefficient.
Although the use of Union is lengthy, it is more efficient to use it here.
Http://www.cnblogs.com/cleo/archive/2006/11/01/547079.html