How to Join SQL

A common scenario when using SQL is associated queries between multiple tables.

In the ANSI SQL standard, there are five types of SQL join, including Inner Join, Left Join, Right Join, Full Join, and Cross join. Self join that joins with the table itself.

In this article, only two tables are considered, and the same is true for multiple tables.

The structure of the two tables is as follows:

create table t_a (
    id bigint auto_increment,
    key varchar(255)
);

create table t_b (
    id bigint auto_increment,
    key varchar(255)
);
Copy the code

The SQL below is just pseudo-code, which is represented differently in different database systems. Join relationships are represented using Venn diagrams.

Cross Join

The cartesian product of the two tables is rarely used in formal business, and is mainly used to test the performance of the service.

                                                           

A Cartesian product is a combination of each item in two sets to get a new set, and in a database, it’s a combination of data in two tables.

select * from t_a cross join t_b;
Copy the code

Cross jojn is another way of writing:

select * from t_a, t_b;
Copy the code

Inner Join

Innner Join means that only the overlap part of the two tables is taken, and the overlap part is the record that meets the condition.

Inner Join SQL as follows:

select * from t_a a inner join t_b b on a.key = b.key;
Copy the code

In the case of an Inner JOIN, you can also view it as finding the records that satisfy the criteria from the Cross join above.

Outer Join

Outer Join is the opposite of an Inner Join. The Outer Join retains all the entries in the table, depending on the type of Join.

The Left Join keeps all the records from the Left table and takes only the matches from the right table. The Right Join will keep all the records from the Right table, and then fetch only the matches from the left table. Full Join fetches the records from both tables.

Left Join

Left JOIN Is also called Left outer Join.

Indicates that the system queries all data in table A and only the part in table B that intersects with table A.

select * from t_a a left join t_b b on a.key = b.key;
Copy the code

The SQL above can also be written as:

select * from t_a a left outer join t_b b on a.key = b.key;
Copy the code

A variation of Left Join fetches only those results that do not appear in table B, with one simple change:

select * from t_a a left join t_b b on a.key = b.key where b.key is null;
Copy the code

Right Join

Right JOIN is also called Right outer join.

Right Join is the opposite of Left Join. The Right Join will mainly query the data in table B, and only query the part of table A that intersects with table B.

select * from t_a a right join t_b b on a.key = b.key;
Copy the code

The SQL above can also be written as:

select * from t_a a right outer join t_b b on a.key = b.key;
Copy the code

There is also a variant of Right Join that fetters only those results that do not appear in table A:

select * from t_a a right join t_b b on a.key = b.key where a.key is null;
Copy the code

Full Join

Full JOIN is also called Full outer join.

Full JOIN will select all the data from the two tables:

select * from t_a a full join t_b b on a.key = b.key;
Copy the code

The SQL above can also be written as:

select * from t_a a full outer join t_b b on a.key = b.key;
Copy the code

Full JOIN also has a variant that fetches only the region where table A intersects table B, as opposed to an inner join:

select * from t_a a full join t_b b on a.key = b.key where a.key is null or b.key is null;
Copy the code

Self join

In a real world scenario, there are special requirements, such as finding all records in a table that have the same field, where Self Join is used.

select * from t_a a1 inner join t_a a2 on a1.key = a2.key;
Copy the code

The text/Rayjun

                                              

REF

[1] en.wikipedia.org/wiki/Join_ (…

[2] en.wikipedia.org/wiki/Venn_d…

[3] en.wikipedia.org/wiki/Americ…