create table vips(id1 int, name varchar(10));
|
create table guests(id2 int, name varchar(10));
|
insert into guests values (1,"p2"),(2,"p1"),(3,"vip1"),(4,"vip2"),(5,"vip3");
|
insert into vips values (1,"vip1"),(2,"vip2");
|
# This is correct with `on` key word
|
MariaDB [test]> select * from guests g left join vips v on v.name=g.name union select * from guests g right join vips v on v.name=g.name;
|
+------+------+------+------+
|
| id2 | name | id1 | name |
|
+------+------+------+------+
|
| 3 | vip1 | 1 | vip1 |
|
| 4 | vip2 | 2 | vip2 |
|
| 1 | p2 | NULL | NULL |
|
| 2 | p1 | NULL | NULL |
|
| 5 | vip3 | NULL | NULL |
|
+------+------+------+------+
|
5 rows in set (0.000 sec)
|
|
# This is not correct using `using` keyword :
|
MariaDB [test]> select * from guests g left join vips v using(name) union select * from guests g right join vips v using(name);
|
+------+------+------+
|
| name | id2 | id1 |
|
+------+------+------+
|
| vip1 | 3 | 1 |
|
| vip2 | 4 | 2 |
|
| p2 | 1 | NULL |
|
| p1 | 2 | NULL |
|
| vip3 | 5 | NULL |
|
| vip1 | 1 | 3 | # this is the right join row and should be id2=3, id1=1 or row should be removed
|
| vip2 | 2 | 4 | # this is the right join row and should be id2=4, id1=3 or row should be removed
|
+------+------+------+
|
7 rows in set (0.001 sec)
|