LEFT JOIN ON AND/WHERE

LEFT JOIN ON AND/WHERE

Question

这两个SQL查询的结果相同吗?

SQL
1
2
SELECT * FROM TableA a LEFT JOIN TableB b ON  b.AId =a.TableAId AND a.TableAId =1
SELECT * FROM TableA a LEFT JOIN TableB b ON b.AId =a.TableAId WHERE a.TableAId = 1

Test SQL

Creat Database

TEST Database
1
2
3
4
5
6
7
8
9
10
11
12
-- Create a new database called 'TEST' for XKA
-- Connect to the 'master' database to run this snippet
USE master
GO
-- Create the new database if it does not exist already
IF NOT EXISTS (
SELECT name
FROM sys.databases
WHERE name = N'TEST'
)
CREATE DATABASE TEST
GO

Creat Table

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
USE TEST

-- Create a new table called 'TableA' in schema 'TEST'
-- Drop the table if it already exists
IF OBJECT_ID('TEST.TableA', 'U') IS NOT NULL
DROP TABLE TEST.TableA
GO
-- Create the table in the specified schema
CREATE TABLE TableA
(
TableAId INT NOT NULL PRIMARY KEY,
-- primary key column
Code [NVARCHAR](50) NOT NULL,
Name [NVARCHAR](50) NOT NULL
-- specify more columns here
);
GO

-- Create a new table called 'TableB' in schema 'SchemaName'
-- Drop the table if it already exists
IF OBJECT_ID('TEST.TableB', 'U') IS NOT NULL
DROP TABLE TableB
GO
-- Create the table in the specified schema
CREATE TABLE TableB
(
TableBId INT NOT NULL PRIMARY KEY,
-- primary key column
AId INT NOT NULL,
Code [NVARCHAR](50) NOT NULL,
Name [NVARCHAR](50) NOT NULL
-- specify more columns here
);
GO

Creat Data

Creat Data
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
-- Insert rows into table 'TableA'
INSERT INTO TableA
VALUES
( -- first row: values for the columns in the list above
1, 'A', 'A'
),
( -- second row: values for the columns in the list above
2, 'B', 'B'
),
(3, 'C', 'C')
-- add more rows here
GO

-- Insert rows into table 'TableB'
INSERT INTO TableB
VALUES
( -- columns to insert data into
1, 1, 'C', 'C'
),
( -- first row: values for the columns in the list above
2, 1, 'D', 'D'
),
( -- second row: values for the columns in the list above
3, 2, 'E', 'E'
),
( -- second row: values for the columns in the list above
4, 2, 'D', 'D'
)
-- add more rows here
GO

Query Result

1
SELECT * FROM TableA a LEFT JOIN TableB b ON  b.AId =a.TableAId
TableAId Code Name TableBId AId Code Name
1 A A 1 1 C C
1 A A 2 1 D D
2 B B 3 2 E E
2 B B 4 2 D D
3 C C NULL NULL NULL NULL
1
SELECT * FROM TableA a LEFT JOIN TableB b ON  b.AId =a.TableAId AND a.code ='H'
TableAId Code Name TableBId AId Code Name
1 A A NULL NULL NULL NULL
2 B B NULL NULL NULL NULL
3 C C NULL NULL NULL NULL
1
SELECT * FROM TableA a LEFT JOIN TableB b ON  b.AId =a.TableAId AND a.TableAId =1
TableAId Code Name TableBId AId Code Name
1 A A 1 1 C C
1 A A 2 1 D D
2 B B NULL NULL NULL NULL
3 C C NULL NULL NULL NULL
1
SELECT * FROM TableA a LEFT JOIN TableB b ON  b.AId =a.TableAId AND b.AId = 1
TableAId Code Name TableBId AId Code Name
1 A A 1 1 C C
1 A A 2 1 D D
2 B B NULL NULL NULL NULL
3 C C NULL NULL NULL NULL
1
SELECT * FROM TableA a LEFT JOIN TableB b ON  b.AId =a.TableAId WHERE a.TableAId = 1
TableAId Code Name TableBId AId Code Name
1 A A 1 1 C C
1 A A 2 1 D D
1
SELECT * FROM TableA a LEFT JOIN TableB b ON  b.AId =a.TableAId WHERE b.AId =1
TableAId Code Name TableBId AId Code Name
1 A A 1 1 C C
1 A A 2 1 D D

ALL

IMAGE

Conclusion

连接多张表查询时会生成临时表,使用LEFT JOIN

  • ON 是生成临时表的条件,不管ON的结果是否为真,都会返回左边表的所有数据
  • AND 是在ON的基础上进行过滤,但是仍会返回左边表的所有数据
  • WHERE 是在临时表生成后,进行过滤,相当于对单表进行过滤

REF

Stackoverflow

评论