Seven types of join in MySQL and seven types of join in mysql

Seven types of join in MySQL and seven types of join in mysql
Create a table

Here we will first create two tables with foreign key associations.

Create database db0206; USE db0206; create table 'db0206 '. 'tbl _ dept' ('id' INT (11) not null AUTO_INCREMENT, 'deptname' VARCHAR (30), 'locadd' VARCHAR (40 ), primary key ('id') ENGINE = innodb charset = utf8; create table 'db0206 '. 'tbl _ emp' ('id' INT (11) not null AUTO_INCREMENT, 'name' VARCHAR (20), 'deptid' INT (11 ), primary key ('id'), foreign key ('demotid') REFERENCES 'db0206 '. 'tb _ dept' ('id') ENGINE = innodb charset = utf8;/* INSERT data */insert into tbl_dept (deptName, locAdd) VALUES ('rd ', 11); insert into tbl_dept (deptName, locAdd) VALUES ('hr', 12); insert into tbl_dept (deptName, locAdd) VALUES ('mk ', 13 ); insert into tbl_dept (deptName, locAdd) VALUES ('mis ', 14); insert into tbl_dept (deptName, locAdd) VALUES ('fd', 15); insert into tbl_emp (NAME, deptId) VALUES ('z3', 1); insert into tbl_emp (NAME, deptId) VALUES ('z4', 1); insert into tbl_emp (NAME, deptId) VALUES ('z5', 1); insert into tbl_emp (NAME, deptId) VALUES ('w5', 2); insert into tbl_emp (NAME, deptId) VALUES ('w6 ', 2); insert into tbl_emp (NAME, deptId) VALUES ('s7 ', 3); insert into tbl_emp (NAME, deptId) VALUES ('ss', 4 );

The writer's diagram is connected to the SQL statement writing and query results.

Executed SQL statements and query results

• Executed SQL statement

select * from tbl_dept a inner join tbl_emp b on a.id=b.deptId;

• Query Result
  

Left Outer Join Wen's diagram

Executed SQL statements and query results

• Executed SQL statement

select * from tbl_dept a left join tbl_emp b on a.id=b.deptId;

• Query Result
  

Right outer join right Outer Join Wen's diagram

Executed SQL statements and query results

• Executed SQL statement

select * from tbl_dept a right join tbl_emp b on a.id=b.deptId;

• Query Result
  

Left join left join Wen's diagram

Executed SQL statements and query results

• Executed SQL statement

elect * from tbl_dept a left join tbl_emp b on a.id=b.deptId where b.deptId is null;

• Query Result

Graph of right join and right join

Executed SQL statements and query results

• Executed SQL statement

select * from tbl_dept a right join tbl_emp b on a.id=b.deptId where a.id is null;

• Query Result

Graph of Full-connection and full-connection

Executed SQL statements and query results

• Executed SQL statement

select * from tbl_dept a right join tbl_emp b on a.id=b.deptId union select * from tbl_dept a left join tbl_emp b on a.id=b.deptId;

• Query Result
  

A dataset's diagram that does not appear in both tables

Executed SQL statements and query results

• Executed SQL statement

select * from tbl_dept a right join tbl_emp b on a.id=b.deptId where a.id is null union select * from tbl_dept a left join tbl_emp b on a.id=b.deptId where b.deptId is null;

• Query Result