SQL left Outer Join, right Outer Join, full join, internal join

The connection conditions can be specified in the FROM or WHERE clause. We recommend that you specify the connection conditions in the FROM clause. The WHERE and HAVING clauses can also contain search conditions to further filter the rows selected by the connection conditions.

Connections can be divided into the following types:

Internal Connection.(Typical join operations use comparison operators such as = or <> ). IncludingEqual connection and natural connection.
The internal join uses the comparison operator to match rows in two tables based on the values of the columns in each table. For example, retrieve all rows with the same student ID in the students and courses tables.

External Connection. External connections can be left external connections, right external connections, or complete external connections..
When an external connection is specified in the FROM clause, it can be specified by one of the following sets of keywords:
Left join or left outer join.
The result set of the left outer Join 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 selection list columns in the right table in the row of the associated result set are null.
Right join or right outer join.
The right outer connection is the reverse connection of the left outer connection. 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 join or full outer join.
The Complete External Connection 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.

Cross join.Returns all rows in the left table. Each row in the left table is combined with all rows in the right table. Cross join is also called Cartesian product.

For example, the following inner connection searches the author of a publisher who lives in the same state and city:

USE pubs
SELECT a. au_fname, a. au_lname, p. pub_name
FROM authors AS a inner join publishers AS p
ON a. city = p. city
AND a. state = p. state
Order by a. au_lname ASC, a. au_fname ASC

Tables or views in the FROM clause can be specified in any order through internal connections or complete external connections. However, when you connect to a specified table or view FROM the left or right, the order of tables or views is very important. For more information about using Left or Right outer join to arrange tables, see using outer join.

Example:
Table a id name table B id job parent_id
1 piece 3 1 23 1
2 Li Si 2 34 2
3 Wang Wu 3 34 4

A. id exists with parent_id Link

Internal Connection
Select a. *, B. * from a inner join B on a. id = B. parent_id

The result is
1 piece 3 1 23 1
2 Li Si 2 34 2

Left join
Select a. *, B. * from a left join B on a. id = B. parent_id

The result is
1 piece 3 1 23 1
2 Li Si 2 34 2
3 Wang Wu null

Right join
Select a. *, B. * from a right join B on a. id = B. parent_id

The result is
1 piece 3 1 23 1
2 Li Si 2 34 2
Null 3 34 4

Full connection
Select a. *, B. * from a full join B on a. id = B. parent_id

The result is
1 piece 3 1 23 1
2 Li Si 2 34 2
Null 3 34 4
3 Wang Wu null

SQL code

DECLARE @ ta table (ida int, va varchar (10) DECLARE @ tb table (idb int, vb varchar (10) Insert into @ TASELECT1, 'A' UNION SELECT2, 'Bc' UNION SELECT3, 'Ccc 'insert into @ TBSELECT1, '2' UNION SELECT3, '58 'UNION SELECT4, '67' -- simple syntax for the inner join: select. IDA,. VA, B. IDB, B. vb from @ ta a, @ tb bwhere. IDA = B. IDB -- Inner join select. IDA,. VA, B. IDB, B. vb from @ ta a inner join @ tb bon. IDA = B. idbselect. IDA,. VA, B. IDB, B. vb from @ ta a join @ tb bon. IDA = B. IDB -- left Outer Join select. IDA,. VA, B. IDB, B. vb from @ ta a left join @ tb bon. IDA = B. idbselect. IDA,. VA, B. IDB, B. vb from @ ta a left outer join @ tb bon. IDA = B. IDB -- right Outer Join select. IDA,. VA, B. IDB, B. vb from @ ta a right join @ tb bon. IDA = B. idbselect. IDA,. VA, B. IDB, B. vb from @ ta a right outer join @ tb bon. IDA = B. IDB -- complete Outer Join select. IDA,. VA, B. IDB, B. vb from @ ta a full join @ tb bon. IDA = B. idbselect. IDA,. VA, B. IDB, B. vb from @ ta a full outer join @ tb bon. IDA = B. IDB -- cross join select. IDA,. VA, B. IDB, B. vb from @ ta a cross join @ tb B -- Self JOIN SELECT. IDA,. VA, B. IDA, B. va from @ ta a, @ ta B WHERE. IDA = B. IDA +1Run: -- create table table1, table2: create table table1 (id int, name varchar (10) Create table table2 (id int, score int) insert into table1 select1, 'Lil' insert into table1 select2, 'Zhang 'insert into table1 select4, 'Wang' insert into table2 select1,90Insert into table2 select2,100Insert into table2 select3,70For example, table --------------------------------------------- table1 | table2 | --------------------------------------------- idname | idscore | 1lee |190| 2zhang |2100| 4wang |370| ------------------------------------------------- The following are all executed in the query analyzer 1. external connections1. Concept: left Outer Join, right outer join, or complete external join2. Left join: left join or left outer join (1) The result set of the left outer Join 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 ). (2) SQL statement select * from table1 left join table2 on table1.id = table2.id --------------- result ------------- idnameidscore -------------------------- 1lee1902zhang21004wangNULLNULL ------------------------------ Note: contains all the clauses of table1, return the corresponding fields of table2 according to specified, non-conforming values are displayed as null.3. Right join: right join or right outer join (1) The right outer join is the 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. (2) SQL statement select * from table1 right join table2 on table1.id = table2.id --------------- result ------------- idnameidscore -------------------------- 1lee1902zhang2100NULLNULL370 ------------------------------ Note: contains all the clauses of table2, return the corresponding fields of table1 according, non-conforming values are displayed as null.4. Complete External join: full join or full outer join (1) The Complete 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. (2) SQL statement select * from table1 full join table2 on table1.id = table2.id --------------- result ------------- idnameidscore -------------------------- returns the sum of left and right connections (see upper left and right connections) 2.1. Concept: inner join is a join that uses a comparison operator to compare the values of the columns to be joined.2. Inner join: join or inner join3. SQL statement select * from table1 join table2 on table1.id = table2.id ------------- result ------------- idnameidscore limit 1lee1902zhang2100 rows Note: only columns of table1 and table2 that meet the conditions are returned4. Equivalent (same as the following execution) A: select. *, B. * from table1 a, table2 B where. id = B. idB: select * from table1 cross join table2 where table1.id = table2.id (Note: cross join can only use where, cannot use on) III. cross join (complete)1. Concept: A cross join without a WHERE clause will generate the Cartesian product of the table involved in the join. The number of rows in the first table multiplied by the number of rows in the second table is equal to the size of the Cartesian result set. (Table1 and table2 cross join generate 3 *3= 9 records)2. Cross join: cross join (without the condition where ...)3. SQL statement select * from table1 cross join table2 ------------- result --------------- idnameidscore ------------------------------ else ------------------------ Note: 3 * is returned *3= 9 records, I .e. Cartesian Product4. Equivalent (same as the following execution) A: select * from table1, table2