SQL Compaction Basics (i): differences between inner join, outer join, and Cross join

first, data construction

First build the table, then talk.

Create DatabaseTest UseTestCreate TableA (AIDint Identity(1,1)Primary Key, namenvarchar( -), ageint)Create TableB (BIDint Identity(1,1)Primary Key, namenvarchar( -), Genderint)

After creation, insert data

InsertA (Name,age)Values('Zhang San', *)InsertA (Name,age)Values('John Doe', -)InsertA (Name,age)Values('XXX', *)InsertA (Name,age)Values('YYY', *)

InsertB (Name,gender)Values('Zhang San',1)InsertB (Name,gender)Values('John Doe',1)InsertB (Name,gender)Values('AAA',2)InsertB (Name,gender)Values('BBB',2)

second, contrast test

By default it is inner join (that is, you enter the join directly), the left join and right join used in development belong to the outer join, and the outer join also includes the full join. Below I let you know the difference by the icon.

There are two tables, table a,table B each with four records, of which two records name is the same:

The result of the 1.INNER JOIN is the intersection of AB

SELECT * from A INNER JOIN B on a.name = B.name

A 2.LEFT [OUTER] JOIN produces a full set of table A, whereas a match in B table has a value, and no match is substituted with a null value.

SELECT * from A left OUTER JOIN B on a.name = B.name

A 3.RIGHT [OUTER] JOIN produces a full set of Table B, and a match in table A has a value, and no match is substituted with a null value.

SELECT * from A right OUTER JOIN B on a.name = B.name

The 4.FULL [OUTER] JOIN produces a and B's set. For records that do not have a match, NULL is the value.

SELECT * from A full OUTER JOIN B on a.name = B.name

You can find the value without matching by the IS null:

SELECT * from A full OUTER JOIN B on a.name = B.name

WHERE a.id is null OR b.id is null

5. Cross joins a n*m combination of the data from table A and table B, the Cartesian product. If this regular session produces 4*4=16 records, we must filter the data during the development process, so this is seldom used.

SELECT * from A cross JOIN B

We believe that the difference between inner join, outer join and Cross join is at a glance.

Add one point: According to the SQL Standard cross join is a Cartesian product. For MySQL, however, Cross joins are equivalent to INNER joins.

SQL Compaction Basics (i): differences between inner join, outer join, and Cross join