Connection query and set Query

Source: Internet
Author: User

Connection query and set query in the database query process, sometimes the data records in a table cannot meet the needs of developers or customers. For example, query the student's Course Selection score. The student course selection information and course score information are in two different data tables. The course information table (T_curriculum) contains the course number, course name, course credits, class hours, instructors, and other student course selection information, the student ID, course number, and course score information are displayed in the score information table (T_result). In this case, in order to display the student selection information and related information of the Selected Course in the query results, you need to retrieve the course information table (T_curriculum) and score information table (T_result) at the same time ). This requires the connection query operation. 1. intranet connection query equi-join is to connect the specified connection conditions by using the equal sign operator (=) and return data rows that meet the connection conditions. The syntax format is as follows: SELECT Table Name 1. Field, table name 2. Field .... FROM table name 1, table name 2 WHERE table name 1. field 1 = table name 2. field 2. In the SELECT statement, table name 1. field and table name 2. field indicates the columns to be queried in table 1 and table 2. Table 1 and Table 2 in the FROM statement indicate the names of the connected data tables. Table 1 in the WHERE clause. field 1 = table name 2. field 2 indicates the column used to specify the connection conditions. The columns in Field 1 and in field 2 must be the columns associated with the two tables. Non-equijoin non-equijoin is a query operation that uses an operator other than the equal sign operator (=) to connect a specified condition. Other operators include,> = (greater than or equal to), <= (less than or equal to),> (greater than), <(less than), and ),! = (Not equal to), you can also use... AND operator. Select r. stuID, S. stuName, C. curID, C. curName, R. resultFROM T_result R, T_curriculum C, T_student swhere r. curID = C. curIDAND R. result> 80 use the ON clause to establish equal connections in SQL statements. In addition to using the equal sign operator (=) IN THE WHERE clause to implement equal join operations, you can also use the ON clause to establish equal join conditions. The syntax rules are as follows: SELECT Table Name 1. Field, table name 2. Field .... FROM table name 1 JOIN table name 2ON table name 1. field 1 = table name 2. in the field, the keyword JOIN indicates that table 1 and table 2 are connected, and the on clause is used to specify the column of the connection condition. When USING the USING clause to establish an equal connection, you sometimes only want to establish an equivalent connection for the columns associated with the two tables. In this case, you can use the USING clause to establish equal connections to simplify the Equi-Join Operation created USING the equal sign operator (=. The syntax is as follows: SELECT Table Name 1. Field, table name 2. Field .... FROM table name 1 JOIN table name 2 USING (Field 1) where the keyword JOIN indicates connecting table 1 and table 2. The USING clause uses parentheses to enclose Field 1, field 1 is the column in which two tables establish equijoin. 2. The result returned by the cross-join is a Cartesian product. The so-called Cartesian product is actually the result of multiplying two sets. Assume that there are n elements in set A and m elements in Set B. If the returned result is n * m, the result is the Cartesian product of set A and Set B. Select r. stuID, C. curIDFROM T_result R, T_curriculum C or FROM T_result R cross join T_curriculum C 3. The connections mentioned earlier in the Self-join query are performed between tables. In addition to different tables, you can perform connection operations on the same table. This connection query method is called self-join. A data table is connected to itself. The syntax rules are as follows: select a. Field, A. Field .... FROM table name 1 A, table name 1 B WHERE. field = B. because the fields are connected to the same table, you must define different aliases for the table in the FROM statement. Here, Table 1 defines the table aliases as a and B respectively. In the SELECT statement, you can use A. Field or B. Field to query the required records. Here the SELECT statement uses the form of A. Field. For example, in the course information table, select the course information with a higher score than that of the operating system. SELECT C2.curID, C2.curName, C2.creditFROM T_curriculum C1, T_curriculum C2WHERE C1.curName = 'OS' AND C1.credit <C2.credit 4. in the connection operation described above, the returned results are records that meet the connection conditions. In some cases, developers or users are also interested in some records that do not meet the connection conditions. In this case, you need to use external connection queries. An external connection query can return not only records that meet the connection conditions, but also records that do not match in another data table in one data table. External Connection query mainly includes three types: left outer connection, right outer connection, and full outer connection. The query results of the left Outer Join in the left Outer Join show not only records that meet the connection conditions, but also records that do not meet the query conditions in the left table. In Oracle databases, the plus sign operator (+) can be used to represent the left Outer Join. When the plus sign operator (+) appears on the left of the connection condition, it is called the left Outer Join. The syntax format is as follows: SELECT Table Name 1. Field, table name 2. Field .... FROM table name 1, table name 2 WHERE table name 1. field 1 (+) = table name 2. field 2. In the SELECT statement, table name 1. field and table name 2. field indicates the columns to be queried in table 1 and table 2. Table 1 and Table 2 in the FROM statement indicate the names of the connected data tables. Table 1 in the WHERE clause. field 1 (+) = table name 2. field 2 indicates the left Outer Join. In this case, the values of all columns in table name 1. Field 1 will be queried. You can use the LEFT [OUTER] JOIN keyword in MySQL and Microsoft SQL Server databases. The OUTER keyword is optional. The syntax for implementing the left outer join using the LEFT [OUTER] JOIN keyword is as follows: SELECT Table Name 1. Field, table name 2. Field .... FROM table name 1 left join table name 2ON table name 1. field 1 = table name 2. field 2 here, the left join keyword is used to replace the comma in The FROM statement in the SQL statement, and the on clause is used to replace the WHERE clause in the standard SQL statement, remove the plus sign operator (+) indicating the left Outer Join in the SQL statement. The right outer join is in the Oracle database. When the plus sign operator (+) appears on the right of the join condition, it is called the right outer join. The syntax format is as follows: SELECT Table Name 1. Field, table name 2. Field .... FROM table name 1, table name 2 WHERE table name 1. field 1 = table name 2. field 2 (+) can be implemented using the RIGHT [OUTER] JOIN keyword in MySQL and Microsoft SQL Server databases. The OUTER keyword is optional. The syntax for implementing the left outer join using the RIGHT [OUTER] JOIN keyword is as follows: SELECT Table Name 1. Field, table name 2. Field .... FROM table name 1 right join table name 2ON table name 1. field 1 = table name 2. in field 2, the query results of all external connections show not only records that do not meet the connection conditions in the left table, but also records that do not meet the query conditions in the right table. A full outer connection can be considered as a collection of left outer connections and right outer connections (excluding duplicate rows ). Full outer join can be implemented using the FULL [OUTER] JOIN keyword, where the OUTER keyword is optional. The syntax for implementing the left outer join using the FULL [OUTER] JOIN keyword is as follows: SELECT Table Name 1. Field, table name 2. Field .... FROM table name 1 full join table name 2ON table name 1. field 1 = table name 2. field 2 5. in the SQL connection query statement, a query method is combined with query. Set query mainly includes three types: parallel operation, transaction operation, and differential operation. Among them, the transaction and differential operations are not applicable to all mainstream databases. The keyword used for executing and performing the UNION operation is UNION. The returned result set includes all the different rows queried in two query statements, excluding duplicate rows. The syntax format is as follows: SELECT statement 1 union select statement 2 where Statement 1 and Statement 2 represent two SELECT statements used for query. The UNION keyword indicates that the query results of the two query statements are executed and operated. Make sure that the columns queried in SELECT statement 1 and SELECT statement 2 are the same, and the Data Types of the corresponding columns must be the same. INTERSECT is the keyword used to perform the INTERSECT operation. The result set returned by the transaction operation includes the public rows that connect to the query results. Duplicate rows are not displayed in the submit operation. The syntax format is as follows: SELECT statement 1 intersect select statement 2 where Statement 1 and Statement 2 represent two SELECT statements for query. The INTERSECT keyword indicates that the query results of the two query statements are handed in. Make sure that the columns queried in SELECT statement 1 and SELECT statement 2 are the same, and the Data Types of the corresponding columns must be the same. MINUS is the keyword used to execute the delivery operation. The record result set returned by the difference operation only exists in the first SELECT statement, but does not exist in the query results of the second SELECT statement. The syntax format is as follows: SELECT statement 1 minus select statement 2

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.