mysql 外连接(左left join、右 right join)、内连接(inner join)、自连接(table1 t1 table1 t2)

内联(inner join):返回两表的集合
SELECT t1.name, t2.salary
  FROM employee AS t1 INNER JOIN info AS t2 ON t1.name = t2.name;

SELECT t1.name, t2.salary
  FROM employee t1 INNER JOIN info t2 ON t1.name = t2.name;
左外连接(left join):以左表为基准,如果 ON 或 USING 中的右表没有匹配的行,则将 LEFT JOIN 所有列设置为的行,右表则是 NULL
SELECT left_tbl.* FROM left_tbl LEFT JOIN right_tbl ON left_tbl.id = right_tbl.id
    WHERE right_tbl.id IS NULL;
右外连接(right join):以右表为基准,如果 ON 或 USING 中的左表没有匹配的行,则将 right JOIN 所有列设置为的行,左表则是 NULL
SELECT left_tbl.* FROM left_tbl RIGHT JOIN right_tbl ON left_tbl.id = right_tbl.id
    WHERE right_tbl.id IS NULL;

黄铭博客
请先登录后发表评论
  • latest comments
  • 总共0条评论