Four types of connections for SQL-left outer, right outer, internal, fully connected

The join condition can be specified in the From or WHERE clause, and it is recommended that the join condition be specified in the FROM clause. The WHERE and having clauses can also contain search conditions to further filter the rows selected by the join condition.
Joins can be divided into the following categories:

1, INNER join (typical join operation, using a comparison operator like = or <>). Includes equality joins and natural joins.
Inner joins use comparison operators to match rows in two tables based on the values of the columns that are common to each table. For example, retrieve all lines of the students and courses table with the same student identification number.

2, outer joins . An outer join can be a left outer join, a right outer join, or a full outer join.
When you specify an outer join in the FROM clause, you can specify it by one of the following sets of keywords:

1) left JOIN or left OUTER join
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.

2) Right Join or right OUTER join
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.
3) Full join or full OUTER join
A full outer join returns all rows from the left and right tables. When a row does not have a matching row in another table, the selection list column for the other table contains a null value. If there are matching rows between the tables, the entire result set row contains the data values of the base table.

3. Cross Join
A cross join returns all the rows in the left table, with each row in the left table combined with all the rows in the right table. Cross joins are also called Cartesian product.

The table or view in the FROM clause can be specified in any order by an inner join or a full outer join, but the order of the table or view is important when you specify a table or view with a left or right outer join. For more information about using left or right outward joins to arrange tables, see Using outer joins.

Example:

-------------------------------------------------
A table ID name B table ID job parent_id
1 Sheets 3 1 23 1
2 Lee 42 34 2
3 Wang Wu 3 34 4
Relationship between a.ID and parent_id

--------------------------------------------------
1) Internal connection
Select a.*,b.* from a inner join B on a.id=b.parent_id
The result is
1 Sheets 3 1 23 1
2 Lee 42 34 2

2) Left Connection
Select a.*,b.* from a LEFT join B on a.id=b.parent_id
The result is
1 Sheets 3 1 23 1
2 Lee 42 34 2
3 Wang Wu Null

 3)   right connection   
  Select   a.*,b.*   from   a   right   join   B & nbsp   on   a.id=b.parent_id       
  results     
  1   3                   1         1   
  2   John Doe                  2         2   < br>  null                       3       &NBS P 4   
    
 4)   full connection    
  Select   a.*,b.*   From   A   full   joins   B     on   a.id=b.parent_id   

The result is
1 Sheets 3 1 23 1
2 Lee 42 34 2
Null 3 34 4
3 Wang Wu Null

The first part, connection query

One, inner connection

The INNER JOIN query operation lists the data rows that match the join criteria, and it uses comparison operators to compare the column values of the concatenated columns. There are three types of internal connections:

1. Equivalent connection: Use the equals sign (=) operator in the join condition to compare the column values of the joined columns, and the query results list all the columns in the joined table, including the repeating columns.

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.

Second, outer connection

Returning to the query results collection includes not only rows that meet the join criteria, but also all rows of data in the left table (when left outer joins), right table (when right outer joins), or two edge tables (full outer joins).

Three, cross-linking

The cross join does not take a WHERE clause, it returns the Cartesian product of all data rows of the two connected tables, and the number of rows returned to the result set equals the number of data rows in the first table that meet the query criteria multiplied by the number of data rows in the second table that meet the query criteria. example, there are 6 types of books in the titles table, and 8 publishers in the publishers table, the number of records retrieved by the following cross-connection will be equal to the 6*8=48 row.

The second part, the example description (instance reference bottom link)

One, inner connection

SELECT *

From [book] as b,[student] as s

where B.studentid=s.studentid

Equivalent to the following (or not the keyword inner, which is the system default)

SELECT *

From [book] as b inner join [Student] as S

On B.studentid=s.studentid

Execution process

Corresponds to the right connection of the inner connection. To the right of the from [book] inner join [Student] equation is the base, which is the s of the Student table (the equation right table, s table). StudentID is the benchmark, traversing the Book table (the equation left table, Book table) matches the B.studentid, and then stitching back. The results contain duplicate columns, B.studentid and S.studentid.

Description

This is not related to where B.studentid=s.studentid or S.studentid=b.studentid locations. It merely represents the satisfaction of the condition, and does not determine who is the benchmark. The following outer joins, which cross-connect the same operation.

Second, outer connection

1. Left outer connection

Code

SELECT *

From [book] as-B left join [Student] as S

On B.studentid=s.studentid

Execution process

That is, based on the book table from the LEFT join [Student] of the [book], that is, B of the Book table (table B). StudentID as the benchmark. Traverse the B that matches it in the Student table (s table). StudentID. If B. StudentID contains s.studentid matches, then stitching, and then traversing the student table of the next S.studentid, when the query is complete to enter the next B.studentid. If B. StudentID there are no corresponding S.studentid matches, the items in the left table are displayed, and the items on the right table are displayed as null.

2. Right outer connection

Code

SELECT *

From [book] as-B right join [Student] as S

On B.studentid=s.studentid

Results

Execution process

That is, the Student table of the from [book] right join [Student] is the base, which is the s of the Student table (s table). StudentID as the benchmark. Iterate through the matching s in the Book table (table B). StudentID. If S. StudentID contains b.studentid matches, then stitching, and then traversing the Book table of the next B.studentid, when the query is complete into the next s.studentid. If S. StudentID there is no corresponding B.studentid match, the item in the right table is displayed, and the entry for the stitching left table is displayed as null.

3. Full external connection

Code

SELECT *

From [book] as-B full outer join [Student] as S

On B.studentid=s.studentid

Results

Execution process

That is, a left outer connection is made to the Book table in the From [book] full outer join [Student], and then to the right outer join in the Student table.

Three, cross-linking

Code

SELECT *

From [book] as-b cross Join [Student] as a

Order by B.bookid

Results

Execution process

That is, according to the order of the Id, the right table to Join the unconditional splicing over. This is done sequentially so that the record is a Cartesian product of two tables.

Summarize:

Format:

SELECT *

From [book] as-B left join [Student] as S

On B.studentid=s.studentid

Examples of projects:

$sql = "Select M.id,m.name,p.id,p.sysset,p.readerset,p.bookset,p.borrowback,p.sysquery from Tb_manager as M left join TB _purview as P on m.id=p.id where Name= ' $_session[admin_name] ' ";

Note that field is the query field of multiple tables is written together, and the use of the Alias method is particularly useful, there can be more than one where filter statement.

Reference: http://www.cnblogs.com/LeoTerry/archive/2010/03/26/1696988.html

Four types of connections for SQL-left outer, right outer, internal, fully connected