Table of SQL syntax joins

First, the connection conditions

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

[1] Equivalent connection query

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

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

[2] The elimination of data in the equivalent result is a natural connection

Select P.name,c.countryname from country as c,person as P where C.countryid = P.countryid

[3] Self-connection

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

Three , internal connection

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    < connection conditions >[  ]

INNER can be omitted, only the JOIN is omitted INNER. An inner join is a traditional connection operation, where a join condition is specified with an ON clause, and a WHERE clause is used to specify additional qualifications for the connection:

Selectfrom asinnerjoinon= C.countryid

Four , LEFT outer join query

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

Select < attribute or expression list > from < table name > LEFT OUTER join < table name > on < join condition > [where < qualifying >]

Such as:

Select P.name,c.countryname from country as C left join person p on P.countryid = 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 or expression list > from < table name > right outer join < table name > on < join condition > [where < qualifying >]

Such as:

Select P.name,c.countryname from country as C right join person p on P.countryid = 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 or expression list > from < table name > full outer join < table name > on < join condition > where < qualification;]

Such as:

Select P.name,c.countryname from country as C full join person p on P.countryid = 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 LastName + firstname from name2 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 name1 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,count (p.id) from country as C inner join person as p on C.countryid = P.countryid GROUP BY C.countryn Ame

Output Result:

　　　　

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

Select C.countryname,count (p.id) as Cofrom Country as C inner join person as p in C.countryid = P.countryid GROUP BY C.cou Ntrynameorder by CO ASC

Output Result:

　　　　

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

Select C.countryname,count (p.id) as Cofrom Country as C inner join person as p in C.countryid = P.countryid GROUP BY C.cou Ntrynamehaving count (p.id) > 2order by CO ASC

The output is:

　　　　

Table of SQL syntax joins