MSSQL five connection query and sub-query

In an advanced database, the data being browsed can be stored in multiple tables. When you need to browse data from a related table, you can query the data by using the Common Properties join table. You can use subqueries, where the results of one query are used as input to the criteria of another query. This chapter discusses how to query data from a win by applying various types of connections, such as inner joins, outer joins, Cross joins, equivalent connections, or self-joins. Further, it explains how to use subqueries Focus ? Querying data using a connection ? Querying data using subqueries Preview lessons ? Several ways to connect queries ? How to use sub-queries

Querying data using a connection

The connection between data tables is reflected by the field values of the table, which are called join fields. The purpose of the join operation is to concatenate multiple tables by adding the criteria in the Join field to query the data from multiple tables. The preceding queries are all for one table, and when the query involves more than two tables, it is called a connection query. Connection queries are mainly divided into the following formats:

1. Internal connection

2. External connection

3. Cross-Connect

4. Self-Connection

5. Equivalent connection and non-equivalent connection

? Internal connection

An inner JOIN uses comparison operators to extract data from multiple tables on a public column. When an inner connection is used, only columns that meet the values of the join condition in the public column are displayed. Two rows in a table that do not meet the join criteria are not displayed.

Grammar:

Select Table name. column name [..., table name. Column Name] FROM table name 1 join table name 2 on table name 1.ref_ table name 2. Column name comparison operator table name 1.ref_ table name 2. Column Name

For example [5-1]

Select Studentinfo.sname, Studentinfo.ssex,grade.gradefrom studentinfo [inner] join grade on Studentinfo.sid=grade. Sid

Internal connection output:

? External connection

The outer join displays a result set that contains all the rows from one table and the matching rows from another table.

There are three types of outer joins:

1. Left connection

2. Right connection

3. Fully connected

Left join returns a matching row for all rows of the specified table and the right side of the specified table on the left OUTER join keyword. For rows from the table on the left, no matching rows are found in the table specified on the right, and a null value is displayed in the column from the table specified on the right for the data. The same is true for right connections.

A full connection is a combination of left and right connections. This connection returns all matching and non-matching rows from two tables. However, the matching record is displayed only once. In the case of non-matching rows, null values are displayed for columns that are not available for data.

Grammar:

Select Table name. column name, table name. Column name [,.... Table name. Column Name] FROM table name 1[left |right |full] out join table name 2 on table name 1. Reference column name join operator table Name 2. Reference Column Name

For example: Selectkecheng.cmame as course, Grade.grade as score from Kecheng to outer join grade on grade. Cid=kecheng. Cid

Foreign key Connection output:

? Cross Connect

Cross joins, also called Cartesian products, connect each row in one table to each row in another table in two tables, and the number of rows in the result set is the product of the number of lines in the first table and the number of rows in the second table. This means that if there are 10 rows in table A and 5 rows in table B, the 10 rows in table A are connected to the 5 rows in table B. The result set will contain 50 rows.

Syntax: SelectA. Column name, b. Column name from Across join B

For example: write in the query window:

Selectw.tid,w.tname,p.cid,p.cmame as ' course name ', P.cperiod

From TEACHERS W Cross Joinkecheng P

? Self-Connection

When a table is connected to its own operation, it is called the table's own connection. To query for a row in one table with another row in the same table, in order to distinguish between two instances of the same table, you can take a table with two aliases, one x and one Y. Connect the rows in x, y that satisfy the query criteria. This is actually the self-connection of the same table.

Syntax: select x. column name, x. column name [as Alias],y. Column name, Y. Column name [as alias] from table name x, table name y[where x. Column name =y. Column Name]

Note: Content within "[]" is optional

For example: Query Studentinfo table student's ID, name, QQ, telephone

Select X.sid,x.sname as name, Y.sqq,y.sphone as phone from Studentxinxi X,studentxinxi y

? Equivalent connection and non-equivalent connection

Grammar:

[< table name 1>.] < column name 1>< comparison operator > [< table name 2>.] < column name 2>

Among them, the comparison operators are mainly: =, >, ", >=, <=,!" = When the comparison operator is "=", it is called an equivalent connection, and the other case is a non-equivalent connection.

Note: Equivalent joins and outer joins use foreign keys to join tables. However, it is used to display all columns for two or more tables. The public columns of all connected tables are displayed.

For example:

Select *from Studentinfo A join grade B on A.sid=b.sid join Kecheng K on K.cid=b.cid

The WHERE clause contains a query block, such as Select-from-where, which is called a subquery or a nested query, and the statement that contains the subquery is called a parent query or an external query.

Focus:

1. Returns a subquery for a value

2. Returns a subquery for a group

? A subquery that returns a value

When the return value of a subquery has only one value, you can use the comparison operator (=,; <, >=, <=,! =) to concatenate the parent and child queries.

For example: write the following code in the query window:

Select Sid,sname,saddress from Studentxinxi where

Sid= (Selectsid from Studentxinxi where sname= ' Jet Li ')

? Returns a subquery for a group

If the subquery has more than one return value, instead of a collection, you cannot use the comparison operator directly, and you can insert any or all between the comparison operator and the subquery.

1. Using in

2. Using exists

3. Use all

4. Use any

Using the In

Grammar:

Select Column name [.... column name] FROM table name where column name [NOT] in

(select Column name from table name [where conditional expression])

Description: You can use in instead of "=any".

For example, write the following code in the query window:

Select Cid,grade from grade where SID in

(select SID from Studentxinxi where sid= ' niit0806 ')

The output results are as follows:

Using exists

exists represents the existence of quantifiers, a subquery with exists does not return any actual data, it only gets the logical value "true" or "false". When the query result set of a subquery is non-null, the outer WHERE clause returns TRUE, otherwise false values are returned.

Grammar:

Select Column name [.... column name] FROM table name whereexists

(select Column name from table name [where conditional expression])

Description: You can use in instead of "=any".

Write the following code in the query window:

Selectcid,grade from grade where exists

(select SID from Studentxinxi where sid= ' niit0806 ')

The following table shows the operators that use the all and any keywords:

Operator	Describe
>all	Represents greater than the maximum value in the list An expression | Column name >all (10,20,30) means "greater than 30"
>any	Represents the minimum value that is greater than the list Expression Column name >any (10,20,30) means "greater than 10"
=any	Represents any value in the list, as it does in the in clause. Expression Column name =any (10,20,30) means "equals 10 or 20 or 30"
<>any	Represents a value that is not equal to any list. Expression Column name <>any (10,20,30) means "not equal to 10 or 20 or 30"
<>all	is not equal to all values in the list, as does the not-in clause Expression Column name <>all (10,20,30) means "not equal to 10 and 20 and 30"

Write the following code in the query window:

Select Sid,saddress from Studentxinxi where

Sid>all (select SID from grade where grade=98)

Practical issues

1. When do I use subqueries?

2. Write a query to show the employee ID and the plant with an employee payment rate greater than 40.

3. You need to extract all the columns from both tables. What kind of connection will you use?

A, cross-connect

B, equivalent connection

C, self-connected

D, right connection

Summary

1 joins and subqueries are used to extract data from multiple tables.

2. Inner joins use comparison operators to combine records from multiple tables on a public column.

3. The left OUTER join returns all rows from the left table and the matching rows from the right table.

4. The right outer join returns all rows from the right table and matching rows from the left table.

5. A full outer join returns all connections from each row of the first table and each row from the second table.

6. The equivalent connection is used to display all the columns of the connected table.

7. The self-connection relates a row to other rows in the same table.

8. Returns 0 or more values in the subordinate clause of the in subquery.

9. Returns a value of TRUE or FALSE in the subordinate clause of the EXISTS subquery.

The all and any keywords are used to modify an existing comparison operator in a subquery.

11. Aggregate functions are also used in subqueries to produce collection values from internal queries.

12. Subqueries that contain one or more queries are called embedded subqueries.

13. The related query is defined as a query that relies on evaluation with an external query.

MSSQL five connection query and sub-query