JOIN ON 常數的用途是什麼?跟放在WHERE一樣嗎?/ FULL JOIN要注意的事項- SQL Server

Posted by: bart30508 | in SQL Server | 8 months, 2 weeks ago |

先說結論:當然不一樣!

直接看範例!

JOIN ON 常數 = JOIN給定的常數與Data Source相同時才會JOIN:

DECLARE @T TABLE (
    Id VARCHAR(1)
    ,Value INT
    )
DECLARE @T1 TABLE (
    Id VARCHAR(1)
    ,Value INT
    )
DECLARE @T2 TABLE (
    Id VARCHAR(1)
    ,Value INT
    )

INSERT INTO @T
VALUES (
    'A'
    ,1
    )
    ,(
    'A'
    ,2
    )
    ,(
    'A'
    ,3
    )

INSERT INTO @T1
VALUES (
    'A'
    ,3
    )
    ,(
    'B'
    ,4
    )
    ,(
    'A'
    ,4
    )

@T/@T1/@T2原始資料:

SELECT * FROM @T
SELECT * FROM @T1
SELECT * FROM @T2

image

JOIN ON 常數

  1. JOIN @T1 t1 ON t0.Id = t1.Id AND t0.Value = 3 SELECT * FROM @T t0 JOIN @T1 t1 ON t0.Id = t1.Id AND t0.Value = 3

image

如此就代表著, @T.Id = A 會JOIN @T1.Id = A,但只發生在@T.Id = 3處

  1. JOIN @T1 t1 ON t0.Id = t1.Id AND t0.Value = 3 AND t1.Value = 4

    SELECT * FROM @T t0 JOIN @T1 t1 ON t0.Id = t1.Id AND t0.Value = 3 AND t1.Value = 4

image

如此就代表著, @T.Id = A 會JOIN @T1.Id = A,但只發生在@T.Id = 3處且@T1.Id = 4處

"可以想成,強制讓@T.Id = 3 能夠JOIN @T1.Id = 4"

也就是ON 是 JOIN 連接的條件,只有裡面那些條件都符合,才能連接;而WHERE 是拿來篩選的 => ON 條件拿來連接資料的條件;WHERE 條件拿來篩選資料的條件

看FULL JOIN的資料更能體會其用法:

SELECT * FROM @T t 
FULL JOIN
@T1 t1
ON t.Id=t1.Id

SELECT * FROM @T t 
FULL JOIN
@T1 t1
ON t.Id=t1.Id
AND t.Value = 3
AND t1.Value = 4

SELECT * FROM @T t 
FULL JOIN
@T1 t1
ON t.Id=t1.Id
AND t.Value IN (2,3)

SELECT * FROM @T t 
FULL JOIN
@T1 t1
ON t.Id=t1.Id
WHERE t1.Value = 4

image

<註> 利用FULL JOIN 做篩選時,要特別小心原本就預計要排除的NULL欄位,因為可能會錯誤排除那些完全不在t able但卻在t1 Table的資料。

如下,想要找出t不是NULL的,卻因為FULL JOIN後造成(B,4)也被刪除了。

SELECT * FROM @t t
FULL JOIN
@t1 t1
ON t.Id=t1.Id

SELECT * FROM @t t
FULL JOIN
@t1 t1
ON t.Id=t1.Id
WHERE t.Value IS NOT NULL

image

Currently unrated
 or 

Subscribe

* indicates required

Recent Posts

Archive

2022
2021

Categories

Apache 1

Data Science 2

Dbfit 1

Design Pattern 1

Devops 4

DigitalOcean 1

Django 1

English 3

Excel 5

Flask 3

Git 1

HackMD 1

Heroku 1

Html/Css 1

Linux 4

Machine Learning 2

Manufacture 1

Mezzanine 18

Oracle 1

Postgresql 7

PowerBI 4

Powershell 4

Python 21

SEO 2

SQL Server 52

SQLite 1

Windows 1

database 8

work-experience 1

其他 1

自我成長 1

資料工程 1

Tags

SEO(1) Github(2) Title Tag(2) ML(1) 李宏毅(1) SQL Server(18) Tempdb(1) SSMS(1) Windows(1) 自我成長(2) Excel(1) python Flask(1) python(5) Flask(2)

Authors

bart30508 (147)

Feeds

RSS / Atom

JOIN ON 常數的用途是什麼?跟放在WHERE一樣嗎?/ FULL JOIN要注意的事項- SQL Server

© COPYRIGHT 2011-2022. Max的文藝復興. ALL RIGHT RESERVED.