sql語句中l(wèi)eft join和inner join中的on與where的區(qū)別分析

關(guān)于SQL SERVER的表聯(lián)接查詢INNER JOIN 、LEFT JOIN和RIGHT JOIN，經(jīng)常會(huì)用到ON和WHERE的條件查詢，以前用的時(shí)候有時(shí)是憑感覺的，總是沒有搞清楚，今日親自測(cè)試了下，理解到了一些內(nèi)容，在此分享。

要測(cè)試，首先我們來創(chuàng)建三張表，數(shù)據(jù)庫(kù)就根據(jù)自己的情況而定

創(chuàng)建表TestJoinOnOrWhere_A、TestJoinOnOrWhere_B、TestJoinOnOrWhere_C

/****** Object:  Table [dbo].[TestJoinOnOrWhere_A]    Script Date: 2015/4/3 14:34:41 ******/CREATE TABLE [dbo].[TestJoinOnOrWhere_A](    [id] [int] NULL,    [value] [int] NULL) ON [PRIMARY]GO/****** Object:  Table [dbo].[TestJoinOnOrWhere_B]    Script Date: 2015/4/3 14:34:41 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[TestJoinOnOrWhere_B](    [id] [int] NULL,    [value] [int] NULL) ON [PRIMARY]GO/****** Object:  Table [dbo].[TestJoinOnOrWhere_C]    Script Date: 2015/4/3 14:34:41 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[TestJoinOnOrWhere_C](    [id] [int] NULL,    [value] [int] NULL) ON [PRIMARY]

表創(chuàng)建好了然后我們添加幾條數(shù)據(jù)

INSERT [dbo].[TestJoinOnOrWhere_A] ([id], [value]) VALUES (1, 1)INSERT [dbo].[TestJoinOnOrWhere_A] ([id], [value]) VALUES (2, 1)INSERT [dbo].[TestJoinOnOrWhere_A] ([id], [value]) VALUES (3, 2)INSERT [dbo].[TestJoinOnOrWhere_B] ([id], [value]) VALUES (1, 1)INSERT [dbo].[TestJoinOnOrWhere_B] ([id], [value]) VALUES (2, 3)INSERT [dbo].[TestJoinOnOrWhere_B] ([id], [value]) VALUES (3, 4)INSERT [dbo].[TestJoinOnOrWhere_C] ([id], [value]) VALUES (1, 1)INSERT [dbo].[TestJoinOnOrWhere_C] ([id], [value]) VALUES (2, 2)INSERT [dbo].[TestJoinOnOrWhere_C] ([id], [value]) VALUES (3, 3)

 現(xiàn)在我們開始測(cè)試

語句1：SELECT * FROM dbo.TestJoinOnOrWhere_A AS a LEFT JOIN dbo.TestJoinOnOrWhere_B AS b ON a.id = b.id AND a.value = 1語句2：SELECT * FROM dbo.TestJoinOnOrWhere_A AS a LEFT JOIN dbo.TestJoinOnOrWhere_B AS b ON a.id = b.id

結(jié)果1：

id   value  id    value

-------------------------------
1    1       1     1
2    1       2     3
3    2      NULL NULL

結(jié)果2

id   value  id    value

-------------------------------
1    1       1     1
2    1       2     3
3    2       3     4

在網(wǎng)上查詢到，有的人說a.value = 1沒有生效，其實(shí)不然，它已經(jīng)生效，只是在左聯(lián)接查詢時(shí)，左表的數(shù)據(jù)是不會(huì)受影響，只有右表的數(shù)據(jù)會(huì)根據(jù)a.value = 1條件取出左表(a表)Value為1的行，通過上面兩個(gè)語句的結(jié)果就可以看出，那么我們用右表篩選條件會(huì)出現(xiàn)什么呢？看看下面語句

語句3：SELECT * FROM dbo.TestJoinOnOrWhere_A AS a LEFT JOIN dbo.TestJoinOnOrWhere_B AS b ON a.id = b.id AND b.value = 1

結(jié)果3：

id   value  id    value

-------------------------------
1    1       1     1
2    1       NULL NULL
3    2       NULL NULL

以上結(jié)果看出，也只是影響了右表的數(shù)據(jù)

語句4：SELECT * FROM dbo.TestJoinOnOrWhere_A AS a LEFT JOIN dbo.TestJoinOnOrWhere_B AS b ON  a.value = 1

結(jié)果4：

id   value  id    value

-------------------------------
1    1       1     1
1    1       2     3
1    1       3     4
2    1       1     1
2    1       2     3
2    1       3     4
3    2       NULL NULL

從上面語句結(jié)果看出，也只影響了右表的數(shù)據(jù)（取出所有a表value對(duì)應(yīng)為1的b表數(shù)據(jù)）

所以在左聯(lián)接查詢時(shí)ON后面的條件只會(huì)影響右表，相反右聯(lián)接查詢影響的就是左邊的表數(shù)據(jù)

如果用WHERE呢？我們看下下面的語句

語句5：SELECT * FROM dbo.TestJoinOnOrWhere_A AS a LEFT JOIN dbo.TestJoinOnOrWhere_B AS b ON a.id = b.id where a.value = 1語句6：SELECT * FROM dbo.TestJoinOnOrWhere_A AS a LEFT JOIN dbo.TestJoinOnOrWhere_B AS b ON a.id = b.id where b.value = 1

結(jié)果5:

id   value  id    value

-------------------------------
1    1       1     1
2    1       2     3

結(jié)果6:

id   value  id    value

-------------------------------
1    1       1     1

可以從結(jié)果看出，這個(gè)影響的結(jié)果就是全部的表，就相當(dāng)于通過ON條件聯(lián)接查詢查詢的結(jié)果，然后通過WHERE后面的條件取總體篩選

對(duì)于INNER JOIN 的ON條件會(huì)怎樣影響呢？先看下面語句執(zhí)行結(jié)果

語句7：  SELECT * FROM dbo.TestJoinOnOrWhere_A AS a INNER JOIN dbo.TestJoinOnOrWhere_B AS b ON a.id = b.id AND a.value = 1語句8：  SELECT * FROM dbo.TestJoinOnOrWhere_A AS a INNER JOIN dbo.TestJoinOnOrWhere_B AS b ON a.id = b.id AND b.value = 1語句9：  SELECT * FROM dbo.TestJoinOnOrWhere_A AS a INNER JOIN dbo.TestJoinOnOrWhere_B AS b ON a.id = b.id WHERE a.value = 1語句10：SELECT * FROM dbo.TestJoinOnOrWhere_A AS a INNER JOIN dbo.TestJoinOnOrWhere_B AS b ON a.id = b.id WHERE b.value = 1

結(jié)果7/9:

id   value  id    value

-------------------------------
1    1       1     1
2    1       2     3

結(jié)果8/10:

id   value  id    value

-------------------------------
1    1       1     1

上面通過WHERE和ON查詢出來的結(jié)果是一樣的，由此可看出，INNER JOIN 的ON條件和WHERE條件影響的都是一個(gè)效果，影響整體的查詢結(jié)果。

下面我們?cè)賮砜聪聦?duì)于LEFT JOIN的三表查詢對(duì)于WHERE和ON影響的結(jié)果

       語句11：SELECT a.id AS a_id,a.value AS a_value,b.id AS b_id,b.value AS b_value,c.id AS c_id,c.value AS c_value FROM dbo.TestJoinOnOrWhere_A AS a LEFT JOIN   dbo.TestJoinOnOrWhere_B AS b ON a.id = b.id AND a.value = 1  LEFT JOIN dbo.TestJoinOnOrWhere_C AS c ON b.id = c.id
       語句12：SELECT a.id AS a_id,a.value AS a_value,b.id AS b_id,b.value AS b_value,c.id AS c_id,c.value AS c_value FROM dbo.TestJoinOnOrWhere_A AS a LEFT JOIN  dbo.TestJoinOnOrWhere_B AS b ON a.id = b.id AND b.value = 1  LEFT JOIN dbo.TestJoinOnOrWhere_C AS c ON b.id = c.id 
       語句13：SELECT a.id AS a_id,a.value AS a_value,b.id AS b_id,b.value AS b_value,c.id AS c_id,c.value AS c_value FROM dbo.TestJoinOnOrWhere_A AS a LEFT JOIN  dbo.TestJoinOnOrWhere_B AS b ON a.id = b.id AND a.value = 1  LEFT JOIN dbo.TestJoinOnOrWhere_C AS c ON b.id = c.id AND b.value = 1
       語句14：SELECT a.id AS a_id,a.value AS a_value,b.id AS b_id,b.value AS b_value,c.id AS c_id,c.value AS c_value FROM dbo.TestJoinOnOrWhere_A AS a LEFT JOIN  dbo.TestJoinOnOrWhere_B AS b ON a.id = b.id AND a.value = 1  LEFT JOIN dbo.TestJoinOnOrWhere_C AS c ON b.id = c.id AND c.value = 2

結(jié)果11：

a_id  a_value  b_id  b_value  c_id   c_value

-----------------------------------------------------
1     1           1        1          1       1
2     1           2        3          2       2
3     2           NULL   NULL    NULL  NULL

結(jié)果12：

a_id  a_value  b_id  b_value  c_id   c_value

-----------------------------------------------------
1     1           1        1          1       1
2     1           NULL   NULL    NULL  NULL
3     2           NULL   NULL    NULL  NULL

結(jié)果13：

a_id  a_value  b_id  b_value  c_id   c_value

-----------------------------------------------------
1     1           1        1          1       1
2     1           2        3          NULL  NULL
3     2           NULL   NULL    NULL  NULL

結(jié)果14：

a_id  a_value  b_id  b_value  c_id   c_value

-----------------------------------------------------
1     1           1        1          NULL  NULL
2     1           2        3          2        2
3     2           NULL   NULL    NULL  NULL

      通過以上三表數(shù)據(jù)查詢結(jié)果，可以看出，LEFT JOIN 查詢，對(duì)于ON的單獨(dú)表?xiàng)l件始終只會(huì)影響條件表的右表（如，a.value=1會(huì)影響b表關(guān)聯(lián)的a表value字段值為1的行，并不會(huì)限制a表的數(shù)據(jù)只顯示value=1的行），RIGHT JOIN 影響效果恰恰相反

      在使用ON條件時(shí)LEFT JOIN影響的是右側(cè)的第二張第三張表，并不會(huì)對(duì)最左側(cè)的表影響，所以對(duì)于a,b,c,三張表，a表數(shù)據(jù)是不受ON條件影響的，只會(huì)影響聯(lián)接查詢后的b或c數(shù)據(jù)

而WHERE就相當(dāng)于在WHERE條件之前查詢的數(shù)據(jù)當(dāng)著一個(gè)表，然后通過WHERE條件進(jìn)行篩選數(shù)據(jù)，所以影響的是整體。