SQL multi-Table link query and subquery embedding SELECT statements

Advanced Query Technology involves the link query technology of multiple tables, the subquery technology embedded in SELECT statements, and the joint technology that combines multiple queries.

1. Connection Query

You need to retrieve data from two or more tables at the same time. The link allows you to retrieve data from two or more tables at the same time and specify one or more columns in these tables as the join condition. In SQL Server, two types of connection syntax can be used, one is the ANSI link syntax, which is the connection condition in the FROM clause; the other is the SQL Server link syntax, this is the connection condition that appears in the WHERE condition.

1. ANSI Link

If the Link error occurs, data in two or more tables can be queried at the same time. The generated result set contains fields in multiple tables, you need to use the fields in a join table to join multiple tables.

During the connection operation, the SQL statement compares the specified fields one by one row, then merges the comparison results with the data that meets the conditions, and generates a new record.

There are three Connection Methods: inner connection, outer connection, and cross connection. In a SELECT statement, multiple tables can be connected. by extending the FROM clause of the SELECT statement, two keywords are added: JOIN and ON.

JOIN: Specifies the table to be linked.

ON: Specifies the fields shared by these tables.

Specify the connection conditions based on the primary key and external key of the table.

The ANSI link syntax is as follows:

SELECT table_name.column_name, table_name.column_name ,......

FROM {table_name [join_type] JOIN table_name ON search_conditions}

WHERE [search_conditions]

[Join_type] can be in the following three keywords:

INNER (inner join): the link query result contains only the rows that meet the conditions. inner join is the default JOIN method of SQL Server. INNER JOIN can be abbreviated as JOIN;

OUTER (OUTER Join): the results of a link query include both the rows that meet the conditions and all the rows in a table. There are three forms of OUTER Join: left Outer Join, right outer join, and full outer join.

For example, if you have already taken course 4 student information, this example involves the student table and electives:

SELECT student table

FROM student table JOIN Course Selection table ON student table. Student ID Course Selection table. Student ID

WHERE course number = 4

2. SQL Server Link

Multi-table join: You can specify multiple tables directly after the FROM clause. in semantics, you can retrieve data FROM the Descartes of these tables. You can use the WHERE clause to set filtering conditions.

The SQL Server link syntax is as follows:

SELECT table_name.column_name, table_name.column_name ,......

FROM {table_name, table_name ,......}

WHERE table_name.column_name join_operator table_name.column_name

In this syntax, the FROM clause lists all the table names used during the connection. The WHERE clause specifies which rows should appear in the result set, that is, the WHERE clause is used to set the filter condition. In the WHERE clause, use the link operator in the two connected columns.

For example, the following example shows the information of at least one student passing the course:

Select distinct student table *

FROM student table Course Selection table

WHERE student table. Student ID = Course Selection table. Student id and Course Selection table. Score = 60

3. subquery

A subquery is a series of SELECT statements. SELECT statements can be nested in many other statements, such as SELECT, INSERT, UPDATE, and DELETE. These nested SELECT statements are called subqueries. Subqueries can divide a complex Query into a series of logical steps, so that a single statement can be used to solve a complex query problem. Subqueries are useful when one query depends on the results of another query.

Note the following when using subqueries:

The subquery should be enclosed in parentheses.

Only one value or a series of values can be used as a subquery instead of an expression.

The subquery cannot query fields that contain text or image data types.

Subqueries can also contain subqueries. nesting can be up to 32 layers.

1. Use the subquery as a derived table

A subquery can be used to generate a derived table instead of the table in the FROM clause. Derivation represents a special usage of subqueries in the from clause. This derived table is referenced by an alias or a user-defined name. The subquery in the from clause returns a result set, and the table formed by this result set is used by the outer SELECT statement.

For example, if an inner-layer query generates a derived table using a subquery, the outer-layer query uses the result set of the Inner-layer query. In terms of function, the derived table itself is equivalent to a complete query.

Slect *

FROM select student ID, name, age from student table

Where class = 'yz02 6' as

2. Use the subquery as an expression

In the T-SQL, all places that use expressions can be replaced with subqueries. In this case, the subquery must return a single value or a field value. A subquery can return a series of values to replace the IN keyword expression that appears IN the WHERE clause.

For example, query the average age of Class 7 students in GZ02 and the difference between the age and the average age of each student.

SELECT avg (AGE) FROM student table as average age

The calculation result is used as an output column in the selection list and as part of the arithmetic expression:

Age-(SELECT avg (AGE) FROM student table) as age difference

3. Related subqueries

The related subquery can be used as a dynamic expression. The value of this expression changes with respect to each row in the outer query. The query processor calculates the value of a subquery for each record in the outer query, one row at a time, and each subquery is calculated as an expression and returned to the outer query. Related subqueries are a very effective combination between dynamically executed subqueries and outer queries.

When related subqueries are used, the inner-layer subqueries are repeatedly executed, and the inner-layer queries are several tooth-shaped times based on the number of records in the outer queries.

For example, you can query the student ID and name of one of the students whose scores are 90 or higher:

SELECT student ID name

FROM student table

WHERE 90 <= (SELECT score

FROM Course Selection table

WHERE student table. Student ID = Course Selection table. Student id and course number = 1)

4. Use the EXISTS and not exists Operators

In related subqueries, you can use the EXISTS and not exists operators to determine whether a value is in a series of values. When SQL Server Processes subqueries with the EXISTS and NOT EXISTS operators:

Whether the record returned by the outer query test subquery exists

Based on the conditions specified by the query, the subquery returns TRUE or FALSE

Subqueries do not generate any data

For example, the information of the students who have selected course 1 and course 2 at the same time:

SELECT student ID, name, class

FROM student table

Where exists (SELECT * FROM course selection table

WHERE student ID = student table. Student id and course number = 1)

And exists (SELECT * FROM course selection table

WHERE student ID = student table. Student id and course number = 2)

① Find the second row of the "Student table" in the outer table and process the inner layer query based on its "student ID" Value

② Compare the "student ID" of the outer layer with the "student ID" of the "Course Selection table" of the inner layer, thus determining the true and false of the outer layer conditions. If it is true, the record is a result that meets the conditions. Otherwise, the record is not output.

③ Process 2nd, 3, and 4 in the "Student table" of the outer table in sequence ,... Line

Retrieve Information about several students in each Elective Course

SELECT * FROM student table WHERE

Not exists (SELECT * FROM course selection table

WHERE student table. Student ID = Course Selection table. Student id and score <60)

And exists (SELECT * FROM course selection table

WHERE student table. Student ID = Course Selection table. Student ID)

Use TOP to limit the result set

When using the SELECT statement for query, we sometimes want to list the first few results, not all results. For example, in a competition, you may only get the TOP three with the highest score. In this case, you need to use the TOP keyword to select the output result.

TOP format:

Select top n [percent] [with ties] query list

Where:

N: non-negative integer

TOP n: The first n rows of the query result.

TOP n percent: The first n % rows of the query result

With ties: displays the result of the parallel operation.

For example, you can retrieve the student ID and total score of the top five students with the total score:

Select top 5 with ties student ID, SUM (score) AS total score

FROM Course Selection table

Group by student ID

Order by total DESC

When using TOP, note that it is best to use it with the order by clause, because the first few are meaningful. However, when using with ties, the order by clause must be used.

Merge multiple result sets

Two or more query results can be combined into a result set, which is the meaning of merging multiple international sessions. UNION can be used to merge multiple query result sets. The format of UNION is as follows:

SELECT Statement 1

UNION

SELECT Statement 2

UNION [ALL]

......

SELECT statement n

Note the following points when using UNION:

By default, the UNION operator deletes all spaces. If the ALL option is used, the idle rows are not deleted:

The columns and columns in all query statements must be in the same order.

The Data Types of corresponding columns in all query statements must be compatible.

If the UNION statement contains an order by clause, the entire result set is sorted.

In the result set, the column names are from 1st SELECT clauses.

For example, the query results of GZ02 6 and GZ02 7 are combined into a result set:

SELECT * FROM student table WHERE class = 'yz02 6'

UNION

SELECT * FROM student table WHERE class = 'yz02 7'