Shulou(Shulou.com)06/01 Report--

About SQL SERVER table join query INNER JOIN, LEFT JOIN and RIGHT JOIN, often use ON and WHERE condition query, in the past, sometimes based on feeling, always do not understand, today personally tested, understand some content, share here.

To test, first let's create three tables, and the database depends on its own situation.

Create tables TestJoinOnOrWhere_A, TestJoinOnOrWhere_B, TestJoinOnOrWhere_C

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

The table is created, and then we add some data.

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)

Now let's start testing.

Statement 1:SELECT * FROM dbo.TestJoinOnOrWhere_A AS a LEFT JOIN dbo.TestJoinOnOrWhere_B AS b ON a.id = b.id AND a.value = 1 statement 2:SELECT * FROM dbo.TestJoinOnOrWhere_A AS a LEFT JOIN dbo.TestJoinOnOrWhere_B AS b ON a.id = b.id

Result 1:

Id value id value

-

1 1 1

2 1 2 3

3 2 NULL NULL

Result 2

Id value id value

-

1 1 1

2 1 2 3

3 2 3 4

Query on the Internet, some people say that a.value = 1 does not take effect, in fact, it is not, it has already taken effect, but in the left join query, the left table data will not be affected, only the right table data will take out the left table (a table) Value 1 row according to the a.value = 1 condition, through the results of the above two statements can be seen, then we use the right table filter conditions what will appear? Look at the following statement

Statement 3:SELECT * FROM dbo.TestJoinOnOrWhere_A AS a LEFT JOIN dbo.TestJoinOnOrWhere_B AS b ON a.id = b.id AND b.value = 1

Result 3:

Id value id value

-

1 1 1

2 1 NULL NULL

3 2 NULL NULL

As can be seen from the above results, it only affects the data in the right table.

Statement 4:SELECT * FROM dbo.TestJoinOnOrWhere_A AS a LEFT JOIN dbo.TestJoinOnOrWhere_B AS b ON a.value = 1

Result 4:

Id value id value

-

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

From the results of the above statement, we can see that it only affects the data of the right table (take out all the b table data whose value corresponds to 1)

So in the left join query, the condition after ON will only affect the right table, on the contrary, the right join query will affect the left table data.

What if I use WHERE? Let's take a look at the following sentence

Statement 5:SELECT * FROM dbo.TestJoinOnOrWhere_A AS a LEFT JOIN dbo.TestJoinOnOrWhere_B AS b ON a.id = b.id where a.value = 1 statement 6:SELECT * FROM dbo.TestJoinOnOrWhere_A AS a LEFT JOIN dbo.TestJoinOnOrWhere_B AS b ON a.id = b.id where b.value = 1

Result 5:

Id value id value

-

1 1 1

2 1 2 3

Result 6:

Id value id value

-

1 1 1

As can be seen from the results, the result of this effect is all the tables, which is equivalent to joining the query results through the ON condition, and then taking the overall filter through the conditions behind the WHERE.

How will it affect the ON condition of INNER JOIN? Let's take a look at the execution result of the following statement

Statement 7: SELECT * FROM dbo.TestJoinOnOrWhere_A AS an INNER JOIN dbo.TestJoinOnOrWhere_B AS b ON a.id = b.id AND a.value = 1 statement 8: SELECT * FROM dbo.TestJoinOnOrWhere_A AS an INNER JOIN dbo.TestJoinOnOrWhere_B AS b ON a.id = b.id AND b.value = 1 statement 9: SELECT * FROM dbo.TestJoinOnOrWhere_A AS an INNER JOIN dbo.TestJoinOnOrWhere_B AS b ON a.id = b.id WHERE a.value = 1 statement 10:SELECT * FROM dbo.TestJoinOnOrWhere_A AS an INNER JOIN dbo.TestJoinOnOrWhere_B AS b ON a.id = b.id WHERE b.value = 1

Result 7Accord9:

Id value id value

-

1 1 1

2 1 2 3

Result 8Accord 10:

Id value id value

-

1 1 1

The results of the above query through WHERE and ON are the same, which shows that the ON condition and WHERE condition of INNER JOIN affect the same effect and affect the overall query result.

Let's take a look at the results of the impact of the three-table query on LEFT JOIN on WHERE and ON

Statement 11:SELECT a.id AS a value AS a value value c.id b. Id AS bachelorum b.value AS barium value C.id AS cantilever musec. 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

Statement 12:SELECT a.id AS a value AS a value value c.id b. Id AS bachelorum b.value AS barium value C.id AS cantilever musec. 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

The statement "13:SELECT a.id AS c_value FROM dbo.TestJoinOnOrWhere_A AS a LEFT JOIN dbo.TestJoinOnOrWhere_B AS b ON a.id a. Value AS" is b.id AS billidjournal b.value AS baccalaurevalue c.id AS cymnidmum c.value = b.id AND a.value = 1 LEFT JOIN dbo.TestJoinOnOrWhere_C AS c ON b.id = c.id AND b.value = 1

The statement "14:SELECT a.id AS c_value FROM dbo.TestJoinOnOrWhere_A AS a LEFT JOIN dbo.TestJoinOnOrWhere_B AS b ON a.id a. Value AS" is b.id AS bachelorum b.value AS baccalaurevalueC.id AS cymbidmum 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

Result 11:

A_id a_value b_id b_value c_id c_value

1 1 1

2 1 2 3 2 2

3 2 NULL NULL NULL NULL

Result 12:

A_id a_value b_id b_value c_id c_value

1 1 1

2 1 NULL NULL NULL NULL

3 2 NULL NULL NULL NULL

Result 13:

A_id a_value b_id b_value c_id c_value

1 1 1

2 1 2 3 NULL NULL

3 2 NULL NULL NULL NULL

Result 14:

A_id a_value b_id b_value c_id c_value

1 1 1 NULL NULL

2 1 2 3 2 2

3 2 NULL NULL NULL NULL

From the data query results of the above three tables, we can see that for LEFT JOIN query, the separate table condition of ON will always affect only the right table of the condition table (for example, a.value=1 will affect the rows of table an associated with value field value 1, and will not restrict the data of table a to display only the rows of value=1), while the effect of RIGHT JOIN is just the opposite

When using the ON condition, LEFT JOIN affects the second and third table on the right, and does not affect the leftmost table, so for the three tables, table an is not affected by the ON condition, but only the b or c data after the join query

WHERE is equivalent to the data queried before the WHERE condition is in front of a table, and then the data is filtered by the WHERE condition, so it affects the whole.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.