Chapter 2: SQL multi-table join _ MySQL

Chapter 2: Connecting multiple SQL tables to bitsCN.com

Multi-table join relationships of SQL are: INNER JOIN, outer join, and cross join.

Create two tables for demonstration:

TB_Characters:

Id	Character
1	Introverted
2	Extrovert
3	Neutral character

TB_Colors:

Id	Color
1	Green
2	Red
4	Blue

1. internal connection:

JOIN or inner join: returns the intersection of inner join.

:

SELECT * FROM tb_characters INNER JOIN tb_colors ON tb_characters.ID = tb_colors.ID;

Result:

II. external connection:

External connections can be divided into: left join, right join, and full outer join.

1. left join ):

:

SELECT * FROM tb_characters LEFT JOIN tb_colors ON tb_characters.ID = tb_colors.ID;

Result:

2. right join ):

:

SELECT * FROM tb_characters RIGHT JOIN tb_colors ON tb_characters.ID = tb_colors.ID;

Result:

3. full outer connection:

:

select * from tb_characters LEFT JOIN tb_colors on tb_characters.ID=tb_colors.IDUNIONselect * from tb_characters RIGHT JOIN tb_colors on tb_characters.ID=tb_colors.ID

Result:

III. cross join ):

Returns the Cartesian product of the table involved in the join operation.

SELECT * FROM tb_characters CROSS JOIN tb_colors;

Result:

Supplement:

1. obtain the internal connection result as follows:

SELECT * FROM tb_characters,tb_colors WHERE tb_characters.ID = tb_colors.ID;

2. obtain the cross-join result as follows:

SELECT * FROM tb_characters,tb_colors;

BitsCN.com