SQL Application and Development: (v) connection of multiple data tables

Each table in the database stores different data, and users often need to combine and refine the information they need with data from multiple tables. If a query needs to operate on more than one table, it is called a connection query. The result set or result table of a connection query is called a connection between tables. The query is actually querying the data through the correlation of the common columns among the tables, which is the main characteristic of the relational database query.

The result of a connection is that there is no limit to the addition of new types of data to the database, with great flexibility. Usually a new table is always created from the connection to contain data from different tables. If the new table has a suitable domain, you can connect it to an existing table.

1. Simple connection operation

The simplest way to connect is to separate the different base tables with commas in the FROM clause of the SELECT statement, and the equivalent connection created with the WHERE clause is a connection that causes the value in one or more columns in the first base table to be equal to the value of one or more columns in the second base table. This displays only rows that match the values of the columns in the two base tables in the query results. It is important to note, however, that the column names are restricted by increasing the table name, regardless of whether the columns in the different tables have the same column names.

For example, in the database "Sales relations system", from "salesman information" and "Customer information" in a table, query "salesman number" is greater than 1004, and in the select list, add a column "salesman number":

SELECT salesman information. Salesman number, salesman information. Salesman number, customer information. Customer's name, customer information, customer's address, customer information. Contact number

From salesman information, customer information

WHERE salesman information. Salesman Number = Customer information. The salesman number and salesman information. Salesman Number >1004

After implementation, it is the result of our condition.

In this query, we can also use the relevant name, "Salesman information" table with alias a instead, "Customer information" table with B instead, when referring to this even table, in addition to the FROM clause, in the SELECT statement anywhere can use a and B, then we have the above statement becomes:

SELECT A. Salesman number, A. Salesman number, B. Customer's name, B. Customer address, b. Contact number

From salesman information A, customer information B

WHERE A. Salesman number =b. The salesman number and a. Salesman number >1004

The result of the execution is exactly the same as before.

This also lays the groundwork for the learning of connections behind multiple tables. For example, in the database "Jewelry sales system", a query is created based on the three tables: jewelry information, jewelers information, and sales details. Required to return the column "jewelry name" in the "Jewelry information" table, "Jeweler's Information" column in the "Jeweler name" and "Sales details" table in the column "profit":

SELECT A. Jewelry name, B. Jeweler's name, C. Profit

From jewelers information as A, jewelers information as B, sales details as C

WHERE A. Jewelry code =c. Jewelry code Name and A. Jeweler number =b. Jeweler number

After execution, it is the query result set.

2. Internal connection

Internal connection is the most commonly used connection query, generally with inner jion keyword to specify the inner connection. However, inner is not required. If you use only the Join keyword, you must also define an on clause. The INNER JOIN query operation lists the data rows that match the join criteria, which uses comparison operators to compare the concatenated column values.

2.1 Equivalent connections

The so-called equivalent connection interpretation uses the equals sign (=) operator in the join condition to compare the value of the concatenated column, and its query results list all the columns in the joined table, including the repeating columns. In other words, a query that joins a connection between a base table through an equal column value is an equivalent join query.

An equivalent join query can specify a join condition in two ways. For example, in the database Sales affiliate system, create a query based on the product information and two tables of the vendor information. The qualified query condition is returned when the "vendor number" is equal in two tables, and requires that the column returned in the Product information table be listed as the "Vendor name", "Contact name" in the "Product name" and "Vendor information" tables.

SELECT A. Name of the product, B. Name of the supplier, B. Name of the contact person, A. Unit Price

From product information A, supplier name B

WHERE A. Vendor number =b. Vendor number

In the WHERE clause of the above statement, use "=" to specify that the query is an equivalent connection query.

You can also use the INNER JOIN keyword in the FROM clause of a query statement to specify that the query is an equivalent connection query.

SELECT A. Name of the product, B. Name of the supplier, B. Name of the contact person, A. Unit Price

From product information A INNER JOIN supplier Information B

On A. Vendor number =b. Vendor number

After execution, the result is the same as the above statement.

You can also use the ORDER BY clause to sort the results of a join query. For example, the query for the equivalent connection query above is arranged in descending order of the column "Unit Price".

SELECT A. Name of the product, B. Name of the supplier, B. Name of the contact person, A. Unit Price

From product information A INNER JOIN supplier Information B

On A. Vendor number =b. Vendor number

ORDER by A. Unit Price DESC

After running, the only difference from the above results is that the results of the query are sorted in descending order based on the "Unit price".

2.2 Non-equivalent connections

The equals sign is not applicable in the join condition of the equivalent join query, and the use of other comparators constitutes a non-equivalent join query. That is, a non-equivalent join query is a comparison operator that uses the equals operator in a join condition to compare the column values of the concatenated columns. In non-equivalent connection queries, you can use the:>, <, >=, <=,! =, and between ... Keywords like and.

For example, from the database sales management system, create a query based on the two tables of vendor information and inbound order information. The qualifying query condition is not equal to the "vendor number" in two tables and returns the column "Vendor name" and "Supplier City".

SELECT supplier name, the city where the supplier is located

From supplier information A INNER JOIN Inbound Receipt Information B

On A. Vendor number!=b. Vendor number

After executing the above statement, its query results return 80 rows, most of which are duplicates. We use the following statement to eliminate duplicate rows.

SELECT DISTINCT supplier name, the city where the supplier is located

From supplier information A INNER JOIN Inbound Receipt Information B

On A. Vendor number!=b. Vendor number

The information in the query results is identical to the information in the "Vendor name" and "Supplier City" listed in the "Supplier Information" table, so the above query does not actually apply value, which means that non-equivalent connection queries often need to be used in conjunction with other connection queries, especially with equivalent connection queries.

2.3 Natural Connections

A natural join is the use of the equals sign (=) operator in the join condition to compare the column values of the connected column, but it uses a selection list to indicate the columns contained in the query result set and to delete the duplicate columns in the Join table. Simply put, the duplicate attribute column is removed from the equivalent connection, which is the natural connection.

Natural joins automatically record matching for columns with the same name. Natural connections do not have to specify any equivalent join conditions. The SQL implementation determines that a column with the same name then forms a match. However, a natural connection can specify the columns that the query results include, but cannot specify the columns to be matched.

For example, in the database "Jewelry Marketing System", a natural connection query was created based on the "Customer information" and "jeweler information" two tables. The limit for this connection query is two tables in which the "Consumer city" and the "jeweler's city" are the same, and the query results are returned according to the column "Consumer name", "Consumer address", "Jeweler name" and "Jeweler's address".

SELECT consumer name, consumer address, consumer city as city, The jeweler's name. Jeweler's address

From customer information A INNER JOIN B

On A. The city where the consumer is located =b the jeweler's city

After executing the above statement, the result is analyzed. Although the use of natural query can eliminate the duplicate rows in the query results, but from the query results of the above statement can be found that the query results are also formed by the Cartesian product.

3. External connection

Inner joins return rows when at least one row that belongs to two tables meets the join condition. The outer connection returns all matching navigation and a certain mismatch, depending on the type of connection being established. 3 types of external connections supported by SQL:

Left OUTER join: Returns all matching rows and returns all unmatched rows from the table to the left of the keyword join.

Right OUTER join: Returns all matching rows and returns all unmatched rows from the table to the right of the keyword join.

Full connection: Returns all matching rows and rows that do not match.

3.1 Left Outer connection

The query result set for the left outer join includes all the rows in the specified left table, not just the rows that match the join columns. If a row in the left table does not find a matching row in the right table, the relative position of the right table in the result set is null.

In the SELECT statement for the left OUTER join query, connect the two base tables with the OUTER join keyword.

For example, in the database "jewellery marketing System", the "Consumer information" and "Jeweler information" tables are checked for left-side connections on the condition of the same city's consumers and jewelers.

SELECT consumer name, consumer address, consumer city as city, jeweler name, jeweler's address

From customer information A left OUTER JOIN jeweler information B

On A. The city where the consumer is located =b the jeweler's city

After execution, the query's results contain three rows with null worth of data, although the three rows have no matching columns, but are still included in the query results because the information in the column "Consumer name" in the three rows is included in the left table. This means that when you make a LEFT outer join query, whether the rows in the left table will be able to find a matching row in the right table, the query results are displayed in that row, and the only difference from the other rows is that the position in the right table corresponds to the row with a null value instead.

3.2 Right Outer connection

The right outer join is a reverse connection of the left outer join, except that all rows of the specified right table are included in the socialize result set. If a row in the right table does not find a matching row in the left table, the relative position of the left table in the result set is null.

In the SELECET statement of the right outer join query, connect the two base tables with the OUTER join keyword.

For example, in the database "Jeweler Marketing System", the "Consumer information" and "Jeweler information" tables are connected right out of the box for consumers and jewelers in the same city.

SELECT consumer name, consumer address, consumer city as city, jeweler name, jeweler's address

From customer information A right OUTER JOIN jeweler information B

On A. The city where the consumer is located =b the jeweler's city

after execution, the query's results contain two rows containing null-worthy data, although the two rows have no matching columns, but are still included in the query results because the information in the "Jeweler name" column in the two rows is included in the right table. That is, when you make a right outer join query, regardless of whether the row in the right table can find a matching row in the left table, the query results will be displayed in that row, and the only difference from the other rows is that the position in the left table corresponds to the row in place of a null value.

3.3 Fully connected Connect

A full connection returns all rows in the left and right tables. When a row does not have a matching row in another table, the corresponding column value of the other table is null. If a row is right-matched between tables, the entire result set contains the data values of the base table.

In the SELECT statement for a fully connected query, connect the two base tables with the full OUTER join keyword.

For example, in the database "Jeweler Marketing System", the "Consumer information" and "Jeweler information" tables are fully connected to the consumers and jewelers in the same city.

SELECT consumer name, consumer address, consumer city as city, jeweler name, jeweler's address

From customer information A full OUTER JOIN jeweler information B

On A. The city where the consumer is located =b the jeweler's city

after execution, the query's results contain six rows with null worth of data, although the six rows have no matching columns, but are still included in the query results because the information in the "Jeweler name" column in the two rows is included in the right table. This means that when a fully connected query is made, whether the left table or the right table can find a matching row, it displays the row in the query result instead of a null value in the location where the match is not found.

4. Joint queries

If you have multiple different query results, but you do not want to concatenate them together, make the data. In this case, you can use the Union clause. A query that uses a UNION clause, called a union query, can combine the results of two or more queries into a single result set that contains all the row data in all query result sets in a union query. A union query differs from a join query on a column in two tables, which is a combination of rows from two tables, which matches the column data in two tables. The syntax format for federated queries is:

SELECT <select_list>

From <table_reference>

[WHERE <search_condition>]

{UNION [all]

SELECT <select_list>

From <table_reference>

[WHERE <search_condition>]}

[ORDER by <order_condition>]

When you make a union query, the UNION clause automatically deletes the duplicate rows, and the column headings of the query result are the column headings of the first query statement. Therefore, you must define a column header in the first query statement.

For example, from the "Customer information" table in the database "Jewelry Marketing System", the name, home address and telephone number of the consumer in the "City of the consumer" as "Beijing" are queried, and the type of addition is classified as "category" and the content is "consumer"; from the jeweler's information table, check out the jeweler's city "Beijing" The jeweler's information, and add a column that contains the contents of the "jeweler"; Finally, the results of the two queries are joined together.

SELECT consumer name as name, consumer address as home address, contact phone, ' consumer ' as category

From Customer information

where consumer city = ' Beijing '

UNION

SELECT Jeweler's name, jeweler's address, telephone, ' jeweler '

From Jewelers information

Where jewelers ' city = ' Beijing '

In a SELECT statement that uses union, if you want to sort the results of a union query, you must use the column name, column header, or column ordinal in the first query statement. And the sort clause is best used to specify the sort order by numbers, and if no numbers are used, the names of the columns in the subquery of the Union need to be the same, or you can use aliases to unify the column names.

In addition, the ORDER BY clause must be placed after the SELECT clause when ordering the results of a union query.

For example, from the Customer information table of the database sales management system, inquire about the customer's name, home address, and contact phone number that is not greater than 1005, and add the Type column "function" to the column, which is "customer"; from the "Salesman Information" table, check "salesman number" No more than 1005 of the salesman's name, home address and telephone number, and add a column, the content of the column is "clerk"; Finally, the two query results are joined together and arranged in ascending order of type "function".

SELECT Customer Name as name, customer address as home address, contact phone, ' customer ' as function

From Customer information

WHERE Customer number <= 1005

UNION

SELECT salesman name, home address, telephone, ' salesman '

From salesman information

WHERE salesman number <=1005

ORDER by function

Union ALL is another way to federate a table. The only difference between it and union is that it does not delete duplicate rows and does not automatically sort rows. When you make a union query on a table, you can use union ALL if you want to display duplicate rows in the query results. Here, no more one by one examples.

5. Cross-connect and self-connect

There are two more special connection queries in connection queries: cross-connect and self-connect. In a cross-join query result, one row for each of the two possible paired rows in two tables. A self-connection is a table connected to itself with a query.

5.1 Cross-Connect

A cross-join of two tables will generate all possible combinations of rows from both tables. When the cross join is not with a WHERE clause, the Cartesian product of all rows of the connected two tables is returned, that is, the number of rows returned to the result set is equal to the number of rows in the first table that match the query criteria multiplied by the number of rows in the second table that match the query criteria. When a cross-join has a WHERE clause, the Cartesian product of all rows connected to two tables is returned minus the number of all rows that are now omitted from the WHERE clause.

Cross-joins are very similar to the basic connection operations described earlier. The only difference is that, in the FROM clause, multiple table names are separated from each other by a cross join keyword, and the ON keyword cannot be used to qualify the join condition as an inner join or an outer join, but the join condition can be limited to a WHERE clause.

For example, in the database sales management system, cross-connect the commodity information table and the Vendor information table. Requests to return the product origin and the supplier's city are "Shanghai" or the idea "Nanjing" the related information.

SELECT Commodity information-product name, commodity information-origin, supplier information. Supplier Name

From product information Cross JOIN supplier information

Where commodity information. origin = supplier information. The city where the supplier is located

and (product information. origin = ' Shanghai ' OR commodity information. origin = ' Nanjing ')

ORDER by product information. Origin

5.2 Self-connect

Connections can be made not only between different tables, but also by connecting a table to itself, a connection that becomes self-connected, and the corresponding query is called a child join query. A child connection is an inner or outer connection to the table itself.

A connection operation for a child connection can use the alias method to implement a connection to the table itself. Essentially, this seed-body connection method is exactly the same as the join operation of two tables. Just name an alias for the table each time it is listed.

For example, the "Customer information" table in the database "Sales management system" to self-connect, query "customer address" the same customer information, and in the query results only return "customer address" as "Beijing Haidian District" or "Beijing Chaoyang" information.

SELECT A. Customer name, a. Contact number, A. Customer address, B. Customer name, b. Contact number

From customer information A INNER JOIN Customer Information B

On A. Customer address =b. Customer Address

WHERE A. Customer address = ' Haidian District, Beijing '

OR A. Customer address = ' Chaoyang District, Beijing '

Because this query involves a connection between the customer information table and itself, the Customer information table is displayed in two roles. To differentiate this role, you must provide two different aliases (A and B) in the FROM clause for the Customer information table, which are used to qualify the column names in the query. If you want to eliminate the rows that the customer matches, simply add a qualification to the statement above to change the statement to:

SELECT A. Customer name, a. Contact number, A. Customer address, B. Customer name, b. Contact number

From customer information A INNER JOIN Customer Information B

On A. Customer address =b. Customer Address

WHERE (A. Customer address = ' Haidian District, Beijing ')

OR A. Customer address = ' Chaoyang District, Beijing '

and A. Customer name <>b. Customer Name

The query results compare to the above query results and find that the rows that the customer matches themselves are eliminated in the query results.

6. Learning Summary

It was a bit of a headache when I was studying the contents of this chapter, and it was a little more. However, its connection is very close, learning is not very laborious, finally summed up the knowledge of learning.

In the connection learning of multiple data tables, the relation of the data table is very practical, and more knowledge is learned in practice.

SQL Application and Development: (v) connection of multiple data tables