1、新建两个表格table1和table2,表结构如下:
2、初始化表格数据
table1
1 | a20050111 |
2 | a20050112 |
3 | a20050113 |
4 | a20050114 |
5 | a20050115 |
table2
1 | 2006032401 |
2 | 2006032402 |
3 | 2006032403 |
4 | 2006032404 |
8 | 2006032408 |
3、where查询
select * from table1 a,table2 b where a.aid=b.bid |
查询结果:
1 a20050111 1 2006032401
2 a20050112 2 2006032402
3 a20050113 3 2006032403
4 a20050114 4 2006032404
4、left join查询
select * from table1 a left join table2 b on a.aid=b.bid |
查询结果:
1 a20050111 1 2006032401
2 a20050112 2 2006032402
3 a20050113 3 2006032403
4 a20050114 4 2006032404
5 a20050115 NULL NULL
5、right join查询
select * from table1 a right join table2 b on a.aid=b.bid |
查询结果:
1 a20050111 1 2006032401
2 a20050112 2 2006032402
3 a20050113 3 2006032403
4 a20050114 4 2006032404
NULL NULL 8 2006032408
6、inner join查询
select * from table1 a inner join table2 b on a.aid=b.bid |
查询结果:
1 a20050111 1 2006032401
2 a20050112 2 2006032402
3 a20050113 3 2006032403
4 a20050114 4 2006032404