9, join and aggregation query

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

In the query process of database retrieval, sometimes in a table of data records often cannot meet the needs of developers or customers. For example, query student course grade information. The student information and course information on two different data table. Among them, in the course of information table (T_curriculum) is included in the course number, course name, course credits, classes and teachers, student information, student number, elective course number and course grades and other information on the performance of information table (T_result), then in order to display information about student course information and selected course in the results of a query, you need at the same time retrieval course information table (T_curriculum) and performance information table(T_result). This requires a query operation.

1 inner connection query

Equijoin
Equivalent connection refers to the specified connection conditions by using the operator (=) together, and returns the rows that match the join condition. The syntax is as follows:
The 1 field of the SELECT table, the table name field 2 ….
FROM   Table 1, table 2
The WHERE Table 1 field of the 1= Table 2 field 2
Among them, 1 fields of table SELECT statement and table 2 field represents the specified data in Table 1 and table 2 to query the column; 1 and table names in the FROM statement that specifies the table 2 connected data table names in the WHERE Clause 1; table name field of the 1= Table 2 field 2 representation is used to specify the connection conditions of the column. The columns in field 1 and field 2 column must be associated with each other between the two column of the table.
Non equijoin
Non equijoin refers to the use of addition operator (=) other operator outside the specified conditions will be connected to execute queries. Other operators including, > = (greater than or equal to, <); = (less than or equal to), > (more than), < (less than);,! = (not equal), you can also use the BETWEEN... The AND operator.

SELECT R.stuID,S.stuName,C.curID, C.curName,R.result FROM T_result R,T_curriculum C,T_student S WHERE R.curID=C.curID AND R.result>80
Use the ON clause to establish equal.
In the SQL statement using the WHERE clause in the addition operator (=) to achieve equivalent connected operation, also can use the ON clause to establish equal connection condition. The grammar rules are as follows:
The 1 field of the SELECT table, the table name field 2... Table name table. FROM 1 JOIN 2 ON 1 field of the 1= table name field 2

The JOIN keyword indicates the tables 1 and 2 together, ON clause to specify connection conditions of the column.
Use the USING clause to establish equal.
In the connection operation, sometimes only hope will be interconnected to two columns in the table to establish a equivalent connection. At this point, you can use the USING clause to establish equal connection to simplify the use of operator (=) equijoin operations established. The syntax is as follows:

The 1 field of the SELECT table, the table name field 2... Table name table. FROM 1 JOIN 2 USING (field 1)
The JOIN keyword indicates the tables 1 and 2 together, used in the USING clause will field 1 brackets enclose, equijoin interrelated establish field 1 is the two in the table.

The 2 cross connection

The results of cross connection returned a Descartes product. The so-called Cartesian product is actually two sets of the multiplication. N elements if the set A, B collection of M elements, if the result finally returned is n*m, then the result is the Cartesian product of the set A and set B.

SELECT R.stuID,C.curID FROM T_result R,T_curriculum C or FROM T_result R cross join T_curriculum C

3 self join query

As mentioned above, the connection is between the table and the table. Join queries in addition to between different table, also can be of the same table to realize the connection operation, the join query method called self connection. The so-called self connection, refers to a data table with itself. The grammar rules are as follows:
The SELECT A. field, A. field ….
FROM   Table 1 A, 1 B table name
WHERE A. field, =B. field
Because the connection is the same table, so in the FROM statement to define different alias table, this table 1 defines the table alias for A and B. The SELECT statement can use the A. field form can also use the B. field in the form of query needs to record. Use the SELECT statement here is the A. field in the form of.
For example, in the course of information tables to choose science scores than the operating system of fractional multi course information.

SELECT C2.curID,C2.curName,C2.credit FROM T_curriculum C1,T_curriculum C2 WHERE C1.curName = 'Operating system' AND C1.credit<C2.credit

The 4 outer join

In front of about connection operation, the returned result is satisfy the join condition records. In some cases, developers or users to not meet some records the join condition is also interested in, this time on the need to use the outer join. Outer join query can return not only satisfy the join condition records, for a data table in another table does not match the record can also return. Outer join includes three kinds: left outer join, join, full outer join.
Left outer join
Left outer join query results not only show that satisfy the join condition record, but also includes the query condition records do not satisfy the table on the left. In the Oracle database, you can use the operator (+) to represent the left outer join. When the plus operator (+) appear in the join condition on the left, is called the connection for the left outer. The syntax is as follows:
The 1 field of the SELECT table, the table name field 2 ….
FROM   Table 1, table 2
The WHERE table name field 1 (+ 1) = Table 2 field 2
Among them, 1 fields of table SELECT statement and table 2 field represents the specified data in Table 1 and table 2 to query the column; 1 and table names in the FROM statement that specifies the table 2 connected data table names in the WHERE clause; table 1 field 1 (+) = Table 2 field 2 said left outer join. At this time, will form 1 field 1 where the columns are searched out.
In the MySQL database and the Microsoft SQL Server database can use the LEFT[OUTER] JOIN keyword, the OUTER keyword is optional. The rules of grammar to realize connection using the LEFT[OUTER] JOIN keyword and left.:

The 1 field of the SELECT table, the table name field 2... Table 1. FROM LEFT JOIN 2 ON 1 1= 2 field and 2 field
Here the use of LEFT the JOIN keyword instead of a FROM statement in the SQL statement, the WHERE clause using the ON clause in the SQL statement instead of the standard, and the plus operator connected left outer said in the SQL statement (+) removal.
Right join
In the Oracle database, when the plus operator (+) appeared in the connection conditions of the right, is called a right outer join. The syntax is as follows:
The 1 field of the SELECT table, the table name field 2 ….
FROM   Table 1, table 2
The WHERE Table 1 field of the 1= Table 2 field 2(+)
In the MySQL and Microsoft SQL Server database can use the RIGHT [OUTER] JOIN keyword, the OUTER keyword is optional. The rules of grammar to realize connection using the RIGHT [OUTER] JOIN left key words are as follows:
The 1 field of the SELECT table, the table name field 2... Table 1. FROM RIGHT JOIN 2 ON 1 1= 2 field and 2 field
Full outer join
Full outer joins in the query results will not only display the connection conditions of the record does not satisfy the table on the left, but also display the query condition the record does not meet the right table. Full outer joins can be considered a left outer join and join the collection (not including the repeat).
Full outer joins can use FULL [OUTER] to realize the JOIN keyword, the OUTER keyword is optional. The rules of grammar to realize connection using the FULL [OUTER] JOIN left key words are as follows:
The 1 field of the SELECT table, the table name field 2... Table 1. FROM FULL JOIN 2 ON 1 1= 2 field and 2 field

The 5 set of query

In the connection SQL query statements, and a query mode is the combination of the query. Aggregation queries mainly includes three kinds: and operation, operation and operation. The operation and the difference operation is not applicable to the current mainstream all database.
And operation(UNION)
Implementation and operation of the key is UNION. And the operation returns a result set includes all the different lines of inquiry two queries, contains no duplicate rows. The syntax is as follows:
The SELECT statement 1
UNION
The SELECT statement 2
The statement said 1 and statement 2 is two for the SELECT query language. The UNION keyword query expressed on these two query results and operation. Here need to ensure that the number of columns of 1 SELECT statements and SELECT statements 2 query must be the same, but the corresponding column data type must be the same.
Intersection operation(INTERSECT)
Perform operations using the keyword is INTERSECT. Intersection operation returns a result set includes public query results. AC operation does not appear to duplicate rows. The syntax is as follows:
The SELECT statement 1
INTERSECT
The SELECT statement 2
The statement said 1 and statement 2 is two for the SELECT query language. The INTERSECT keyword query expressed on these two query results of operation. Here need to ensure that the number of columns of 1 SELECT statements and SELECT statements 2 query must be the same, but the corresponding column data type must be the same.
Difference operation(MINUS)
The execution of operations using the keyword is MINUS. Differential operator returns the result set of records are only found in the first SELECT statement in the presence, but not in the second SELECT statement in the query results. In the following format:
The SELECT statement 1
MINUS
The SELECT statement 2

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download

Posted by Editha at November 28, 2013 - 2:55 AM