MySQL Join 类型-Toy模板网

这篇具有很好参考价值的文章主要介绍了MySQL Join 类型。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

1 Join 类型有哪些

SQL Join 类型的区别
MySQL Join 类型,MySQL,mysql,left join,right join,inner join,full join

Inner Join: 左,右表都有的数据
Left Join: 左表返回所有的行, 右表没有的补充为 NULL
Right Loin: 右表返回所有的行, 左表没有的补充为 NULL
Full Outer Join: 左表 + 右表所有数据

用户表:
MySQL Join 类型,MySQL,mysql,left join,right join,inner join,full join
答题表:

2 Inner Join

查询左表和右表都有的数据
用户表 user_no = 1000 和答题表 user_no = 6543 , 不在交集范围, 没有在查询出来

-- inner join --
select tup.user_no, tup.university, tqpd.question_id, tqpd.result  
    from t_user_profile tup inner join t_question_practice_detail tqpd  
        on tup.user_no = tqpd.user_no;

MySQL Join 类型,MySQL,mysql,left join,right join,inner join,full join

3 Left Join

左表返回所有的行, 右表没有的补充为 NULL
用户表 user_no = 1000 对应的答题信息为空, 补充 NULL, 答题表 user_no = 6543 不在查询范围

-- left join --
select tup.user_no, tup.university, tqpd.question_id, tqpd.result  
    from t_user_profile tup left join t_question_practice_detail tqpd  
        on tup.user_no = tqpd.user_no;

MySQL Join 类型,MySQL,mysql,left join,right join,inner join,full join

4 Right Join

右表返回所有的行, 左表没有的补充为 NULL
答题表 user_no = 6543 对应的用户信息为空, 补充 NULL, 用户表 user_no = 1000 不在查询范围

-- right join --  
select tup.user_no, tup.university, tqpd.question_id, tqpd.result  
    from t_user_profile tup right join t_question_practice_detail tqpd  
        on tup.user_no = tqpd.user_no;

MySQL Join 类型,MySQL,mysql,left join,right join,inner join,full join

5 Full Join

在 MySQL 中没有 Full Join, 可以用 left join 和 right join 实现
用户表 user_no = 1000 对应的答题信息补充 NULL, 答题表 user_no = 6543 对应的用户信息补充 NULL

-- left join and right join --  
select tup.user_no, tup.university, tqpd.question_id, tqpd.result  
    from t_user_profile tup left join t_question_practice_detail tqpd  
        on tup.user_no = tqpd.user_no  
union  
select tup.user_no, tup.university, tqpd.question_id, tqpd.result  
    from t_user_profile tup right join t_question_practice_detail tqpd  
        on tup.user_no = tqpd.user_no;

MySQL Join 类型,MySQL,mysql,left join,right join,inner join,full join 文章来源地址https://www.toymoban.com/news/detail-720375.html