Table Join Query

You can implement multiple table queries by using the Join operator. Connection is the main feature of relational database model, and it is also a sign that distinguishes it from other types of database management system.

In the relational database management system, the relationship between the data is not determined, and all the information of an entity is often stored in a single table. When retrieving data, the connection operation queries the information for different entities that reside in multiple tables. The connection operation gives the user a lot of flexibility and they can add new data types at any time. Create a new table for different entities, and then query through the connection.

A connection can be established in the FROM clause or a WHERE clause in a SELECT statement, and it is plausible to distinguish the join operation from the search condition in the WHERE clause when the connection is indicated in the FROM clause. Therefore, it is recommended to use this method in Transact-SQL.

(no connection can be directly connected to the text, ntext, and image data type columns, but these three kinds of columns are indirectly connected.) ）

The connection query is actually querying the data through the correlation of the common columns between the tables, which is the most important characteristic of the relational database query.
Select table 1. field name 1, table 2. Field Name 2, ...
From table 1, table 2
where join condition
The connection syntax format for the FROM clause defined by the SQL-92 standard is:
From table name join_type table name [on (join condition)]
The On (join condition) clause in the JOIN operation indicates the join condition, which is constructed by the columns and comparison operators, logical operators, and so on in the connected table.
Yes.
Join_type Connection Query Classification:
1. Self-connect query, connect to the same table (can be understood as an inner join of two different tables, sometimes equivalent to a nested query)
2. Internal connection query,< is divided into: natural connection, equivalent connection, non-equivalent connection three kinds of > (INNER JOIN)
3. Outer connection Query,< is divided into: Left outer connection, right outer connection, full outer connection three kinds of >
4. Cross-connect query, also make unconditional query.
5. Joint queries

A Self-connect query:

A table itself establishes a connection with itself called self-connection or self-connection.
Self-linking is like two separate tables, where one row of a table can be connected to another row in the same table.
Cases:
The results of the "101" course are more than the "9505201" Student Records,
and ranked by grades from high to low.
Select x.* from Sclass x,sclass y
where x.cno= ' 101 ' and X.degree>y.degree and y.sno= ' 9505201 ' and y.cno= ' 101 '
ORDER BY X.degree Desc

Two. Internal connection query

An inner join (INNER join) uses comparison operators to perform comparison operations on some (some) column data between tables, and lists the data rows in those tables that match the join criteria. According to the comparison method used, the inner connection is divided into equivalent connection, natural connection and unequal connection three kinds.

1. Equivalent connection:

Use the equals sign (=) operator in a join condition to compare the column values of the connected columns
The so-called equivalent connection, refers to the table through the "equals" relationship between the connection, resulting in a temporary table,
The temporary table is then processed to produce the final result. all columns in the connected table are listed in their query results .
include the repeating column .

SELECT *
From authors as a INNER JOIN publishers as P
On a.city=p.city

We can do this in two ways, both of which are equivalent
One is: SELECT e.employeeid,e.employeename,d.deptname from Employeetb as e,depttb as D WHERE E.deptid=d.deptid (connection with "=" sign)
The other is: SELECT e.employeeid,e.employeename,d.deptname from Employeetb as e INNER joins DEPTTB as D on E.deptid=d.deptid (using Keyword connection)

2, unequal connections: Use a comparison operator other than the equals operator in the join condition to compare the column values of the connected columns. These operators include >, >=, <=, <,!>,!<, and <>.

3. Natural connection:

Use the Equals (=) operator in the join condition to compare the column values of the connected column, but it uses the selection list to indicate which columns are included in the query result collection and to delete the duplicate columns in the Join table.
Eliminating duplicate columns in an equivalent connection is a natural connection. (State,city is present in two tables)

SELECT A.*,p.pub_id,p.pub_name,p.country
From authors as a INNER JOIN publishers as P
On a.city=p.city

Three-off connection query (left outer connection, right outer join, full outer join)

In the query results collection, only the rows that meet the query criteria (WHERE search condition or having condition) and join conditions are returned. In the case of an outer join, it returns to the query result collection not only the rows that meet the join criteria, but also all data rows in the left table (when left outer joins), the right table (when the right outer joins), or two edge tables (full outer joins).

The result set of the left outer join includes all rows of the left table specified in the OUTER clause, not just the rows that match the joined columns. If a row in the left table does not have a matching row in the right table, all select list columns in the right table in the associated result set row are null values

A right outer join is a reverse join of a left outer join. All rows of the right table will be returned. If a row in the right table does not have a matching row in the left table, a null value will be returned for left table.

The number of records connected to a left join B is the same as the number of records in Table A
A RIGHT join B is connected to the number of records and the number of records in table B is wrong with this statement, only if Table A and table B are a pair of one o'clock.

First we make two tables: the Employee Information table and the departmental information table, where the table is built only to tell the concept of the connection, so the fields are very simple
EMPLOYEETB (Employee information Sheet):

EmployeeID EmployeeName DeptID
0001 Sheets 301
0002 Li 401
0003 Wang 502
0004 Zhao 602
0005 Zheng Seven NULL

DEPTTB (Department information Sheet)
DeptID Deptname
01 Technical Department
02 Marketing Department
03 Engineering Department

1 left Outer junction (Ieft OUTER join or RIGHT join)
But in some cases, we need to know all the employees ' information, even if he doesn't belong to any department. So we can take the outer join, here for the left outer join, that is, the table of the left table in the connection, regardless of whether it is possible to find a matching item in the right table, to retrieve, if there is no matching item, then the field value in the right table is null (empty), in this case the employee does not belong to any department
The search statements are:
SELECT E.employeeid,e.employeename,d.deptname from Employeetb as E left OUTER joins DEPTTB as D on E.deptid=d.deptid
The results of the search are:

EmployeeID EmployeeName Deptname
0001 Three Technical Department
0002 Li Si Technology dept.
0003 Harry Marketing Department
0004 Zhao Six Marketing department
0005 Zheng Seven NULL

But here, the engineering department will not be retrieved, because, Deptname is in the table on the right side of the connection, "Engineering" does not have any records in the left table, so it will not be retrieved. The focus here is on the "left table in the connection"


2. Right outer connection (Starboard OUTER join)
Sometimes, we need to know all the department's information, even if it doesn't have any employees. In our query, the Department table is on the right side of the connection, if we want to know all the record information in the table on the right, then we can take the right outer join, if this record cannot find a match in the table on the left, the corresponding field (EMPLOYEEID,EMPLOYEENAME) is null
The search statements are:
SELECT E.employeeid,e.employeename,d.deptname from Employeetb as E right OUTER joins DEPTTB as D on E.deptid=d.deptid
The results of the search are:

EmployeeID EmployeeName Deptname
0001 Three Technical Department
0002 Li Si Technology dept.
0003 Harry Marketing Department
0004 Zhao Six Marketing department
NULL NULL Engineering Department

But here, Zheng Seven is not to be retrieved, because it can not find a match in the right table, here is concerned about "the right table in the connection"

3. Fully external connection (full OUTER join or complete join)
What if we want to know all the records? Whether employees have departments or departments, there are no employees, we need to search. You can use a full outer join here. Focus on the two parts of the connection. If there is no department, department is empty, no employees, employee information is empty.
The search statements are:
SELECT E.employeeid,e.employeename,d.deptname from Employeetb as e full OUTER joins DEPTTB as D on E.deptid=d.deptid
The results of the search are:

EmployeeID EmployeeName Deptname
0001 Three Technical Department
0002 Li Si Technology dept.
0003 Harry Marketing Department
0004 Zhao Six Marketing department
0005 Zheng Seven NULL
NULL NULL Engineering Department

Four Cross Join

The cross join does not take a WHERE clause, it returns the Cartesian product of all data rows of the connected two tables, returning to the number in the result set
The number of rows is equal to the number of rows in the first table that meet the query criteria multiplied by the data rows in the second table that meet the query criteria.
example, there are 6 categories of books in the titles table, while the publishers table has 8 publishers, the number of records retrieved by the following cross-connect will be
On the 6*8=48 line.

SELECT Type,pub_name

From the titles cross JOIN publishers

ORDER by Type

Table Join Query