mysql join 与inner 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,
    sex varchar(20)
) ENGINE = MyISAM
  DEFAULT CHARSET = utf8;
insert into u2
values (3, 'male');
insert into u2
values (2, 'female');
insert into u2
values (7, 'male');

join与inner join完全一样

select * from u1 join u2 on u1.id=u2.id
id	name	id	sex
3	c	3	male
2	b	2	female

select * from u1 inner join u2 on u1.id=u2.id
id	name	id	sex
3	c	3	male
2	b	2	female

1 join 与inner join没有区别,关联条件共有的才会显示
2 a join b,关联后的表行数小于a并且小于b
3 join可以用于数据过滤

内连接不加关联条件,等价于cross join

select * from u1 join u2
id	name	id	sex
1	a	3	male
2	b	3	male
3	c	3	male
1	a	2	female
2	b	2	female
3	c	2	female
1	a	7	male
2	b	7	male
3	c	7	male

id	name	id	sex
1	a	3	male
2	b	3	male
3	c	3	male
1	a	2	female
2	b	2	female
3	c	2	female
1	a	7	male
2	b	7	male
3	c	7	male

内连接也可以不用on,用where效果一样

select * from u1 join u2 where u1.id=u2.id
id	name	id	sex
3	c	3	male
2	b	2	female

select * from u1 join u2 where u1.id=u2.id
id	name	id	sex
3	c	3	male
2	b	2	female

内连接也可以用不等的关联条件

select * from u1 join u2 on u1.id<u2.id;
id	name	id	sex
1	a	3	male
2	b	3	male
1	a	2	female
1	a	7	male
2	b	7	male
3	c	7	male

select * from u1 join u2 where u1.id<u2.id;
id	name	id	sex
1	a	3	male
2	b	3	male
1	a	2	female
1	a	7	male
2	b	7	male
3	c	7	male