1) SQL Server connection
Original method: SELECT NAME from A, B Where A.Id = B.AID
Recommended now: SELECT NAME FROM A inner Join B on A.ID = B.AID
Separate the connection operation with the search condition in the WHERE clause. So, this method is recommended in Transact-SQL.
The connection syntomitization of the FROM clause defined by the SQL-92 standard is:
From join_table join_type join_table
[On (join_condition)]
Where join_table points to the table name of the connection operation, the connection can be operated on the same table, or you can operate multiple tables, the same
The connection of a table operation is also called self-connection.
Join_type indicates that the connection type can be divided into three: internal connections, external connections, and cross-connect. Inner Join usage
The comparison operation of the interval (some) column data is listed in these tables, and the data lines matching the connection conditions are listed. According to the use
The comparison method is different, and the inner connection is divided into equivalent connection, natural connection, and not waiting for three kinds.
The outer connection is divided into left-way connections (LEFT OUTER JOIN or LEFT JOIN), right-external connections (Right Outer Join or Right Join)
Three of full outer connect (Full Outer Join or Full Join). Unlike internal connections, external connections are not only listed with the connection conditions.
The line, but lists the left table (when connected to the left), the right table (right-external connection) or two tables (all-end connection) all matching conditions
Data line.
Cross Join does not have a WHERE clause, it returns a descint of all data lines in the connection table, and its result collection
The number of data lines is equal to the number of data rows that meet the query conditions in the first table multiplied by the number of data lines that meet the query conditions in the second table.
The on (join_condition) clause in the connection operation indicates the connection condition, which is logical by the columns and comparison operators in the connected table.
Operator, etc.
No matter which connection can be directly connected to the text, ntext, and the image data type column, but you can indirectly over these columns.
connection. E.g:
SELECT P1.PUB_ID, P2.PUB_ID, P1.PR_INFO
From pub_info as p1 inner join pub_info as p2
On Datalength (p1.pr_info) = DATALENGTH (P2.PR_INFO)
(1) internal connection
Internal connection query operations list the data lines that match the connection condition, which uses the comparison operator to compare the column values of the columns. The internal connection is divided into three types:
1. Equal Connection: Use the column value of the connected column in the connection condition to compare the column of the column, and the query results are listed
All columns in the table include repeated columns.
2, inequality: Use the column values of the columns connected to the listed columns that are connected to other comparison operators other than operators. These ones
Operators include>,> =, <=,!.
3, natural connection: use the column value of the connected column using equal to (=) operator in connection conditions, but it uses the selection list to point out the query
The columns included in the result collection and delete the repeat columns in the connection table.
For example, the following uses the equivalent connection to list authors and publishers in the same city in the Publishers table:
SELECT *
From authors as a inner join publishers as pon a.city = p.city
For example, use natural connections, remove the authors and publishers tables (City and State) in the selection list:
Select a. *, Pub_id, pub_name, p.country
From authors as a inner join public publishers asp
ON A.CITY = P.city
(2) external connection
In-connection, returning only the query criteria (WHERE search criteria or Having condition) and connection conditions
Row. When using external connections, it returns to the query result collection not only contains rows that meet the connection conditions, but also the left table (left outside)
When connected), all data rows in the right table (when connected to the right) or two edge tables (all external connections).
The forum content and author information are connected to the left outer connection:
Select a. *, B. * From Luntan Left Join UserTable As B
ON A.USERNAME = B.USERNAME
The following uses all the authors in the city table and all the authors in the USER table, as well as their city:
SELECT A. *, B. *
From City As a Full Outer Join User AS B
ON A.USERNAME = B.USERNAME
(3) cross-connect
Cross connections do not bring a WHERE clause, it returns the two tables of the two tables that are connected to the Cartesian, return to the number in the result collection
According to the number of data lines that comply with the query conditions in the first table, multiplied by the number of query conditions in the second table complies with the number of query conditions.
In the case, there are 6 types of books in the Titles table, and 8 publishers in the Publishers table, the following cross-connect retrieved record number will wait
On 6 * 8 = 48 lines.
SELECT TYPE, PUB_NAME
From titles cross Join Publishers
ORDER by Type
2) Oracle connection
In Oracle PL-SQL, the left connection and right connections are implemented as follows:
SELECT
EMP_NAME, DEPT_NAME FORM EMPLOYEEEE, Department
WHERE
EMPLOYE.EMP_DEPTID (
)
=
Department.deptid
This SQL text uses the right connection, ie the other side of the "( )" location is the direction of the connection, and all records on the right side of the right connection will be displayed, no matter whether it is matched on the left side, That is to say, in the above example, there will be no employees in a certain department, and the names of this department will appear in the query results. on the contrary:
SELECT
EMP_NAME, DEPT_NAME FORM EMPLOYEEEE, Department
WHERE
EMPLOYEE.EMP_DEPTID
=
Department.deptid
)
It is the left connection, no matter whether this employee has a department number that can be matched in the Department of the Department, this employee's record will be displayed.
SELECT
EMP_NAME, DEPT_NAME FORM EMPLOYEEEE, Department
WHERE
EMPLOYE.EMP_DEPTID (
)
=
Department.deptid
This SQL text uses the right connection, ie the other side of the "( )" location is the direction of the connection, and all records on the right side of the right connection will be displayed, no matter whether it is matched on the left side, That is to say, in the above example, there will be no employees in a certain department, and the names of this department will appear in the query results. on the contrary:
SELECT
EMP_NAME, DEPT_NAME FORM EMPLOYEEEE, Department
WHERE
EMPLOYEE.EMP_DEPTID
=
Department.deptid
)
It is the left connection, no matter whether this employee has a department number that can be matched in the Department of the Department, this employee's record will be displayed.
