sql 查询两表某字段一样的数据_MySQL数据库的多表查询操作常用SQL命令

本节主要针对MySQL数据库的多表查询操作常用SQL命令进行总结，主要包括内连接(INNER JOIN)，左外部连接(LEFT JOIN)，右外部连接(RIGHT JOIN)，全连接(FULL JOIN)，交叉连接(CROSS JOIN)，自然连接(NATURAL JOIN)，自连接(SELF JOIN)，联合查询(UNION)。

SQL JOIN

1.内连接-INNER JOIN

INNER JOIN 主要是获取两个或多张表的交集，只返回匹配的行；多张表之间没有主表附表之分。

//INNER JOIN = JOIN//规则1SELECT column_list FROM table1 t1 INNER JOIN table2 t2 ON t1.column_name=t2.column_name;SELECT * FROM table1 t1 INNER JOIN table2 t2 ON t1.主键=t2.外键; //常用写法//规则2SELECT column_list FROM table1 t1 JOIN table2 t2 ON t1.column_name=t2.column_name;SELECT * FROM table1 t1 JOIN table2 t2 ON t1.主键=t2.外键;//常用写法//规则3SELECT column_list FROM table1 t1 JOIN table2 t2 USING (相同列名);SELECT * FROM table1 t1 JOIN table2 t2 USING (id);//常用写法//规则4SELECT column_list FROM table1 t1, table2 t2 where t1.column_name=t2.column_name;SELECT * FROM table1 t1, table2 t2 where t1.主键=t2.外键;//常用写法//案例SELECT t1.*, t2.* FROM user t1 INNER JOIN role t2 ON t1.id = t2.uid;SELECT t1.*, t2.* FROM user t1 JOIN role t2 ON t1.id = t2.uid;SELECT t1.*, t2.* FROM users1 t1 JOIN users2 t2 USING (id);SELECT t1.*, t2.* FROM user t1, role t2 where t1.id = t2.uid;

SQL JOIN - INNER JOIN

2.左外部连接-LEFT JOIN

LEFT JOIN 以左表为主表，右表为附表；返回左表的全部行和右表满足ON条件的行；

如果左表的行在右表中没有匹配，那么这一行右表中对应数据用NULL代替；

可以是一主一附(一个左表，一个右表)，也可以是一主多附(一个左表，多个右表)

//LEFT JOIN = LEFT OUTER JOIN//规则1SELECT column_list FROM table1 t1 LEFT JOIN table2 t2 ON t1.column_name=t2.column_name;SELECT * FROM table1 t1 LEFT JOIN table2 t2 ON t1.主键=t2.外键; //常用写法//规则2SELECT column_list FROM table1 t1 LEFT OUTER JOIN table2 t2 ON t1.column_name=t2.column_name;SELECT * FROM table1 t1 LEFT OUTER JOIN table2 t2 ON t1.主键=t2.外键;//常用写法//案例SELECT t1.*, t2.* FROM user t1 LEFT JOIN role t2 ON t1.id = t2.uid;SELECT t1.*, t2.* FROM user t1 LEFT OUTER JOIN role t2 ON t1.id = t2.uid;//user 是主表，role和resource是附表SELECT t1.*, t2.*,t3.* FROM user t1  LEFT JOIN role t2 ON t1.id = t2.uid LEFT JOIN resource t3 ON t1.id = t3.uid;

SQL JOIN - LEFT JOIN

3.右外部连接-RIGHT JOIN

RIGHT JOIN 以右表为主表，右表为附表；返回右表的全部行和左表满足ON条件的行；

如果右表的行在左表中没有匹配，那么这一行左表中对应数据用NULL代替；

//RIGHT JOIN = RIGHT OUTER JOIN//规则1SELECT column_list FROM table1 t1 RIGHT JOIN table2 t2 ON t1.column_name=t2.column_name;SELECT * FROM table1 t1 RIGHT JOIN table2 t2 ON t1.主键=t2.外键; //常用写法//规则2SELECT column_list FROM table1 t1 RIGHT OUTER JOIN table2 t2 ON t1.column_name=t2.column_name;SELECT * FROM table1 t1 RIGHT OUTER JOIN table2 t2 ON t1.主键=t2.外键;//常用写法//案例SELECT t1.*, t2.* FROM user t1 RIGHT JOIN role t2 ON t1.id = t2.uid;SELECT t1.*, t2.* FROM user t1 RIGHT OUTER JOIN role t2 ON t1.id = t2.uid;

SQL JOIN - RIGHT JOIN

4.全连接-FULL JOIN

FULL JOIN 会从左表和右表返回符合条件的所有行；

只要左表(table1)和右表(table2)其中一个表中存在匹配，就返回结果；

如果其中一个表的数据行在另一个表中没有匹配的行，那么对面的数据用NULL代替；

FULL JOIN 结合了 LEFT JOIN 和 RIGHT JOIN 的结果;

SQLServer 和Oracle支持FULL JOIN；

MySQL不支持FULL JOIN,可以使用LEFT JOIN + UNION + RIGHT JOIN来达到效果

//FULL JOIN = FULL OUTER JOIN//规则1SELECT column_list FROM table1 t1 FULL JOIN table2 t2 ON t1.column_name=t2.column_name;SELECT * FROM table1 t1 FULL JOIN table2 t2 ON t1.主键=t2.外键; //常用写法//规则2SELECT column_list FROM table1 t1 FULL OUTER JOIN table2 t2 ON t1.column_name=t2.column_name;SELECT * FROM table1 t1 FULL OUTER JOIN table2 t2 ON t1.主键=t2.外键;//常用写法//mysql中LEFT JOIN + UNION + RIGHT JOINSELECT column_list FROM table1 t1 LEFT JOIN table2 t2 ON t1.column_name=t2.column_nameUNIONSELECT column_list FROM table1 t1 RIGHT JOIN table2 t2 ON t1.column_name=t2.column_name;//案例  SQLServer / Oracle下使用SELECT t1.*, t2.* FROM user t1 FULL JOIN role t2 ON t1.id = t2.uid;SELECT t1.*, t2.* FROM user t1 FULL OUTER JOIN role t2 ON t1.id = t2.uid;//案例  MySQL下使用SELECT * FROM user t1 LEFT JOIN role t2 ON  t1.id = t2.uidUNIONSELECT * FROM user t1 RIGHT JOIN role t2 ON  t1.id = t2.uid;

SQL JOIN - FULL JOIN

5.交叉连接-CROSS JOIN

FULL JOIN 返回左表中的所有行，左表中的每一行与右表中的所有行组合；

交叉联接也称作笛卡尔积；

如果table1有5行记录，table2有6行记录，交叉连接结果为5*6=30条记录；

//规则1  table1和table2两表的笛卡尔积 = 两个表行数的乘积SELECT * FROM table1 CROSS JOIN table2;//规则2  首先生成两表的笛卡尔积，再根据ON条件来过滤SELECT * FROM table1 t1 CROSS JOIN table2 ON t1.column_name=t2.column_name;//规则3 首先生成两表的笛卡尔积，再根据WHERE条件来过滤SELECT * FROM table1 t1 CROSS JOIN table2 WHERE t1.column_name=t2.column_name;//案例SELECT * FROM user CROSS JOIN role;SELECT * FROM user t1 CROSS JOIN role ON t1.id=t2.uid;SELECT * FROM user t1 CROSS JOIN role WHERE t1.id=t2.uid;

SQL JOIN - CROSS JOIN

6.自然连接-NATURAL JOIN

NATURAL JOIN 等同于INNER JOIN或INNER USING 匹配两个表中相同名称的列；

具有相同名称的关联表的列将仅出现一次;

关联的表具有一对或多对相同名称的列;这些列必须是相同的数据类型;

不要在自然连接中使用ON子句;

//规则SELECT * FROM table1 NATURAL JOIN table2;//案例SELECT * FROM table1 NATURAL JOIN table2;

7.自连接-SELF JOIN

SELF JOIN 是表与自身联接(一元关系)的连接，特别是当表具有引用其自身主键的外键时；

连接表本身意味着表的每一行都与自身以及表的其他每一行结合在一起；

自连接可以看作是同一表的两个副本的连接；

//规则SELECT * FROM table t1, table t2 WHERE t1.column_name=t2.column_name;SELECT * FROM table t1 INNER JOIN table t2 ON t1.column_name=t2.column_name;//案例SELECT * FROM resource t1, resource t2 WHERE t1.id=t2.pid;SELECT * FROM resource t1 INNER JOIN resource t2 ON t1.id=t2.pid;

8.联合查询-UNION

UNION 操作符用于合并两个或多个 SELECT 语句的结果集;

UNION 内部的每个 SELECT 语句必须拥有相同数量的列,相似的数据类型，相同的列顺序；

//规则1 UNION 操作符默认地选取不同的值SELECT column_list FROM table1 UNION SELECT column_list FROM table2;//规则2 UNION ALL允许选取重复的值SELECT column_list FROM table1 UNION ALL SELECT column_list FROM table2;//案例select id,name,pass,age from user1 union select id,name,pass,age from user2;select id,name,pass,age from user1 union all select id,name,pass,age from user2;

至此MySQL数据库的多表查询操作常用SQL命令总结完毕，编程就这么简单，小伙伴们不妨试试，总结不易，希望大家多点赞收藏转发，在此谢谢！