Inner Connection, outer link (left connection, right connection, full connection), cross connection big Summary + SQL joins plot [go]

1. What is a connection query?

Concept: Query data from these tables based on the relationship between the columns of two tables or multiple tables.

Objective: To implement multiple table query operations.

2. Classification:

First of all, the connection is divided into three kinds: inner connection, outer connection, cross connection

Internal connection (INNER join):

Divided into three kinds: equivalent connection, natural connection, unequal connection

Outer JOIN (OUTER join):

Divided into three types:
Left outer join (Ieft OUTER join or RIGHT join)
Right outer join (OUTER join or starboard join)
Full-OUTER join or complete join

Cross join:

There is no WHERE clause, which returns the Cartesian product of all data rows in the Join table

3. Introduction of specific Use

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:

Specific Case table:

Book Table A:

Stu Table B:

1. Inner Connection

Definition: Only the rows that meet the join criteria in two tables are combined as result sets.
Within a connection, only rows that match in two tables can appear in the result set
Key words:INNER JOIN
Format:

SELECT  from [INNER] JOIN table name 2 on or where conditional expression  

Inner Connection Category:

Equivalent join: 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 .

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

Natural join: 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 .

SQL statements:

 1 , select  Span style= "color: #808080;" >*  as  a,stu as  b where  a.sutid =   B.stuid  2 , select  *  from  book as  a inner  join  stu as  b on  a.sutid =   B.stuid  // connections can be used in either of the two ways, where the inner of the second way can be omitted. 

Result: The same ID column showing the results

2. Outer JOIN

An outer join can be a left outer join, a right outer join, or a full outer join.

This means: On the basis of the inner join, it also contains all the non-conforming data rows in the table , and the corresponding table columns are filled with null (left--corresponding right null)
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.

Select *  from  as  Left Join  as  on = B.stuid  

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.

Select *  from  as  Right Join  as  on = B.stuid  

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.

Select *  from  as  Full outer Join  as  on = B.stuid

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 .

Select *  from  as  Cross Join  as Order  by a.ID  

Appendix A tall Illustration:

Copyright NOTICE: Welcome to Exchange! 52205916

Inner Connection, outer link (left connection, right connection, full connection), cross connection big Summary + SQL joins plot [go]