SQL query-table connection

I. Introduction of connection Query

The conditions used to connect a table in a connection query are called join conditions or connection predicates. The form is:

[< table 1>]. < column Name 1>< join operator >[< table 2>]. < column 2>　　

Common join operators include the

1. Comparison operators: =, >, <, >=, <=,! =, between, and and.

2. Logical operator: not, and, or.

3, using between and and connection query form for [< table 1>].< column name 1><between>[< table 2>].< column name 2>and[< table 2>].< column name 3 >.

Second, the connection according to the result set classification

1. Inner connection: The rows in the table are connected to each other. The number of rows in the result set equals the product of the number of rows per table that satisfy the condition, and the representation of the join is equal.

2, outer connection: Participate in the connection of the table has primary and secondary points, the main table of each row of data to match the data column from the table, the data matching the connection condition will be returned directly to the result set, the data column that does not conform to the join condition will be returned to the result set with null padding, the outer connection is divided into left outer

(a), equivalent connection query

Select p.*, C.*from as aswhere= P.countryid

In the equivalent connection above, both tables have countryid fields, so there will be two columns in the results found Countryid

(b), the elimination of data in the equivalent result is a natural connection

Select  from  as  as where = P.countryid

(c), self-connection

A table of data itself connected to itself is called self-connection

three , internal connection query

1. The syntax structure of the INNER join query is as follows:

　　　　　　Select < attribute or expression list >from< table name >[inner] Join < table name >on< join conditions >[] 　　　　　　　　　　　　　　　　　　

Inner can be omitted, when only the join is omitted inner. An inner join is a traditional connection operation, where you specify a join condition with an ON clause and a WHERE clause to specify additional qualifications:

Select  from  as Inner Join  on = C.countryid

four . Left outer connection query

　　1. The syntax structure of the LEFT Outer connection query:

　　　　Select < attribute orexpression list >from< table name >leftouterJoin  < table name >on< connection conditions >[   ]　　　　　　　　　　　　

Such as:

Select  from  as  Left Join  on = C.countryid　

　　all records in the first table that meet the criteria are included in the result table, and if the records match on the connection connection, the second table returns the corresponding value, otherwise the second table returns NULL. that is, regardless of whether the second table has a record, all the fields of the first table will be returned, which is the difference between an outer join and an inner join.

Five, right outer connection query

1. The syntax structure of the right outer join query is as follows:

　　　　Select < attribute orexpression list >from< table name >Rightouter  Join< table name >on< join condition >[  ]　　　　　　　　　　　　　

Such as:

Select  from  as  Right Join  on = C.countryid

　　In the results table, include all records in the second table that meet the criteria. If the record is matched on the join condition, the first table returns the corresponding value, otherwise the first table returns NULL.

six, full-outer connection query

1. The syntax structure of the full outer join query is as follows:

　　　Select < attribute orexpression list >from< table name >fullouterJoin  < table name >on< join conditions >where< qualifications >]　　　　　　　　　

Such as:

Select  from  as  Full Join  on = C.countryid

All records in the results table that meet the criteria are included in the two tables. If the tuple is matched on the join condition, the other table returns the corresponding, and no returns NULL.

Seven, cross-linking

Cross-connect Corss join, used to combine all records from the first table with all records of the second table once and back, is useful when generating a test database, for example, you define 7 last names, 7 names, and then cross-connect to generate 49 records.

If you have the following table: Last Name table

　　　　

Name table:

　　　　

Execute the following SQL statement:

Select +  from  Cross Join name1

The results are as follows:

　　　　　　

8. Joint Query Union ( UNION ALL)

Union is a special operator that produces one result set for two or more than two queries. Join joins the information horizontally (adding more columns), and union connects the information vertically (adding more rows).

There are several key points to note when using union to process the query.

(1), all union queries must have the same number of columns in the select list. That is, if the first query has 3 columns, the second query will have only 3 columns.

(2), union returns the title set of the result is only obtained from the first query, regardless of how the second query is named or the alias is not changed.

(3), the data type of the corresponding column in the query must be implicitly consistent. Note that you do not require full consistency, only implicit consistency.

(4), unlike other non-union, the default return option for union is distinct, not all. the difference between the Union ALL statement and the Union is simply that the same record is encountered, all reserved.

For example, using the 7th example, execute the following statement:

Select *  from  UnionSelect* from name2

The returned result is:

　　　

Since union is the default distinct query, you can use union all when you want to get all the records, so even if the two tables in the example above have ' king ', they will also return two records.

Now take a look at the composite example:

To demonstrate, two tables were built and several records were added, as follows:

Person table

　　　　

Country table

　　　　

1. Query A list that contains the list of country names and the total number of person in that country.

Select c.countryname,countfrom asinnerJoin  as on= Group by C.countryname

Output Result:

　　　　

Now we are adding a condition that requires the population to be sorted in ascending order:

Select c.countryname,count as Cofrom asinner  Joinas on= Group by  C.countrynameorderbyASC

Output Result:

　　　　

Add a condition that requires that only records with a person number greater than 2 be exported:

SelectC.countryname,Count(p.id) asCo fromCountry asCInner JoinPerson asP onC.countryid=P.countryidGroup  byC.countryname having Count(p.id)> 2Order  byCoASC

The output is:

　　　　

SQL query-table connection