both Table A and Table B.
|
|
SELECT * FROM TableA
FULL OUTER JOIN TableB
ON TableA.name = TableB.name
id name id name
-- ---- -- ----
1 Pirate 2 Pirate
2 Monkey null null
3 Ninja 4 Ninja
4 Spaghetti null null
null null 1 Rutabaga
null null 3 Darth Vader
Full outer join produces the set of all records in Table A and Table B, with matching records from both sides where available. If there is no match, the missing side will contain null.
|
|
SELECT * FROM TableA
LEFT OUTER JOIN TableB
ON TableA.name = TableB.name
id name id name
-- ---- -- ----
1 Pirate 2 Pirate
2 Monkey null null
3 Ninja 4 Ninja
4 Spaghetti null null
Left outer join produces a complete set of records from Table A, with the matching records (where available) in Table B. If there is no match, the right side will contain null.
|
|
SELECT * FROM TableA
LEFT OUTER JOIN TableB
ON TableA.name = TableB.name
WHERE TableB.id IS null
id name id name
-- ---- -- ----
2 Monkey null null
4 Spaghetti null null
To produce the set of records only in Table A, but not in Table B, we perform the same left outer join, then exclude the records we don't want from the right side via a where clause.
|
|
SELECT * FROM TableA
FULL OUTER JOIN TableB
ON TableA.name = TableB.name
WHERE TableA.id IS null
OR TableB.id IS null
id name id name
-- ---- -- ----
2 Monkey null null
4 Spaghetti null null
null null 1 Rutabaga
null null 3 Darth Vader
To produce the set of records unique to Table A and Table B, we perform the same full outer join, then exclude the records we don't want from both sides via a where clause.
|
|
There's also a cartesian product or cross join, which as far as I can tell, can't be expressed as a Venn diagram:
SELECT * FROM TableA
CROSS JOIN TableB
This joins "everything to everything", resulting in 4 x 4 = 16 rows, far more than we had in the original sets. If you do the math, you can see why this is a very dangerous join to run against large tables.
分享到:
相关推荐
SQL join 完全用法 SQL join 完全用法 学习sql join
PL/SQL Join实例分析 数据库Join 使用分析比较
sqljoin示例,包含左联右联内联以及外联等连接方式是学习SQL必不可少的知识
手把手教你写 SQL Join 联接 手把手教你写 SQL Join 联接 - Defonds 的专栏 - CSDN博客手把手教你写 SQL Join 联接 - Defonds 的专栏 - CSDN博客
画图解释 SQL join 语句1
SQL Join类型除内join,其他join没匹配数据时数组字段输出为NULL。
表连接 SQL JOIN 速查表
LINQ to SQL语句之Join和Order By部分代码 语句描述:这个例子在From子句中使用外键导航筛选在西雅图的雇员,同时列出其所在地区。这条生成SQL语句为: SELECT [t0].[FirstName], [t0].[LastName], [t2]....
SQL 连接 JOIN 例解
sql语句中join的用法 sql语句中join的用法
SQL Inner Join SQL Left Join SQL Right Join SQL Full Join SQL Union SQL Select Into SQL Create DB SQL Create Table SQL Constraints SQL Not Null SQL Unique SQL Primary Key SQL Foreign Key ...
SQL JOIN 连接 SQL JOIN 子句用于把来自两个或多个表的行结合起来,基于这些表之间的共同字段。 最常见的 JOIN 类型:SQL INNER JOIN(简单的 JOIN)。 SQL INNER JOIN 从多个表中返回满足 JOIN 条件的所有行。 让...
NULL 博文链接:https://kaqi.iteye.com/blog/360493
非常不错使用join on实现数据库字段的连接输出效果。
MySQL join summary graph and code.I just wanna submit a .jpg,
在面试的时候曾3次踩过了sql join的坑,第一次是校招面试的时候,面的是国内某大行的开发岗,被问到会不会使用sql join,当时直接回答的是不会,只会使用简单的sql操作。第二次面试的是另一大行的测试岗,人家直接问...
使用右键菜单实现SQL关联查询,通过本表和外接表的两个字段关联,实现关联后,对两个表进行属性查找。
SQL语句left join/right join/inner join 的用法比较 SQL语句left join/right join/inner join 的用法比较
17. SQL INNER JOIN 关键字 22 18. SQL LEFT JOIN 关键字 23 19. SQL RIGHT JOIN 关键字 24 20. SQL UNION 和 UNION ALL 操作符 26 21. SQL SELECT INTO 语句 28 22. SQL CREATE DATABASE 语句 29 23. SQL CREATE ...
数据库应用技术