Join usage in MySQL

Article directory

Join usage in SQL

#1 environment

MySQL5.7
Mac

#2 create table and insert data

Create two new tables tableA and tableB

mysql> create table tableA ( id int(10), name varchar(100) );
mysql> create table tableB ( id int(10), name varchar(100) );

insert data

tableA:

mysql> insert into tableA values(1,'Bruges');
mysql> insert into tableA values(2,'Paris');
mysql> insert into tableA values(3,'London');
mysql> insert into tableA values(4,'Berlin');
mysql> insert into tableA values(5,'Jerusalem');

tableB:

mysql> insert into tableB values(1,'Doha');
mysql> insert into tableB values(2,'Jerusalem');
mysql> insert into tableB values(3,'New Delhi');
mysql> insert into tableB values(4,'Manila');
mysql> insert into tableB values(5,'Kuala Lumpur');

#3 start

#3.1 inner join

The result is the intersection of A and B, and inner join can also be abbreviated to join

select * from tableA join tableB ON tableA.name=tableB.name;

Output results:

+------+--------------+------+--------------+
| id   | name         | id   | name         |
+------+--------------+------+--------------+
|    5 | Jerusalem     |    2 | Jerusalem     |
+------+--------------+------+--------------+

#3.2 left join

The complete set of table A is generated, and the matched one in table B has A value (the unmatched one is replaced by A null value). left join returns all the rows in the left table and the rows in the right table that meet the ON condition. If the rows in the left table do not match in the right table, the corresponding data in the right table of this row is replaced by null.

select * from tableA left join tableB ON tableA.name=tableB.name;

Output results:

+------+--------------+------+--------------+
| id   | name         | id   | name         |
+------+--------------+------+--------------+
|5 | Jerusalem | 2 | Jerusalem|
|1 | Bruges | NULL | NULL|
|2 | Paris | NULL | NULL|
|3 | London | NULL | NULL|
|4 | Berlin | NULL | NULL|
+------+--------------+------+--------------+

Unique to the left table:

select * from tableA left join tableB ON tableA.name=tableB.name where tableB.name is null;

Output results:

+------+-----------+------+------+
| id   | name      | id   | name |
+------+-----------+------+------+
|    1 | Bruges    | NULL | NULL |
|    2 | Paris      | NULL | NULL |
|    3 | London      | NULL | NULL |
|    4 | Berlin      | NULL | NULL |
+------+-----------+------+------+

#3.3 right join

In contrast to the left join, the complete set of table B is generated, while the matched one in table A has A value (the unmatched one is replaced by A null value). Left join returns all the rows in the left table and the rows in the right table that meet the ON condition. If the rows in the left table do not match in the right table, the corresponding data in the right table of this row is replaced by null.

select * from tableA right join tableB ON tableA.name=tableB.name;

Output results:

+------+--------------+------+--------------+
| id   | name         | id   | name         |
+------+--------------+------+--------------+
|5 | Jerusalem | 2 | Jerusalem|
|Null | null | 1 | Doha|
|Null | null | 3 | New Delhi|
|Null | null | 4 | Manila|
|Null | null | 5 | Kuala Lumpur|
+------+--------------+------+--------------+

Unique to the right table:

select * from tableA right join tableB ON tableA.name=tableB.name where tableA.name is null;

Output results:

+------+------+------+-----------+
| id   | name | id   | name      |
+------+------+------+-----------+
| NULL | NULL |    1 | Doha      |
| NULL | NULL |    3 | New Delhi    |
| NULL | NULL |    4 | Manila    |
| NULL | NULL |    5 | Kuala Lumpur    |
+------+------+------+-----------+

#3.4 full join

The union of A and B. oracle has full join, but mysql has no full join. We can use union to get there.

select * from tableA left join tableB ON tableA.name=tableB.name 
union
select * from tableA right join tableB ON tableA.name=tableB.name;

Output results:

+------+--------------+------+--------------+
| id   | name         | id   | name         |
+------+--------------+------+--------------+
|5 | Jerusalem | 2 | Jerusalem|
|1 | Bruges | NULL | NULL|
|2 | Paris | NULL | NULL|
|3 | London | NULL | NULL|
|4 | Berlin | NULL | NULL|
|Null | null | 1 | Doha|
|Null | null | 3 | New Delhi|
|Null | null | 4 | Manila|
|Null | null | 5 | Kuala Lumpur|
+------+--------------+------+--------------+

110 original articles published, 20 praised, 40000 visitors+
Private letter follow

Tags: MySQL SQL Mac Oracle

Posted on Sun, 12 Jan 2020 00:04:57 -0800 by AbraCadaver