mysql left join测试

测试建表

create table if not exists u1
(
    id   int,
    name varchar(20)
) ENGINE = MyISAM
  DEFAULT CHARSET = utf8;
insert into u1
values (1, 'a');
insert into u1
values (2, 'b');
insert into u1
values (3, 'c');


create table if not exists u2
(
    id   int,
    name varchar(20)
) ENGINE = MyISAM
  DEFAULT CHARSET = utf8;
insert into u2
values (2, 'bb');
insert into u2
values (2, 'cc');
insert into u2
values (7, 'yy');

create table if not exists u3
(
    id   int,
    name varchar(20)
) ENGINE = MyISAM
  DEFAULT CHARSET = utf8;
insert into u3
values (2, 'apple');
insert into u3
values (1, 'banana');
insert into u3
values (9, 'lizi');

表u1原本是3行,由于关联条件id 在u2 不是唯一的,有重复,所以关联后表的行数变多了.

select *
from u1
         left join u2 on u1.id = u2.id
         left join u3 on u2.id=u3.id;

id name id name	id	name
2	b	2	bb	2	apple
2	b	2	cc	2	apple
1	a	(null)	(null)	(null)	(null)
3	c	(null)	(null)	(null)	(null)

不写关联条件,报错
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘’ at line 3

select *
from u1
         left join u2 

少写一个关联条件,报错
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘’ at line 4

select *
from u1
         left join u2 
         left join u3 on u2.id=u3.id

如下写法,报错
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘’ at line 4

select *
from u1
         left join u2 
         left join u3 on u1.id=u2.id and u2.id=u3.id