What's different between INTERSECT and JOIN?
What's different between INTERSECT and JOIN?
1.INTERSECT just compares 2 sets and picks only distinct equivalent values from both sets.
It's important to note that NULL marks are considered as equals in INTERSECT set operator.
Also sets should contain equal number of columns with implicitly convertible types.
- Under Join i guess you mean INNER JOIN? INNER JOIN will return you rows where matching predicate will return TRUE. I.E. if there are NULL marks in both tables those rows will not be returned because NULL <> NULL in SQL.
Also INTERSECT is just comparing SETS on all attributes. Their types should be implicitly convertible to each other. While in join you can compare on any predicate and different types of sets. It is not mandatory to return just rows where there are matches. For example you can produce cartesian product in join.
Select * from Table1
Join Table2 on 1 = 1
https://www.quora.com/What-is-the-difference-between-inner-join-and-intersect
There are 3 differences
Intersect is an operator and Inner join is a type of join.
Intersect can return matching null values but inner join can't.
Intersect doesn't return any duplicate values but inner join returns duplicate values if it's present in the tables.
Try the following, for example:
CREATE TABLE a (id INT);
CREATE TABLE b (id INT);
INSERT INTO a VALUES (1), (NULL), (2);
INSERT INTO b VALUES (1), (NULL), (3), (1);
SELECT a.id
FROM
a INNER JOIN b
ON a.id
= b.id
;
output
1
1
SELECT id FROM a
INTERSECT
SELECT id FROM b;
Output
Null
1
相关文章
- Your branch is behind 'origin/master' by N commits, and can be fast-forwarded 解决方法
- There are inconsistent line endings in the 'xxx' script. Some are Mac OS X (UNIX) and some are Windows.问题解决
- [HTML 5 Performance] Optimize Cross-browser Images with webp and the 'picture' Element
- [Tools] Unlock TypeScript's Features in Vanilla JS with @ts-check and JSDoc
- [CSS3] The different of Background-size between 'cover' and 'contain'
- [Angular] New async 'as' syntax and ngIf.. else
- [HTML 5 Performance] Optimize Cross-browser Images with webp and the 'picture' Element
- [CSS3] The different of Background-size between 'cover' and 'contain'
- [Angular] Dynamic component's instance and sorting
- 「简要翻译」Apple :UI Design Dos and Don'ts
- CrazySNS has on line - And you'll see why 1984 won't be like "1984."
- 「翻译」Photoshop CC 2015 is copying Sketch and it's a good thing
- Atitit. Async await 优缺点 异步编程的原理and实现 java c# php
- Atitit.编程语言and 自然语言的比较and 编程语言未来的发展
- Atitit.播放系统规划新版本 and 最近版本回顾 v3 pbf.doc
- Atitit.ati dwr的原理and设计 attilax 总结 java php 版本
- 笔记:Why don't you pull up a chair and give this lifestyle a try?
- git: Your branch and 'origin/master' have diverged