LEFT JOIN ON AND/WHERE

Question

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

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

-- 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

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

-- 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

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

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

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

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

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

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

Conclusion

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

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

REF

Stackoverflow