Join table Supplement for SQL

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 criteria to further filter the rows selected by the join condition.

Connections can be divided into the following categories:

internal connections. (typical join operation, using a comparison operator like = or <>). Including equal connections and natural connections .
An inner join uses a comparison operator 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.

External Connection。 An outer join can beleft outward connection, right outer join, or 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:
Left JOIN or left OUTER join.
The result set for the left outer join includes all rows from 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.
Right join or right OUTER join.
The right outward connection is a reverse connection of the left outward connection. 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.

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.

Cross Connect. The cross join returns all the rows in the left table, and each row in the left table is combined with all the rows in the right table. Cross joins are also called Cartesian product.

For example, the following inner join retrieves a publisher residing in the same state and city as the

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

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 outward connection. 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

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

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

Right connection
Select a.*,b.* from a right join 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

Fully connected
Select a.*,b.* from a full join 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 SQL code

Declare@ta table (IDA int,va varchar) DECLARE@TB table (IDB int,vb varchar) INSERT into @TASELECT1, ' AA ' UNION SELECT 2, ' BC ' Union SELECT3, ' CCC ' INSERT into @TBSELECT1, ' 2 ' Union SELECT3, ' ' union SELECT4, ' 67 '--INNER JOIN simple notation Select A.ida,a.va,b. Idb,b.vb from @TA A, @TB bwhere a.ida=b.idb--inner JOIN select A.ida,a.va,b.idb,b.vb from @TA A INNER join @TB BON A.ida=b.idbselec  T A.ida,a.va,b.idb,b.vb from @TA a join @TB BON a.ida=b.idb--left outer join select A.ida,a.va,b.idb,b.vb from @TA A ieft JOIN @TB BON A.ida=b.idbselect A.ida,a.va,b.idb,b.vb from @TA A left OUTER join @TB BON a.ida=b.idb--right outer join select A.ida,a.va,b.idb,b.vb From @TA a right joins @TB BON a.ida=b.idbselect A.ida,a.va,b.idb,b.vb from @TA A right OUTER JOIN @TB BON a.ida=b.idb--complete outside Join select A.ida,a.va,b.idb,b.vb from @TA a full JOIN @TB BON a.ida=b.idbselect A.ida,a.va,b.idb,b.vb from @TA a full OUTER Join @TB BON a.ida=b.idb--crossing join Select A.ida,a.va,b.idb,b.vb from @TA A Cross join @TB b--self-join select A.ida,a.va,b.ida,b.va FR OM @TA A, @TA B WHERE A.ida=b.ida+1 execution in Query Analyzer:--Build Tables Table1,table2:create table table1 (ID int,name varchar) CREATE TABLE table2 (ID int,score int) INSERT INTO table1 Select 1, ' Lee ' insert INTO table1 select 2, ' Zhang ' insert to table1 Select 4, ' Wang ' insert into table2 s Elect 1,90insert to table2 select 2,100insert into table2 Select 3,70 as table-------------------------------------------------table1|table2|----------------------------------------- --------idname|idscore|1lee|190|2zhang|2100|4wang|370|------------------------------------------------- The following is an outer join in Query Analyzer 1. Concept: Includes a LEFT outer join, a right outer join, or a full outer join 2. LEFT JOIN: The result set of the left joins or Ieft outer join (1) is an outer join that includes all rows of the left table specified in the leftmost outer clause. Instead of just the rows that the join columns match. 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 (NULL). (2) SQL statement SELECT * FROM table1 LEFT join table2 on table1.id= Table2.id-------------Results-------------idnameidscore------------------------------1lee1902zhang21004wangnullnull----------- -------------------NOTE: All clauses that contain table1, return table2 corresponding fields according to the specified criteria, non-conforming null display 3. Right connection: either R join or starboard outer join (1) A right outer join is a reverse join of a left outer join. will returnAll rows of the right table. 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. (2) SQL statement SELECT * FROM table1 right join table2 on table1.id= Table2.id-------------Results-------------idnameidscore------------------------------1lee1902zhang2100nullnull370------------- -----------------NOTE: All clauses that contain table2, return table1 corresponding fields according to the specified criteria, non-conforming null display 4. Full join or complete outer join (1) 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. (2) SQL statement SELECT * FROM table1 full join table2 on table1.id= Table2.id-------------Results-------------idnameidscore------------------------------1lee1902zhang21004wangnullnullnullnull370 ------------------------------Note: Returns the left and right connections and (see Upper and lower connections) two, inner connection 1. Concept: An inner join is a join that compares the values of the columns to be joined by comparison operators 2. Internal connection: Join or inner Join3.sql Statement SELECT * FROM table1 join table2 on table1.id= Table2.id-------------Results-------------idnameidscore------------------------------1lee1902zhang2100------------------------ ------Note: Returns only the columns of Table1 and table2 that match the criteria 4. Equivalent (same as the following) A:select a.*,b.* from table1 a,table2 b where a.id=b.idb:select * from Table1 Cross Join Table2 wheRe Table1.id=table2.id (note: After cross join conditions can only be used where, cannot be used on) three, crossed connections (full) 1. Concept: A cross join without a WHERE clause will produce a 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 equals the size of the Cartesian product result set. (Table1 and table2 cross-joins generate 3*3=9 Records) 2. Cross join: Crosses joins (without conditions where ...) 3.sql Statement SELECT * FROM table1 cross join Table2-------------Results-------------idnameidscore------------------------------1lee1902zhang1904wang1901lee21002zhang21004w ang21001lee3702zhang3704wang370------------------------------Note: Returns the 3*3=9 record, which is the Cartesian product 4. Equivalent (same as the following execution) A:select * from Table1,table2

Join table Supplement for SQL