Detailed explanation of the implementation method of database multi-table connection query

Detailed explanation of the implementation method of database multi-table connection query

Multiple table queries can be implemented using the join operator. Connection is the main feature of the relational database model and is also a mark that distinguishes it from other types of database management systems.

In a relational database management system, the relationship between the data does not need to be determined when the table is created, and all the information of an entity is often stored in one table. When retrieving data, information about different entities stored in multiple tables is queried through a join operation. The join operation gives users great flexibility, and they can add new data types at any time. Create new tables for different entities and query them later through joins.

A join can be established in either the FROM clause or the WHERE clause of a SELECT statement. It is helpful to specify the join in the FROM clause to distinguish the join operation from the search condition in the WHERE clause. Therefore, this method is recommended in Transact-SQL.

The join syntax of the FROM clause defined by the SQL-92 standard is:

FROM join_table join_type join_table 

[ON (join_condition)] 

The join_table indicates the name of the table involved in the join operation. The join can be performed on the same table or multiple tables. The join on the same table is also called a self-join.

join_type indicates the join type, which can be divided into three types: inner join, outer join and cross join. INNER JOIN uses comparison operators to compare certain columns of data between tables and lists the data rows in these tables that match the connection conditions. Depending on the comparison method used, inner joins are divided into three types: equal joins, natural joins, and unequal joins.

There are three types of outer joins: left outer join (LEFT OUTER JOIN or LEFT JOIN), right outer join (RIGHT OUTER JOIN or RIGHT JOIN) and full outer join (FULL OUTER JOIN or FULL JOIN). Unlike inner joins, outer joins do not only list rows that match the join conditions, but also list all data rows that meet the search conditions in the left table (left outer join), the right table (right outer join), or both tables (full outer join).

A cross join (CROSS JOIN) has no WHERE clause. It returns the Cartesian product of all rows in the joined table. The number of rows in the result set is equal to the number of rows in the first table that meet the query conditions multiplied by the number of rows in the second table that meet the query conditions.

The ON (join_condition) clause in the join operation indicates the join condition, which is composed of columns in the joined tables and comparison operators, logical operators, etc.
No matter what kind of connection, you cannot directly connect to columns of text, ntext, and image data types, but you can indirectly connect these three columns.

(I) Inner Join

The inner join query operation lists the data rows that match the join condition, and it uses comparison operators to compare the column values ​​of the joined columns. There are three types of inner joins:

1. Equi-join: Use the equal sign (=) operator in the join condition to compare the column values ​​of the joined columns. The query results list all columns in the joined tables, including duplicate columns.

2. Unequal join: Use comparison operators other than the equality operator in the join condition to compare the column values ​​of the columns being joined. These operators include >, >=, <=, <, !>, !<, and <>.

3. Natural connection: The equal (=) operator is used in the connection condition to compare the column values ​​of the connected columns, but it uses the select list to indicate the columns included in the query result set and deletes duplicate columns in the connected table.

For example, the following uses an equijoin to list the authors and publishers in the same city in the authors and publishers tables:

SELECT * 

FROM authors AS a INNER JOIN publishers AS p 

ON a.city=p.city 

Another example is to use a natural join and remove duplicate columns (city and state) from the authors and publishers tables in the select list:

SELECT a.*,p.pub_id,p.pub_name,p.country 

FROM authors AS a INNER JOIN publishers AS p 

ON a.city=p.city

(ii) External connection

In the case of an inner join, only the rows that meet the query conditions (WHERE search conditions or HAVING conditions) and the connection conditions are returned in the query result set. When an outer join is used, the query result set returned includes not only the rows that meet the join conditions, but also all the data rows in the left table (left outer join), the right table (right outer join), or the two edge tables (full outer join).

An outer join can be a left outer join, a right outer join, or a full outer join.

When an outer join is specified in the FROM clause, it can be specified by one of the following sets of keywords: LEFT JOIN or LEFT OUTER JOIN; RIGHT JOIN or RIGHT OUTER JOIN; FULL JOIN or FULL OUTER JOIN.

(1) Left outer join: The result set of a left outer join includes all rows of the left table specified in the LEFT OUTER clause, not just the rows where the join columns match. If a row in the left table has no matching row in the right table, all select-list columns of the right table have null values ​​in the associated result set row.

(2) Right outer join: A right outer join is the reverse join of a left outer join. Will return all rows of the right table. If a row in the right table has no matching row in the left table, a null value is returned for the left table.

(3) Full outer join: A full outer join returns all rows from both the left and right tables. When a row has no matching row in the other table, the select list columns of the other table contain NULL values. If there are matching rows between the tables, the entire result set row contains the data values ​​from the base table.

An inner join returns rows only if at least one row from both tables meets the join condition. Inner joins eliminate rows that do not match any rows in the other table. An outer join returns all rows from at least one of the tables or views mentioned in the FROM clause that satisfy any of the WHERE or HAVING search conditions. All rows of the left table referenced by a left outer join, and all rows of the right table referenced by a right outer join are retrieved. All rows from both tables in a full outer join are returned.

As shown below, a left outer join is used to connect the forum content and author information:

SELECT a.*,b.* FROM luntan LEFT JOIN usertable as b 

ON a.username=b.username 

The following uses a full outer join to join all authors in the city table with all authors in the user table, along with their cities:

SELECT a.*,b.* 

FROM city as a FULL OUTER JOIN user as b 

ON a.username=b.username

(III) Cross-connection

A cross join without a WHERE clause returns the Cartesian product of all rows in the two tables being joined. The number of rows returned in the result set is equal to the number of rows in the first table that meet the query conditions multiplied by the number of rows in the second table that meet the query conditions.

For example, there are 6 categories of books in the titles table, and there are 8 publishing houses in the publishers table. The number of records retrieved by the following cross join will be equal to 6*8=48 rows.

SELECT type,pub_name 

FROM titles CROSS JOIN publishers 

ORDER BY type

If you have any questions, please leave a message or come to our community to discuss. I hope this article can help you. Thank you for your support!