两表查询常用SQL-CSDN博客

1、两个表：table_a和table_b，求两表的交集，关键字：INNER JOIN

SELECT a.*,b.* FROM table_a AS a INNER JOIN table_b AS b ON a.id=b.id；

2、两个表：table_a和table_b，table_a为主表，关联查询table_b，table_b有数据就显示，没有数据就显示null，关键字：LEFT JOIN

SELECT a.*,b.* FROM table_a AS a LEFT JOIN table_b AS b ON a.id=b.id；

3、两个表：table_a和table_b，table_b为主表，关联查询table_a，table_a有数据就显示，没有数据就显示null，关键字：RIGHT JOIN

SELECT a.*,b.* FROM table_a  AS a RIGHT JOIN table_b  AS b ON a.id=b.id；

4、两个表：table_a和table_b，求table_a表中有且table_b表没有的数据

SELECT a.*,b.* FROM table_a AS a LEFT JOIN table_b AS b ON a.id=b.id WHERE b.id IS NULL；

5、两个表：table_a和table_b，求table_b表中有且table_a表没有的数据

SELECT a.*,b.* FROM table_a AS a RIGHT JOIN table_b AS b ON a.id=b.id WHERE a.id IS NULL;