Connection query and set query _ MySQL

Link query and set query bitsCN.com

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. query internal connections

Equijoin

Equivalent join means to connect a specified connection condition by using the equal sign operator (=) and return data rows that meet the connection condition. 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, the table name is 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-equivalent join

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. 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 connections

In addition to using the equal sign operator (=) in the WHERE clause, you can also use the ON clause to create 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 2

ON table name 1. Field 1 = table name 2. Field

The keyword JOIN is used to connect table 1 and Table 2. the ON clause is used to specify the columns of the JOIN condition.

Use the USING clause to establish equal connections

During the connection operation, 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)

The keyword JOIN indicates that table 1 and Table 2 are connected, and field 1 is enclosed by brackets in the USING clause. Field 1 is the columns in the two tables that establish equijoin and associate with each other.

2. Cross join

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. curID

FROM T_result R, T_curriculum C

Or FROM T_result R cross join T_curriculum C

3. Self-connection query

The preceding connections 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 a. field = B. Field

Since the same table is connected, you need to 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.credit

FROM T_curriculum C1, T_curriculum C2

WHERE C1.curName = 'OS'

AND C1.credit

4. query external connections

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.

Left outer join

The query results 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, the table name is 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 rules for implementing left outer join using the LEFT [OUTER] JOIN keyword are as follows:

SELECT table name 1. field, table name 2. field ....

FROM table name 1 left join table name 2

ON 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, the ON clause is used to replace the WHERE clause in the standard SQL statement, and the plus sign operator (+) representing the outer left join in the SQL statement) remove.

Outer right connection

In Oracle databases, when the plus sign operator (+) appears on the right of the connection 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 (+)

You can use 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 2

ON table name 1. Field 1 = table name 2. field 2

All external connections

The query results in the outer join 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 rules for implementing the left outer join using the FULL [OUTER] JOIN keyword are as follows:

SELECT table name 1. field, table name 2. field ....

FROM table name 1 full join table name 2

ON table name 1. Field 1 = table name 2. field 2

5. set query

In the SQL connection query statement, another 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.

And operate (UNION)

The keyword used for execution and 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

Statements 1 and 2 represent two SELECT statements 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)

The keyword used to perform the transaction operation is INTERSECT. 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

Statements 1 and 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.

Differential operation (MINUS)

The keyword used to execute the submit operation is MINUS. 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

BitsCN.com