Multi-table join query in SQL

1. Overview

In the relational database management system, the relationship between data does not have to be determined when a table is created, and all information of an object is often stored in
A table. When retrieving data, you can use the join operation to query information about different entities in multiple tables. Connection operation
With great flexibility, they can add new data types at any time. Create a new table for different entities and then connect to the table.
Query.
The connection can be established in the from clause or where clause of the SELECT statement. However, "it seems" to indicate in the from clause that the connection operation can be distinguished from the search conditions in the WHERE clause. Therefore, this method is recommended in transact-SQL.

The connection syntax format for the from clause defined by the SQL-92 standard is:
From join_table join_type join_table
[On (join_condition)]
Join_table indicates the name of the table involved in the join operation. The join operation can be performed on the same table or on multiple tables. The Join Operation on the same table is also called a self-Join Operation.

Join_type indicates the connection type, which can be divided into three types: internal connection, external connection, and cross connection.

Inner join uses a comparison operator to compare data in some columns of a table, and lists the data rows in these tables that match the connection conditions. According to the comparison method used, internal connections are classified into equivalent connections, natural connections, and unequal connections.

Outer Join is divided into left Outer Join or left join, right Outer Join or right join, and full outer join.
Outer Join or full join. Different from internal connections, external connections not only list the rows that match the connection conditions, but also list the left table (when the left Outer Join is performed) and the right table (when the right outer join is performed) or all data rows that meet the search criteria in two tables (when the table is fully connected.

Cross join does not have a where clause. It returns the Cartesian product of all data rows in the join table, the number of rows in the result set is equal to the number of rows that meet the query conditions in the first table multiplied by the number of rows that meet the query conditions in the second table. The ON (join_condition) clause in the Join Operation specifies the join condition, which consists of columns, comparison operators, and logical operators in the connected table.

2. Sample Test 2.1 preparations Create two test tables table_user and table_dep (including data ): Table_user: Table_dep: Inner join/join) If no join condition is specified for an inner join, the result of the cross join is the same as that of the Cartesian product. However, unlike the Cartesian product, the data table is not as complex as the Cartesian product, the internal join efficiency is higher than that of cartesian products. However, when using inner join, you must specify the connection conditions. In SQL Server2000, inner join is equivalent to join. -- Inner join Select * From table_user A, table_dep B Where a. dep_id = B. dep_id -- Note: The above method is SQL Server's support for relational calculus. Select * From table_user a inner join table_dep B On a. dep_id = B. dep_id -- Note: This is SQL Server's support for relational algebra (similar to the following) -- Join (this is equivalent to inner join in SQL Server) Select * From table_user a join table_dep B On a. dep_id = B. dep_id The execution results of the three statements are the same: 2.2 Outer Join ([outer] Join) Left Outer Join: The result set includes all rows in 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 selection list columns in the right table in the row of the associated result set are null ). The right outer join is a reverse join of the left Outer Join. All rows in the right table are returned. If a row in the right table does not match a row in the left table, a null value is returned for the left table. Full (full) External join returns all rows in the left and right tables. If a row does not match a row in another table, the selection list column of the other table contains a null value. If there are matched rows between tables, the entire result set row contains the data value of the base table. -- About outer join [outer] Join Note: outer is optional -- Left Outer Join Select * From table_user a left Outer Join table_dep B On a. dep_id = B. dep_id Result: Left Outer Join -- Right Outer Join Select * From table_user a right Outer Join table_dep B On a. dep_id = B. dep_id Result: Outer right join -- Full outer join Select * From table_user a full outer join table_dep B On a. dep_id = B. dep_id Result: Complete (full) External Connection 2.3 cross join) Cross join is a concrete embodiment of Cartesian Product in SQL. -- Relationship algebra angle (cross join) Select * From table_user a cross join table_dep B Result: The Cartesian product of the two tables. (The data table is too large to be added !) In addition, the keyword of the Condition Clause of cross join is where, rather than the on keyword. Select * From table_user a cross join table_dep B Where a. dep_id = B. dep_id Cross join (complete) Concept: without the cross join of the where clause, the number of rows in the first table of the Cartesian product involved in the join is multiplied by the number of rows in the second table, which is equal to the Cartesian Product and the size of the result set. Cross join: cross join (without the condition where, if the returned or displayed Number of matched rows) SQL Syntax: Select * From Table1 cross join Table2 If a condition (where) exists) Select * From Table1 cross join Table2 where table1. condition column name = table2. condition column name Equivalent Select * From Table1, Table2 (without where)