Detailed explanation of the implementation method of multi table join query in database

Detailed explanation of the implementation method of multi-table join query in database

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

In a relational database management system, it is not necessary to determine the relationship between data when a table is established, and all the information of an entity is often stored in a table. When retrieving data, the information of different entities stored in multiple tables is queried through join operation. Connection operations give users great flexibility, and they can add new data types at any time. Create new tables for different entities, and then query through joins.

Connections can be established in either the FROM clause or the WHERE clause of an SELECT statement, and plausible pointing out a connection in the FROM clause helps to distinguish the connection operation from the search criteria in the WHERE clause. Therefore, this method is recommended in Transact-SQL.

The join syntax format for the FROM clause as defined by the SQL-92 standard is:

FROM join_table join_type join_table 

[ON (join_condition)] 

Among them, join_table indicates the names of the tables participating in the join operation. Joins can operate on the same table or multiple tables. Joins operating on the same table are also called self-joins.

join_type indicates the types of connections, which can be divided into three types: inner connection, outer connection and cross connection. An internal join (INNER JOIN) uses a comparison operator to compare column (s) of data between tables and lists the rows of data in those tables that match the join condition. According to different comparison methods, internal connection can be divided into three types: equivalent connection, natural connection and unequal connection.

There are three types of external connections: left external connection (LEFT OUTER JOIN or LEFT JOIN), right external connection (RIGHT OUTER JOIN or RIGHT JOIN) and full external connection (FULL OUTER JOIN or FULL JOIN). Unlike inner joins, outer joins list not only the rows that match the join criteria, but all the rows that match the search criteria in the left table (when left outer joins), right table (when right outer joins), or both tables (when full outer joins).

Cross Join (CROSS JOIN) does not have an WHERE clause, which returns the Cartesian product of all data rows in the join table, and the number of data rows in the result set is equal to the number of data rows in the first table that match the query criteria multiplied by the number of data rows in the second table that match the query criteria.

The ON (join_condition) clause in the join operation indicates the join condition, which consists of columns in the joined table, comparison operators, logic operators, and so on.
text, ntext, and image data type columns cannot be directly joined either way, but these three columns can be indirectly joined.

(1) Internal connection

The inner join query operation lists the rows of data that match the join criteria and compares the column values of the joined columns using a comparison operator. There are three types of internal connections:

1. Equivalent join: Use the equal sign (=) operator in the join condition to compare the column values of the joined columns, and the query result lists all columns in the joined table, including duplicate columns.

2. Unequal join: Compare the column values of the joined columns using comparison operators other than the Equal operator in the join condition. These operators include > , > =, < =, < ,! > ,! < And < > .

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

For example, the authors and publishers in the same city in the authors and publishers tables are listed below using equivalent joins:

SELECT * 

FROM authors AS a INNER JOIN publishers AS p 

ON a.city=p.city 

Another example is to remove duplicate columns (city and state) from the authors and publishers tables in the selection list using natural joins:

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

FROM authors AS a INNER JOIN publishers AS p 

ON a.city=p.city

(2) External connection

For internal join, only rows that match the query criteria (WHERE search criteria or HAVING criteria) and join criteria are returned in the query result set. When outer join is used, it returns to the query result set not only the rows that meet the join conditions, but also all the data rows in the left table (when left outer join), the right table (when right outer join) or two edge join tables (all outer join).

The outer join can be a left outward join, a right outward join, or a complete outer join.

When specifying an outer join 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 outward join: The result set of the left outward join includes all the rows of the left table specified in the LEFT OUTER clause, not just the rows matched by the join column. If a row in the left table does not match a row in the right table, all the select list columns in the right table are null in the associated result set row.

(2) Right outward connection: Right outward connection is the reverse connection of left outward connection. All rows of the right table are returned. If a row in the right table does not match a row in the left table, a null value will be returned for the left table.

(3) Full outer join: A full outer join returns all rows in the left and right tables. When a row does not match a row in another table, the select list column of the other table contains null values. If there are matching rows between tables, the entire result set row contains the data values of the base table.

An inner join returns a row only if at least one row belonging to both tables meets the join conditions. An inner join eliminates rows that do not match any rows in another table. The outer join returns all rows of at least one table or view mentioned in the FROM clause, as long as they match any WHERE or HAVING search criteria. All rows of the left table referenced by the left outward join and all rows of the right table referenced by the right outward join are retrieved. All rows of both tables in the full outer join will be returned.

Use the left outer link below to connect the forum content with the author information:

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

ON a.username=b.username 

All the authors in the city table and all the authors in the user table, and their cities, are joined below using a full outer join:

SELECT a.*,b.* 

FROM city as a FULL OUTER JOIN user as b 

ON a.username=b.username

(3) Cross Connection

Cross-join does not have an WHERE clause, which returns the Cartesian product of all the rows of the two tables being joined. 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.

For example, if there are six categories of books in the titles table and eight publishers 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 exchange and discuss in this community. I hope this article can help everyone. Thank you for your support to this site!