JeTbahrZ

1DROP TABLE IF EXISTS #left;
2DROP TABLE IF EXISTS #right;
3
4CREATE TABLE #left (
5    a       char(4) NOT NULL,
6    b       char(2) NOT NULL,
7    c       varchar(13) NOT NULL,
8    d       bit NOT NULL,
9    e       char(4) NOT NULL,
10    f       char(25) NULL,
11    g       char(25) NOT NULL,
12    h       char(25) NULL
13    --- and a few other columns
14);
15
16CREATE UNIQUE CLUSTERED INDEX IX ON #left (a, b, c, d, e, f, g, h)
17
18UPDATE STATISTICS #left WITH ROWCOUNT=63800000, PAGECOUNT=186000;
19
20CREATE TABLE #right (
21    a       char(4) NOT NULL,
22    b       char(2) NOT NULL,
23    c       varchar(13) NOT NULL,
24    d       bit NOT NULL,
25    e       char(4) NOT NULL,
26    f       char(25) NULL,
27    g       char(25) NOT NULL,
28    h       char(25) NULL
29    --- and a few other columns
30);
31
32CREATE CLUSTERED INDEX IX ON #right (a, b, c, d, e, f, g, h)
33
34UPDATE STATISTICS #right WITH ROWCOUNT=55700000, PAGECOUNT=128000;
35	
36SELECT *
37FROM #left AS l
38LEFT JOIN #right AS r ON
39    l.a=r.a AND
40    l.b=r.b AND
41    l.c=r.c AND
42    l.d=r.d AND
43    l.e=r.e AND
44    l.f=r.f AND
45    l.g=r.g AND
46    l.h=r.h
47WHERE l.a='2018';
48	
49SELECT *
50FROM #left AS l
51LEFT JOIN #right AS r ON
52    l.c=r.c AND     -- used to be third
53    l.a=r.a AND     -- used to be first
54    l.b=r.b AND     -- used to be second
55    l.d=r.d AND
56    l.e=r.e AND
57    l.f=r.f AND
58    l.g=r.g AND
59    l.h=r.h
60WHERE l.a='2018';