SQL multi-Link query and sub-query technology embedding SELECT statement

Advanced query technology is mainly related to multiple tables linked query technology, embedded SELECT statement subquery technology, the combination of multiple queries combined technology.

1. Connection Query

You need to retrieve data from two or more tables at the same time. A link allows you to retrieve data from two tables or more than two tables at the same time, specifying one or more of these tables as a join condition. In SQL Server, you can use the form of two connection syntaxes, one of which is the ANSI link syntax form, where the join condition appears in the FROM clause, and another form of SQL Server link syntax, where the join condition appears in the Where condition.

1. ANSI Link

Link errors you can query data from two or more tables at the same time, the resulting set contains fields from multiple tables, and you need to use fields that are commonly owned in a table to connect multiple tables.

When a connection operation is made, the SQL row compares the fields that you specify, and then merges the comparison results with the data that satisfies the criteria and generates a new record.

There are three ways to connect: internal, external, and cross connections. In a SELECT statement, you can connect multiple tables; The link adds two keywords by extending the FROM clause of the SELECT statement: Join and on

JOIN: Specifies the table to which to link

On: Specify the fields that these tables have in common

Specify the join condition based on the primary key and the foreign key of the table.

The ANSI link syntax form looks like this:

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 form of the following three keywords:

INNER (inner join): The linked query result set contains only the rows that meet the criteria, and the inner join is the default connection method for SQL Server, and the INNER join can be shortened to join;

OUTER (outer join): The linked query result set contains both rows that meet the criteria, and all rows of one of the tables, with three forms of outer joins: Left outer joins, right outer joins, and full outer joins.

For example, an example of a student's information that has taken the number 4th course, which involves students ' tables and electives:

SELECT Student Table

From student form JOIN the selected timetable on the student table. School number selection timetable. School Number

WHERE to select the curriculum number =4

2. SQL Server Links

Multiple table joins, you can specify multiple tables directly after the FROM clause, meaning that the data is retrieved from the Cartesian product of these tables, and the filter conditions can be set with the WHERE clause.

The SQL Server link syntax forms the following:

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 form, the FROM clause lists all the table names used by the connection, where clauses specify which rows should appear in the result set, and the WHERE clause is used to set the filter condition. In the WHERE clause, use the link operator in two connected columns.

For example: Retrieves an example of information about a student who has at least one course pass:

SELECT DISTINCT Student Table *

From Students ' Table selection schedule

WHERE Student table. Study Number = Select the timetable. The school number and the timetable. Grade =60

3. Sub-query

A subquery is a series of SELECT statements. A SELECT statement can be nested within many other statements, such as SELECT, INSERT, UPDATE, delete, and so on, and these nested SELECT statements are called subqueries. Subqueries can decompose a complex query into a series of logical steps so that a single statement can be used to solve a complex query problem. Subqueries can be useful when a query relies on the results of another query.

When using subqueries, be aware that:

Subqueries need to be in parentheses

You need only one value or a series of values, you can use a subquery instead of an expression

You cannot query a field that contains a text or image with a data type in a subquery

Subqueries can also be included in subqueries, nesting up to 32 levels

1. The handle query is used as a derived table

You can use a subquery to produce a derived table that replaces the table in the FROM clause. Derivation represents a special use of a neutron query in the FROM clause, referencing the derived table with an alias or user-defined name. A subquery in the FROM clause returns a result set that is used by the outer SELECT statement to form the table.

For example, an inner query produces a derived table with a subquery, and the outer query uses the result set of the inner query. Functionally, the derived table itself is equivalent to a complete query

Slect A *

From select school number, name, age from student table

Where class = ' GZ02 6 ' as a

2. The handle query is used as an expression

In T-SQL, all where expressions are used, you can substitute subqueries. The subquery must return a single value or a value of one field at this time. A subquery can return a series of values in place of an expression in the In keyword that appears in the WHERE clause.

For example: Check the average age of 7 students in GZ02 and the difference between the age and average age of each classmate

SELECT avg (age) from student table as average age

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

Age-(SELECT avg (age) from student table) as age difference

3. Related subqueries

A correlated subquery can be used as a dynamic expression, and the value of the expression changes relative to each row of the outer query. The query processor computes the value of the subquery for each record of the outer query, one row at a time, and the subquery is evaluated and returned to the outer query as an expression. A correlated subquery is a very effective union between a dynamically executing subquery and an outer query.

When using the correlated subquery, the inner subquery is executed repeatedly, how many records the outer query has, and how many times the inner query is tooth shaped.

For example: Check the number and name of the students who have taken 1 of the course number and have scored more than 90 points:

Name of the SELECT school number

From student table

WHERE <= (SELECT results

From selected timetable

WHERE Student table. School Number = Select a timetable. School number and course number =1)

4. Using the exists and not EXISTS operators

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

The outer query tests whether the records returned by the subquery exist

The subquery returns TRUE or FALSE based on the criteria specified by the query

Subqueries do not produce any data

For example: At the same time take the 1th and 2nd students of the course of information:

SELECT School Number, name, class

From student table

WHERE EXISTS (SELECT * from selected timetable

WHERE number = Student table. School number and course number =1)

and EXISTS (SELECT * from selected timetable

WHERE number = Student table. School number and course number =2)

① find the 1th row of the outer table "Student table" and process the inner query according to its "number" value

② uses the outer "study number" and the inner table "The choice timetable" the "school number" comparison, thus decides the outer condition the true, the false, if is true, then this record is conforms to the condition the result, conversely, does not output.

③ Order Process 2nd, 3, 4 、。。。 in the "Student table" of the outer table Yes

Retrieves a few student information for each elective course

SELECT * FROM student table WHERE

Not EXISTS (SELECT * from selected timetable

WHERE Student table. School Number = Select a timetable. School number and result <60)

and EXISTS (SELECT * from selected timetable

WHERE Student table. Study Number = Select timetable. School Number)

Using top to limit result sets

When querying using a SELECT statement, sometimes we want to list the first few results, not the full results. For example, when a contest is likely to take only the top three of the highest score, you need to select the results of the output using the upper keyword

Use Top's format as:

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

which

N: is a non-negative integer

Top N: The first n rows that represent the results of a query

Top N Percent: The first n% line that represents the result of a query

With ties: means include side-by-side results

For example: To retrieve the total score in the top 5 students of the student number and its total score:

SELECT Top 5 with TIES number, SUM (score) as total score

From selected timetable

GROUP by School Number

Order BY total score 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, it is required that the ORDER BY clause be used

Merging multiple result sets

You can combine the results of two or more queries into one result set, which is the meaning of merging multiple international sessions. You can use Union to implement the purpose of merging multiple query result sets. The format of the Action Union is:

SELECT Statement 1

UNION

SELECT Statement 2

UNION [All]

......

SELECT Statement N

Use union, you should pay attention to several points:

By default, the Union operator deletes all of the spare. If you use the All option, the free rows are not deleted:

The number of columns and columns in all query statements must be the same

The data type of the corresponding column in all query statements must be compatible

If you include an ORDER BY clause in a UNION statement, the entire result set is sorted

In the result set, the column name comes from the 1th select clause

For example, the query results for the GZ02 6 and GZ02 7 students are merged into one result set:

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

UNION

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