准备条件:
假定有两个表
table1:
ID DATA
1 A
2 B
3 C
table2:
ID DATA
1 A
2 B
4 D
问题提出:
1.如果要查找table1和table2中相同的数据,
则sql语句为: SELECT table1.ID, table1.DATA, table2.ID, table2.DATA
FROM table1 INNER JOIN table2 ON (table1.DATA = table2.DATA) AND (table1.ID = table2.ID);
查询结果为:
table1_ID | table1_DATA | table2_ID | table2_DATA |
1 | A | 1 | A |
2 | B | 2 | B |
2.如果要查找在table1中且也在table2中的数据,
则sql语句为:SELECT table1.ID, table1.DATA, table2.ID, table2.DATA
FROM table1 LEFT JOIN table2 ON (table1.DATA = table2.DATA) AND (table1.ID = table2.ID);
查询结果为:
table1_ID | table1_DATA | table2_ID | table2_DATA |
1 | A | 1 | A |
2 | B | 2 | B |
3 | C |
3.如果要查找在table2中且也在table1中的数据,
则sql语句为:SELECT table1.ID, table1.DATA, table2.ID, table2.DATA
FROM table1 RIGHT JOIN table2 ON (table1.DATA = table2.DATA) AND (table1.ID = table2.ID);
查询结果为:
table1_ID | table1_DATA | table2_ID | table2_DATA |
1 | A | 1 | A |
2 | B | 2 | B |
4 | D |
sql 的 INNER JOIN 、LEFT JOIN 、RIGHT JOIN 代码和查询效果如上。
希望和大家多多交流....