Left Outer Join Check

Question

Post reply

Left Outer Join Check

carlton 84646

Ten Centuries

Points: 1026

September 20, 2022 at 8:35 am

Go to Answer

#4089697

Hi Community,

Can someone please check my T-SQL code and let me know if I'm getting the right result.

I'm doing a left outer join on the following tables and I'm getting 18 records back, but no missing data.

This is confusing, because in the circuits table there are 77 records and in the races table there are over a 1000 records, therefore I'm sure I should get some nulls, but I'm not getting any null records. In anycase, when I compare my result to the result from the Udemy course that I'm taking on SQL, I should be getting nulls on fields 'Sepang International Circuit' and 'Istanbul Park'.

Can someone take a look at my code and let me know if I'm going wrong?

SELECT
  circuits.name AS circuit_name
 ,circuits.location
 ,circuits.country
 ,races.name AS race_name
 ,races.round
FROM dbo.circuits
RIGHT OUTER JOIN dbo.races
  ON circuits.circuit_id = races.circuit_id
WHERE circuits.circuit_id < 70
AND races.race_year = 2019

Data

Circuits

CREATE TABLE circuits (
    circuit_id int,
    circuit_ref nvarchar(256),
    name nvarchar(256),
    location nvarchar(256),
    country nvarchar(256),
    latitude float,
    longitude float,
    altitude int,
    ingestion_date datetime)

INSERT circuits VALUES
(1,N'albert_park',N'Albert Park Grand Prix Circuit',N'Melbourne',N'Australia',-37.8497,144.968,10,CONVERT(DATETIME, '2022-09-19 22:34:26.000', 121)),
(2,N'sepang',N'Sepang International Circuit',N'Kuala Lumpur',N'Malaysia',2.76083,101.738,18,CONVERT(DATETIME, '2022-09-19 22:34:26.000', 121)),
(3,N'bahrain',N'Bahrain International Circuit',N'Sakhir',N'Bahrain',26.0325,50.5106,7,CONVERT(DATETIME, '2022-09-19 22:34:26.000', 121)),
(4,N'catalunya',N'Circuit de Barcelona-Catalunya',N'Montmeló',N'Spain',41.57,2.26111,109,CONVERT(DATETIME, '2022-09-19 22:34:26.000', 121)),
(5,N'istanbul',N'Istanbul Park',N'Istanbul',N'Turkey',40.9517,29.405,130,CONVERT(DATETIME, '2022-09-19 22:34:26.000', 121)),
(6,N'monaco',N'Circuit de Monaco',N'Monte-Carlo',N'Monaco',43.7347,7.42056,7,CONVERT(DATETIME, '2022-09-19 22:34:26.000', 121)),
(7,N'villeneuve',N'Circuit Gilles Villeneuve',N'Montreal',N'Canada',45.5,-73.5228,13,CONVERT(DATETIME, '2022-09-19 22:34:26.000', 121)),
(8,N'magny_cours',N'Circuit de Nevers Magny-Cours',N'Magny Cours',N'France',46.8642,3.16361,228,CONVERT(DATETIME, '2022-09-19 22:34:26.000', 121)),
(9,N'silverstone',N'Silverstone Circuit',N'Silverstone',N'UK',52.0786,-1.01694,153,CONVERT(DATETIME, '2022-09-19 22:34:26.000', 121)),
(10,N'hockenheimring',N'Hockenheimring',N'Hockenheim',N'Germany',49.3278,8.56583,103,CONVERT(DATETIME, '2022-09-19 22:34:26.000', 121)),
(11,N'hungaroring',N'Hungaroring',N'Budapest',N'Hungary',47.5789,19.2486,264,CONVERT(DATETIME, '2022-09-19 22:34:26.000', 121)),
(12,N'valencia',N'Valencia Street Circuit',N'Valencia',N'Spain',39.4589,-0.331667,4,CONVERT(DATETIME, '2022-09-19 22:34:26.000', 121)),
(13,N'spa',N'Circuit de Spa-Francorchamps',N'Spa',N'Belgium',50.4372,5.97139,401,CONVERT(DATETIME, '2022-09-19 22:34:26.000', 121)),
(14,N'monza',N'Autodromo Nazionale di Monza',N'Monza',N'Italy',45.6156,9.28111,162,CONVERT(DATETIME, '2022-09-19 22:34:26.000', 121)),
(15,N'marina_bay',N'Marina Bay Street Circuit',N'Marina Bay',N'Singapore',1.2914,103.864,18,CONVERT(DATETIME, '2022-09-19 22:34:26.000', 121)),
(16,N'fuji',N'Fuji Speedway',N'Oyama',N'Japan',35.3717,138.927,583,CONVERT(DATETIME, '2022-09-19 22:34:26.000', 121)),
(17,N'shanghai',N'Shanghai International Circuit',N'Shanghai',N'China',31.3389,121.22,5,CONVERT(DATETIME, '2022-09-19 22:34:26.000', 121)),
(18,N'interlagos',N'Autódromo José Carlos Pace',N'São Paulo',N'Brazil',-23.7036,-46.6997,785,CONVERT(DATETIME, '2022-09-19 22:34:26.000', 121)),
(19,N'indianapolis',N'Indianapolis Motor Speedway',N'Indianapolis',N'USA',39.795,-86.2347,223,CONVERT(DATETIME, '2022-09-19 22:34:26.000', 121)),
(20,N'nurburgring',N'Nürburgring',N'Nürburg',N'Germany',50.3356,6.9475,578,CONVERT(DATETIME, '2022-09-19 22:34:26.000', 121)),
(21,N'imola',N'Autodromo Enzo e Dino Ferrari',N'Imola',N'Italy',44.3439,11.7167,37,CONVERT(DATETIME, '2022-09-19 22:34:26.000', 121)),
(22,N'suzuka',N'Suzuka Circuit',N'Suzuka',N'Japan',34.8431,136.541,45,CONVERT(DATETIME, '2022-09-19 22:34:26.000', 121)),
(23,N'osterreichring',N'A1-Ring',N'Spielburg',N'Austria',47.2197,14.7647,678,CONVERT(DATETIME, '2022-09-19 22:34:26.000', 121)),
(24,N'yas_marina',N'Yas Marina Circuit',N'Abu Dhabi',N'UAE',24.4672,54.6031,3,CONVERT(DATETIME, '2022-09-19 22:34:26.000', 121)),
(25,N'galvez',N'Autódromo Juan y Oscar Gálvez',N'Buenos Aires',N'Argentina',-34.6943,-58.4593,8,CONVERT(DATETIME, '2022-09-19 22:34:26.000', 121)),
(26,N'jerez',N'Circuito de Jerez',N'Jerez de la Frontera',N'Spain',36.7083,-6.03417,37,CONVERT(DATETIME, '2022-09-19 22:34:26.000', 121)),
(27,N'estoril',N'Autódromo do Estoril',N'Estoril',N'Portugal',38.7506,-9.39417,130,CONVERT(DATETIME, '2022-09-19 22:34:26.000', 121)),
(28,N'okayama',N'Okayama International Circuit',N'Okayama',N'Japan',34.915,134.221,266,CONVERT(DATETIME, '2022-09-19 22:34:26.000', 121)),
(29,N'adelaide',N'Adelaide Street Circuit',N'Adelaide',N'Australia',-34.9272,138.617,58,CONVERT(DATETIME, '2022-09-19 22:34:26.000', 121)),
(30,N'kyalami',N'Kyalami',N'Midrand',N'South Africa',-25.9894,28.0767,1460,CONVERT(DATETIME, '2022-09-19 22:34:26.000', 121)),
(31,N'donington',N'Donington Park',N'Castle Donington',N'UK',52.8306,-1.37528,88,CONVERT(DATETIME, '2022-09-19 22:34:26.000', 121)),
(32,N'rodriguez',N'Autódromo Hermanos Rodríguez',N'Mexico City',N'Mexico',19.4042,-99.0907,2227,CONVERT(DATETIME, '2022-09-19 22:34:26.000', 121)),
(33,N'phoenix',N'Phoenix street circuit',N'Phoenix',N'USA',33.4479,-112.075,345,CONVERT(DATETIME, '2022-09-19 22:34:26.000', 121)),
(34,N'ricard',N'Circuit Paul Ricard',N'Le Castellet',N'France',43.2506,5.79167,432,CONVERT(DATETIME, '2022-09-19 22:34:26.000', 121)),
(35,N'yeongam',N'Korean International Circuit',N'Yeongam County',N'Korea',34.7333,126.417,0,CONVERT(DATETIME, '2022-09-19 22:34:26.000', 121)),
(36,N'jacarepagua',N'Autódromo Internacional Nelson Piquet',N'Rio de Janeiro',N'Brazil',-22.9756,-43.395,1126,CONVERT(DATETIME, '2022-09-19 22:34:26.000', 121)),
(37,N'detroit',N'Detroit Street Circuit',N'Detroit',N'USA',42.3298,-83.0401,177,CONVERT(DATETIME, '2022-09-19 22:34:26.000', 121)),
(38,N'brands_hatch',N'Brands Hatch',N'Kent',N'UK',51.3569,0.263056,145,CONVERT(DATETIME, '2022-09-19 22:34:26.000', 121)),
(39,N'zandvoort',N'Circuit Park Zandvoort',N'Zandvoort',N'Netherlands',52.3888,4.54092,6,CONVERT(DATETIME, '2022-09-19 22:34:26.000', 121)),
(40,N'zolder',N'Zolder',N'Heusden-Zolder',N'Belgium',50.9894,5.25694,36,CONVERT(DATETIME, '2022-09-19 22:34:26.000', 121)),
(41,N'dijon',N'Dijon-Prenois',N'Dijon',N'France',47.3625,4.89913,484,CONVERT(DATETIME, '2022-09-19 22:34:26.000', 121)),
(42,N'dallas',N'Fair Park',N'Dallas',N'USA',32.7774,-96.7587,139,CONVERT(DATETIME, '2022-09-19 22:34:26.000', 121)),
(43,N'long_beach',N'Long Beach',N'California',N'USA',33.7651,-118.189,12,CONVERT(DATETIME, '2022-09-19 22:34:26.000', 121)),
(44,N'las_vegas',N'Las Vegas Street Circuit',N'Nevada',N'USA',36.1162,-115.174,639,CONVERT(DATETIME, '2022-09-19 22:34:26.000', 121)),
(45,N'jarama',N'Jarama',N'Madrid',N'Spain',40.6171,-3.58558,609,CONVERT(DATETIME, '2022-09-19 22:34:26.000', 121)),
(46,N'watkins_glen',N'Watkins Glen',N'New York State',N'USA',42.3369,-76.9272,485,CONVERT(DATETIME, '2022-09-19 22:34:26.000', 121)),
(47,N'anderstorp',N'Scandinavian Raceway',N'Anderstorp',N'Sweden',57.2653,13.6042,153,CONVERT(DATETIME, '2022-09-19 22:34:26.000', 121)),
(48,N'mosport',N'Mosport International Raceway',N'Ontario',N'Canada',44.0481,-78.6756,332,CONVERT(DATETIME, '2022-09-19 22:34:26.000', 121)),
(49,N'montjuic',N'Montjuïc',N'Barcelona',N'Spain',41.3664,2.15167,79,CONVERT(DATETIME, '2022-09-19 22:34:26.000', 121)),
(50,N'nivelles',N'Nivelles-Baulers',N'Brussels',N'Belgium',50.6211,4.32694,139,CONVERT(DATETIME, '2022-09-19 22:34:26.000', 121)),
(51,N'charade',N'Charade Circuit',N'Clermont-Ferrand',N'France',45.7472,3.03889,790,CONVERT(DATETIME, '2022-09-19 22:34:26.000', 121)),
(52,N'tremblant',N'Circuit Mont-Tremblant',N'Quebec',N'Canada',46.1877,-74.6099,214,CONVERT(DATETIME, '2022-09-19 22:34:26.000', 121)),
(53,N'essarts',N'Rouen-Les-Essarts',N'Rouen',N'France',49.3306,1.00458,81,CONVERT(DATETIME, '2022-09-19 22:34:26.000', 121)),
(54,N'lemans',N'Le Mans',N'Le Mans',N'France',47.95,0.224231,67,CONVERT(DATETIME, '2022-09-19 22:34:26.000', 121)),
(55,N'reims',N'Reims-Gueux',N'Reims',N'France',49.2542,3.93083,88,CONVERT(DATETIME, '2022-09-19 22:34:26.000', 121)),
(56,N'george',N'Prince George Circuit',N'Eastern Cape Province',N'South Africa',-33.0486,27.8736,15,CONVERT(DATETIME, '2022-09-19 22:34:26.000', 121)),
(57,N'zeltweg',N'Zeltweg',N'Styria',N'Austria',47.2039,14.7478,676,CONVERT(DATETIME, '2022-09-19 22:34:26.000', 121)),
(58,N'aintree',N'Aintree',N'Liverpool',N'UK',53.4769,-2.94056,20,CONVERT(DATETIME, '2022-09-19 22:34:26.000', 121)),
(59,N'boavista',N'Circuito da Boavista',N'Oporto',N'Portugal',41.1705,-8.67325,28,CONVERT(DATETIME, '2022-09-19 22:34:26.000', 121)),
(60,N'riverside',N'Riverside International Raceway',N'California',N'USA',33.937,-117.273,470,CONVERT(DATETIME, '2022-09-19 22:34:26.000', 121)),
(61,N'avus',N'AVUS',N'Berlin',N'Germany',52.4806,13.2514,53,CONVERT(DATETIME, '2022-09-19 22:34:26.000', 121)),
(62,N'monsanto',N'Monsanto Park Circuit',N'Lisbon',N'Portugal',38.7197,-9.20306,158,CONVERT(DATETIME, '2022-09-19 22:34:26.000', 121)),
(63,N'sebring',N'Sebring International Raceway',N'Florida',N'USA',27.4547,-81.3483,18,CONVERT(DATETIME, '2022-09-19 22:34:26.000', 121)),
(64,N'ain-diab',N'Ain Diab',N'Casablanca',N'Morocco',33.5786,-7.6875,19,CONVERT(DATETIME, '2022-09-19 22:34:26.000', 121)),
(65,N'pescara',N'Pescara Circuit',N'Pescara',N'Italy',42.475,14.1508,129,CONVERT(DATETIME, '2022-09-19 22:34:26.000', 121)),
(66,N'bremgarten',N'Circuit Bremgarten',N'Bern',N'Switzerland',46.9589,7.40194,551,CONVERT(DATETIME, '2022-09-19 22:34:26.000', 121)),
(67,N'pedralbes',N'Circuit de Pedralbes',N'Barcelona',N'Spain',41.3903,2.11667,85,CONVERT(DATETIME, '2022-09-19 22:34:26.000', 121)),
(68,N'buddh',N'Buddh International Circuit',N'Uttar Pradesh',N'India',28.3487,77.5331,194,CONVERT(DATETIME, '2022-09-19 22:34:26.000', 121)),
(69,N'americas',N'Circuit of the Americas',N'Austin',N'USA',30.1328,-97.6411,161,CONVERT(DATETIME, '2022-09-19 22:34:26.000', 121)),
(70,N'red_bull_ring',N'Red Bull Ring',N'Spielburg',N'Austria',47.2197,14.7647,678,CONVERT(DATETIME, '2022-09-19 22:34:26.000', 121)),
(71,N'sochi',N'Sochi Autodrom',N'Sochi',N'Russia',43.4057,39.9578,2,CONVERT(DATETIME, '2022-09-19 22:34:26.000', 121)),
(72,N'port_imperial',N'Port Imperial Street Circuit',N'New Jersey',N'USA',40.7769,-74.0111,4,CONVERT(DATETIME, '2022-09-19 22:34:26.000', 121)),
(73,N'BAK',N'Baku City Circuit',N'Baku',N'Azerbaijan',40.3725,49.8533,-7,CONVERT(DATETIME, '2022-09-19 22:34:26.000', 121)),
(74,N'hanoi',N'Hanoi Street Circuit',N'Hanoi',N'Vietnam',21.0166,105.766,9,CONVERT(DATETIME, '2022-09-19 22:34:26.000', 121)),
(75,N'portimao',N'Autódromo Internacional do Algarve',N'Portimão',N'Portugal',37.227,-8.6267,108,CONVERT(DATETIME, '2022-09-19 22:34:26.000', 121)),
(76,N'mugello',N'Autodromo Internazionale del Mugello',N'Mugello',N'Italy',43.9975,11.3719,255,CONVERT(DATETIME, '2022-09-19 22:34:26.000', 121)),
(77,N'jeddah',N'Jeddah Street Circuit',N'Jeddah',N'Saudi Arabia',21.5433,39.1728,15,CONVERT(DATETIME, '2022-09-19 22:34:26.000', 121))

SELECT * FROM circuits

CREATE TABLE races (
    race_id int,
    race_year nvarchar(256),
    round nvarchar(256),
    circuit_id int,
    name nvarchar(256),
    date nvarchar(256),
    time nvarchar(256),
    ingestion_date nvarchar(max))

INSERT races VALUES
(1,N'2009',N'1',1,N'Jamacia',N'29/03/2009',N'06:00:00',N'2022-09-19 22:34:27'),
(2,N'2009',N'2',2,N'Malaysian Grand Prix',N'05/04/2009',N'09:00:00',N'2022-09-19 22:34:27'),
(3,N'2009',N'3',17,N'Chinese Grand Prix',N'19/04/2009',N'07:00:00',N'2022-09-19 22:34:27'),
(4,N'2009',N'4',3,N'Bahrain Grand Prix',N'26/04/2009',N'12:00:00',N'2022-09-19 22:34:27'),
(5,N'2009',N'5',4,N'Spanish Grand Prix',N'10/05/2009',N'12:00:00',N'2022-09-19 22:34:27'),
(6,N'2009',N'6',6,N'Monaco Grand Prix',N'24/05/2009',N'12:00:00',N'2022-09-19 22:34:27'),
(7,N'2009',N'7',5,N'Turkish Grand Prix',N'07/06/2009',N'12:00:00',N'2022-09-19 22:34:27'),
(8,N'2009',N'8',9,N'British Grand Prix',N'21/06/2009',N'12:00:00',N'2022-09-19 22:34:27'),
(9,N'2009',N'9',20,N'German Grand Prix',N'12/07/2009',N'12:00:00',N'2022-09-19 22:34:27'),
(10,N'2009',N'10',11,N'Hungarian Grand Prix',N'26/07/2009',N'12:00:00',N'2022-09-19 22:34:27'),
(11,N'2009',N'11',12,N'European Grand Prix',N'23/08/2009',N'12:00:00',N'2022-09-19 22:34:27'),
(12,N'2009',N'12',13,N'Belgian Grand Prix',N'30/08/2009',N'12:00:00',N'2022-09-19 22:34:27'),
(13,N'2009',N'13',14,N'Italian Grand Prix',N'13/09/2009',N'12:00:00',N'2022-09-19 22:34:27'),
(14,N'2009',N'14',15,N'Singapore Grand Prix',N'27/09/2009',N'12:00:00',N'2022-09-19 22:34:27'),
(15,N'2009',N'15',22,N'Japanese Grand Prix',N'04/10/2009',N'05:00:00',N'2022-09-19 22:34:27'),
(16,N'2009',N'16',18,N'Brazilian Grand Prix',N'18/10/2009',N'16:00:00',N'2022-09-19 22:34:27'),
(17,N'2009',N'17',24,N'Abu Dhabi Grand Prix',N'01/11/2009',N'11:00:00',N'2022-09-19 22:34:27'),
(18,N'2008',N'1',1,N'Australian Grand Prix',N'16/03/2008',N'04:30:00',N'2022-09-19 22:34:27'),
(19,N'2008',N'2',2,N'Malaysian Grand Prix',N'23/03/2008',N'07:00:00',N'2022-09-19 22:34:27'),
(20,N'2008',N'3',3,N'Bahrain Grand Prix',N'06/04/2008',N'11:30:00',N'2022-09-19 22:34:27'),
(21,N'2008',N'4',4,N'Spanish Grand Prix',N'27/04/2008',N'12:00:00',N'2022-09-19 22:34:27'),
(22,N'2008',N'5',5,N'Turkish Grand Prix',N'11/05/2008',N'12:00:00',N'2022-09-19 22:34:27'),
(23,N'2008',N'6',6,N'Monaco Grand Prix',N'25/05/2008',N'12:00:00',N'2022-09-19 22:34:27'),
(24,N'2008',N'7',7,N'Canadian Grand Prix',N'08/06/2008',N'17:00:00',N'2022-09-19 22:34:27'),
(25,N'2008',N'8',8,N'French Grand Prix',N'22/06/2008',N'12:00:00',N'2022-09-19 22:34:27'),
(26,N'2008',N'9',9,N'British Grand Prix',N'06/07/2008',N'12:00:00',N'2022-09-19 22:34:27'),
(27,N'2008',N'10',10,N'German Grand Prix',N'20/07/2008',N'12:00:00',N'2022-09-19 22:34:27'),
(28,N'2008',N'11',11,N'Hungarian Grand Prix',N'03/08/2008',N'12:00:00',N'2022-09-19 22:34:27'),
(29,N'2008',N'12',12,N'European Grand Prix',N'24/08/2008',N'12:00:00',N'2022-09-19 22:34:27'),
(30,N'2008',N'13',13,N'Belgian Grand Prix',N'07/09/2008',N'12:00:00',N'2022-09-19 22:34:27'),
(31,N'2008',N'14',14,N'Italian Grand Prix',N'14/09/2008',N'12:00:00',N'2022-09-19 22:34:27'),
(32,N'2008',N'15',15,N'Singapore Grand Prix',N'28/09/2008',N'12:00:00',N'2022-09-19 22:34:27'),
(33,N'2008',N'16',16,N'Japanese Grand Prix',N'12/10/2008',N'04:30:00',N'2022-09-19 22:34:27'),
(34,N'2008',N'17',17,N'Chinese Grand Prix',N'19/10/2008',N'07:00:00',N'2022-09-19 22:34:27'),
(35,N'2008',N'18',18,N'Brazilian Grand Prix',N'02/11/2008',N'17:00:00',N'2022-09-19 22:34:27'),
(36,N'2007',N'1',1,N'Australian Grand Prix',N'18/03/2007',N'03:00:00',N'2022-09-19 22:34:27'),
(37,N'2007',N'2',2,N'Malaysian Grand Prix',N'08/04/2007',N'07:00:00',N'2022-09-19 22:34:27'),
(38,N'2007',N'3',3,N'Bahrain Grand Prix',N'15/04/2007',N'11:30:00',N'2022-09-19 22:34:27'),
(39,N'2007',N'4',4,N'Spanish Grand Prix',N'13/05/2007',N'12:00:00',N'2022-09-19 22:34:27'),
(40,N'2007',N'5',6,N'Monaco Grand Prix',N'27/05/2007',N'12:00:00',N'2022-09-19 22:34:27'),
(41,N'2007',N'6',7,N'Canadian Grand Prix',N'10/06/2007',N'17:00:00',N'2022-09-19 22:34:27'),
(42,N'2007',N'7',19,N'United States Grand Prix',N'17/06/2007',N'17:00:00',N'2022-09-19 22:34:27'),
(43,N'2007',N'8',8,N'French Grand Prix',N'01/07/2007',N'12:00:00',N'2022-09-19 22:34:27'),
(44,N'2007',N'9',9,N'British Grand Prix',N'08/07/2007',N'12:00:00',N'2022-09-19 22:34:27'),
(45,N'2007',N'10',20,N'European Grand Prix',N'22/07/2007',N'12:00:00',N'2022-09-19 22:34:27'),
(46,N'2007',N'11',11,N'Hungarian Grand Prix',N'05/08/2007',N'12:00:00',N'2022-09-19 22:34:27'),
(47,N'2007',N'12',5,N'Turkish Grand Prix',N'26/08/2007',N'12:00:00',N'2022-09-19 22:34:27'),
(48,N'2007',N'13',14,N'Italian Grand Prix',N'09/09/2007',N'12:00:00',N'2022-09-19 22:34:27'),
(49,N'2007',N'14',13,N'Belgian Grand Prix',N'16/09/2007',N'12:00:00',N'2022-09-19 22:34:27'),
(50,N'2007',N'15',16,N'Japanese Grand Prix',N'30/09/2007',N'04:30:00',N'2022-09-19 22:34:27'),
(51,N'2007',N'16',17,N'Chinese Grand Prix',N'07/10/2007',N'06:00:00',N'2022-09-19 22:34:27'),
(52,N'2007',N'17',18,N'Brazilian Grand Prix',N'21/10/2007',N'16:00:00',N'2022-09-19 22:34:27'),
(53,N'2006',N'1',3,N'Bahrain Grand Prix',N'12/03/2006',N'14:30:00',N'2022-09-19 22:34:27'),
(54,N'2006',N'2',2,N'Malaysian Grand Prix',N'19/03/2006',N'15:00:00',N'2022-09-19 22:34:27'),
(55,N'2006',N'3',1,N'Australian Grand Prix',N'02/04/2006',N'14:00:00',N'2022-09-19 22:34:27'),
(56,N'2006',N'4',21,N'San Marino Grand Prix',N'23/04/2006',N'14:00:00',N'2022-09-19 22:34:27'),
(57,N'2006',N'5',20,N'European Grand Prix',N'07/05/2006',N'14:00:00',N'2022-09-19 22:34:27'),
(58,N'2006',N'6',4,N'Spanish Grand Prix',N'14/05/2006',N'14:00:00',N'2022-09-19 22:34:27'),
(59,N'2006',N'7',6,N'Monaco Grand Prix',N'28/05/2006',N'14:00:00',N'2022-09-19 22:34:27'),
(60,N'2006',N'8',9,N'British Grand Prix',N'11/06/2006',N'12:00:00',N'2022-09-19 22:34:27'),
(61,N'2006',N'9',7,N'Canadian Grand Prix',N'25/06/2006',N'13:00:00',N'2022-09-19 22:34:27'),
(62,N'2006',N'10',19,N'United States Grand Prix',N'02/07/2006',N'14:00:00',N'2022-09-19 22:34:27'),
(63,N'2006',N'11',8,N'French Grand Prix',N'16/07/2006',N'14:00:00',N'2022-09-19 22:34:27'),
(64,N'2006',N'12',10,N'German Grand Prix',N'30/07/2006',N'14:00:00',N'2022-09-19 22:34:27'),
(65,N'2006',N'13',11,N'Hungarian Grand Prix',N'06/08/2006',N'14:00:00',N'2022-09-19 22:34:27'),
(66,N'2006',N'14',5,N'Turkish Grand Prix',N'27/08/2006',N'15:00:00',N'2022-09-19 22:34:27'),
(67,N'2006',N'15',14,N'Italian Grand Prix',N'10/09/2006',N'14:00:00',N'2022-09-19 22:34:27'),
(68,N'2006',N'16',17,N'Chinese Grand Prix',N'01/10/2006',N'14:00:00',N'2022-09-19 22:34:27'),
(69,N'2006',N'17',22,N'Japanese Grand Prix',N'08/10/2006',N'14:00:00',N'2022-09-19 22:34:27'),
(70,N'2006',N'18',18,N'Brazilian Grand Prix',N'22/10/2006',N'14:00:00',N'2022-09-19 22:34:27'),
(71,N'2005',N'1',1,N'Australian Grand Prix',N'06/03/2005',N'14:00:00',N'2022-09-19 22:34:27'),
(72,N'2005',N'2',2,N'Malaysian Grand Prix',N'20/03/2005',N'15:00:00',N'2022-09-19 22:34:27'),
(73,N'2005',N'3',3,N'Bahrain Grand Prix',N'03/04/2005',N'14:30:00',N'2022-09-19 22:34:27'),
(74,N'2005',N'4',21,N'San Marino Grand Prix',N'24/04/2005',N'14:00:00',N'2022-09-19 22:34:27'),
(75,N'2005',N'5',4,N'Spanish Grand Prix',N'08/05/2005',N'14:00:00',N'2022-09-19 22:34:27'),
(76,N'2005',N'6',6,N'Monaco Grand Prix',N'22/05/2005',N'14:00:00',N'2022-09-19 22:34:27'),
(77,N'2005',N'7',20,N'European Grand Prix',N'29/05/2005',N'14:00:00',N'2022-09-19 22:34:27'),
(78,N'2005',N'8',7,N'Canadian Grand Prix',N'12/06/2005',N'13:00:00',N'2022-09-19 22:34:27'),
(79,N'2005',N'9',19,N'United States Grand Prix',N'19/06/2005',N'14:00:00',N'2022-09-19 22:34:27'),
(80,N'2005',N'10',8,N'French Grand Prix',N'03/07/2005',N'14:00:00',N'2022-09-19 22:34:27'),
(81,N'2005',N'11',9,N'British Grand Prix',N'10/07/2005',N'14:00:00',N'2022-09-19 22:34:27'),
(82,N'2005',N'12',10,N'German Grand Prix',N'24/07/2005',N'14:00:00',N'2022-09-19 22:34:27'),
(83,N'2005',N'13',11,N'Hungarian Grand Prix',N'31/07/2005',N'14:00:00',N'2022-09-19 22:34:27'),
(84,N'2005',N'14',5,N'Turkish Grand Prix',N'21/08/2005',N'15:00:00',N'2022-09-19 22:34:27'),
(85,N'2005',N'15',14,N'Italian Grand Prix',N'04/09/2005',N'14:00:00',N'2022-09-19 22:34:27'),
(86,N'2005',N'16',13,N'Belgian Grand Prix',N'11/09/2005',N'14:00:00',N'2022-09-19 22:34:27'),
(87,N'2005',N'17',18,N'Brazilian Grand Prix',N'25/09/2005',N'14:00:00',N'2022-09-19 22:34:27'),
(88,N'2005',N'18',22,N'Japanese Grand Prix',N'09/10/2005',N'14:00:00',N'2022-09-19 22:34:27'),
(89,N'2005',N'19',17,N'Chinese Grand Prix',N'16/10/2005',N'14:00:00',N'2022-09-19 22:34:27'),
(90,N'2004',N'1',1,N'Australian Grand Prix',N'07/03/2004',N'\N',N'2022-09-19 22:34:27'),
(91,N'2004',N'2',2,N'Malaysian Grand Prix',N'21/03/2004',N'\N',N'2022-09-19 22:34:27'),
(92,N'2004',N'3',3,N'Bahrain Grand Prix',N'04/04/2004',N'\N',N'2022-09-19 22:34:27'),
(93,N'2004',N'4',21,N'San Marino Grand Prix',N'25/04/2004',N'\N',N'2022-09-19 22:34:27'),
(94,N'2004',N'5',4,N'Spanish Grand Prix',N'09/05/2004',N'\N',N'2022-09-19 22:34:27'),
(95,N'2004',N'6',6,N'Monaco Grand Prix',N'23/05/2004',N'\N',N'2022-09-19 22:34:27'),
(96,N'2004',N'7',20,N'European Grand Prix',N'30/05/2004',N'\N',N'2022-09-19 22:34:27'),
(97,N'2004',N'8',7,N'Canadian Grand Prix',N'13/06/2004',N'\N',N'2022-09-19 22:34:27'),
(98,N'2004',N'9',19,N'United States Grand Prix',N'20/06/2004',N'\N',N'2022-09-19 22:34:27'),
(99,N'2004',N'10',8,N'French Grand Prix',N'04/07/2004',N'\N',N'2022-09-19 22:34:27'),
(100,N'2004',N'11',9,N'British Grand Prix',N'11/07/2004',N'\N',N'2022-09-19 22:34:27'),
(101,N'2004',N'12',10,N'German Grand Prix',N'25/07/2004',N'\N',N'2022-09-19 22:34:27'),
(102,N'2004',N'13',11,N'Hungarian Grand Prix',N'15/08/2004',N'\N',N'2022-09-19 22:34:27'),
(103,N'2004',N'14',13,N'Belgian Grand Prix',N'29/08/2004',N'\N',N'2022-09-19 22:34:27'),
(104,N'2004',N'15',14,N'Italian Grand Prix',N'12/09/2004',N'\N',N'2022-09-19 22:34:27'),
(105,N'2004',N'16',17,N'Chinese Grand Prix',N'26/09/2004',N'\N',N'2022-09-19 22:34:27'),
(106,N'2004',N'17',22,N'Japanese Grand Prix',N'10/10/2004',N'\N',N'2022-09-19 22:34:27'),
(107,N'2004',N'18',18,N'Brazilian Grand Prix',N'24/10/2004',N'\N',N'2022-09-19 22:34:27'),
(108,N'2003',N'1',1,N'Australian Grand Prix',N'09/03/2003',N'\N',N'2022-09-19 22:34:27'),
(109,N'2003',N'2',2,N'Malaysian Grand Prix',N'23/03/2003',N'\N',N'2022-09-19 22:34:27'),
(110,N'2003',N'3',18,N'Brazilian Grand Prix',N'06/04/2003',N'\N',N'2022-09-19 22:34:27'),
(111,N'2003',N'4',21,N'San Marino Grand Prix',N'20/04/2003',N'\N',N'2022-09-19 22:34:27'),
(112,N'2003',N'5',4,N'Spanish Grand Prix',N'04/05/2003',N'\N',N'2022-09-19 22:34:27'),
(113,N'2003',N'6',23,N'Austrian Grand Prix',N'18/05/2003',N'\N',N'2022-09-19 22:34:27'),
(114,N'2003',N'7',6,N'Monaco Grand Prix',N'01/06/2003',N'\N',N'2022-09-19 22:34:27'),
(115,N'2003',N'8',7,N'Canadian Grand Prix',N'15/06/2003',N'\N',N'2022-09-19 22:34:27'),
(116,N'2003',N'9',20,N'European Grand Prix',N'29/06/2003',N'\N',N'2022-09-19 22:34:27'),
(117,N'2003',N'10',8,N'French Grand Prix',N'06/07/2003',N'\N',N'2022-09-19 22:34:27'),
(118,N'2003',N'11',9,N'British Grand Prix',N'20/07/2003',N'\N',N'2022-09-19 22:34:27'),
(119,N'2003',N'12',10,N'German Grand Prix',N'03/08/2003',N'\N',N'2022-09-19 22:34:27'),
(120,N'2003',N'13',11,N'Hungarian Grand Prix',N'24/08/2003',N'\N',N'2022-09-19 22:34:27'),
(121,N'2003',N'14',14,N'Italian Grand Prix',N'14/09/2003',N'\N',N'2022-09-19 22:34:27'),
(122,N'2003',N'15',19,N'United States Grand Prix',N'28/09/2003',N'\N',N'2022-09-19 22:34:27'),
(123,N'2003',N'16',22,N'Japanese Grand Prix',N'12/10/2003',N'\N',N'2022-09-19 22:34:27'),
(124,N'2002',N'1',1,N'Australian Grand Prix',N'03/03/2002',N'\N',N'2022-09-19 22:34:27'),
(125,N'2002',N'2',2,N'Malaysian Grand Prix',N'17/03/2002',N'\N',N'2022-09-19 22:34:27'),
(126,N'2002',N'3',18,N'Brazilian Grand Prix',N'31/03/2002',N'\N',N'2022-09-19 22:34:27'),
(127,N'2002',N'4',21,N'San Marino Grand Prix',N'14/04/2002',N'\N',N'2022-09-19 22:34:27'),
(128,N'2002',N'5',4,N'Spanish Grand Prix',N'28/04/2002',N'\N',N'2022-09-19 22:34:27'),
(129,N'2002',N'6',23,N'Austrian Grand Prix',N'12/05/2002',N'\N',N'2022-09-19 22:34:27'),
(130,N'2002',N'7',6,N'Monaco Grand Prix',N'26/05/2002',N'\N',N'2022-09-19 22:34:27'),
(131,N'2002',N'8',7,N'Canadian Grand Prix',N'09/06/2002',N'\N',N'2022-09-19 22:34:27'),
(132,N'2002',N'9',20,N'European Grand Prix',N'23/06/2002',N'\N',N'2022-09-19 22:34:27'),
(133,N'2002',N'10',9,N'British Grand Prix',N'07/07/2002',N'\N',N'2022-09-19 22:34:27'),
(134,N'2002',N'11',8,N'French Grand Prix',N'21/07/2002',N'\N',N'2022-09-19 22:34:27'),
(135,N'2002',N'12',10,N'German Grand Prix',N'28/07/2002',N'\N',N'2022-09-19 22:34:27'),
(136,N'2002',N'13',11,N'Hungarian Grand Prix',N'18/08/2002',N'\N',N'2022-09-19 22:34:27'),
(137,N'2002',N'14',13,N'Belgian Grand Prix',N'01/09/2002',N'\N',N'2022-09-19 22:34:27'),
(138,N'2002',N'15',14,N'Italian Grand Prix',N'15/09/2002',N'\N',N'2022-09-19 22:34:27'),
(139,N'2002',N'16',19,N'United States Grand Prix',N'29/09/2002',N'\N',N'2022-09-19 22:34:27'),
(140,N'2002',N'17',22,N'Japanese Grand Prix',N'13/10/2002',N'\N',N'2022-09-19 22:34:27'),
(141,N'2001',N'1',1,N'Australian Grand Prix',N'04/03/2001',N'\N',N'2022-09-19 22:34:27'),
(142,N'2001',N'2',2,N'Malaysian Grand Prix',N'18/03/2001',N'\N',N'2022-09-19 22:34:27'),
(143,N'2001',N'3',18,N'Brazilian Grand Prix',N'01/04/2001',N'\N',N'2022-09-19 22:34:27'),
(144,N'2001',N'4',21,N'San Marino Grand Prix',N'15/04/2001',N'\N',N'2022-09-19 22:34:27'),
(145,N'2001',N'5',4,N'Spanish Grand Prix',N'29/04/2001',N'\N',N'2022-09-19 22:34:27'),
(146,N'2001',N'6',23,N'Austrian Grand Prix',N'13/05/2001',N'\N',N'2022-09-19 22:34:27'),
(147,N'2001',N'7',6,N'Monaco Grand Prix',N'27/05/2001',N'\N',N'2022-09-19 22:34:27'),
(148,N'2001',N'8',7,N'Canadian Grand Prix',N'10/06/2001',N'\N',N'2022-09-19 22:34:27'),
(149,N'2001',N'9',20,N'European Grand Prix',N'24/06/2001',N'\N',N'2022-09-19 22:34:27'),
(150,N'2001',N'10',8,N'French Grand Prix',N'01/07/2001',N'\N',N'2022-09-19 22:34:27'),
(151,N'2001',N'11',9,N'British Grand Prix',N'15/07/2001',N'\N',N'2022-09-19 22:34:27'),
(152,N'2001',N'12',10,N'German Grand Prix',N'29/07/2001',N'\N',N'2022-09-19 22:34:27'),
(153,N'2001',N'13',11,N'Hungarian Grand Prix',N'19/08/2001',N'\N',N'2022-09-19 22:34:27'),
(154,N'2001',N'14',13,N'Belgian Grand Prix',N'02/09/2001',N'\N',N'2022-09-19 22:34:27'),
(155,N'2001',N'15',14,N'Italian Grand Prix',N'16/09/2001',N'\N',N'2022-09-19 22:34:27'),
(156,N'2001',N'16',19,N'United States Grand Prix',N'30/09/2001',N'\N',N'2022-09-19 22:34:27'),
(157,N'2001',N'17',22,N'Japanese Grand Prix',N'14/10/2001',N'\N',N'2022-09-19 22:34:27'),
(158,N'2000',N'1',1,N'Australian Grand Prix',N'12/03/2000',N'\N',N'2022-09-19 22:34:27'),
(159,N'2000',N'2',18,N'Brazilian Grand Prix',N'26/03/2000',N'\N',N'2022-09-19 22:34:27'),
(160,N'2000',N'3',21,N'San Marino Grand Prix',N'09/04/2000',N'\N',N'2022-09-19 22:34:27'),
(161,N'2000',N'4',9,N'British Grand Prix',N'23/04/2000',N'\N',N'2022-09-19 22:34:27'),
(162,N'2000',N'5',4,N'Spanish Grand Prix',N'07/05/2000',N'\N',N'2022-09-19 22:34:27'),
(163,N'2000',N'6',20,N'European Grand Prix',N'21/05/2000',N'\N',N'2022-09-19 22:34:27'),
(164,N'2000',N'7',6,N'Monaco Grand Prix',N'04/06/2000',N'\N',N'2022-09-19 22:34:27'),
(165,N'2000',N'8',7,N'Canadian Grand Prix',N'18/06/2000',N'\N',N'2022-09-19 22:34:27'),
(166,N'2000',N'9',8,N'French Grand Prix',N'02/07/2000',N'\N',N'2022-09-19 22:34:27'),
(167,N'2000',N'10',23,N'Austrian Grand Prix',N'16/07/2000',N'\N',N'2022-09-19 22:34:27'),
(168,N'2000',N'11',10,N'German Grand Prix',N'30/07/2000',N'\N',N'2022-09-19 22:34:27'),
(169,N'2000',N'12',11,N'Hungarian Grand Prix',N'13/08/2000',N'\N',N'2022-09-19 22:34:27'),
(170,N'2000',N'13',13,N'Belgian Grand Prix',N'27/08/2000',N'\N',N'2022-09-19 22:34:27'),
(171,N'2000',N'14',14,N'Italian Grand Prix',N'10/09/2000',N'\N',N'2022-09-19 22:34:27'),
(172,N'2000',N'15',19,N'United States Grand Prix',N'24/09/2000',N'\N',N'2022-09-19 22:34:27'),
(173,N'2000',N'16',22,N'Japanese Grand Prix',N'08/10/2000',N'\N',N'2022-09-19 22:34:27'),
(174,N'2000',N'17',2,N'Malaysian Grand Prix',N'22/10/2000',N'\N',N'2022-09-19 22:34:27'),
(175,N'1999',N'1',1,N'Australian Grand Prix',N'07/03/1999',N'\N',N'2022-09-19 22:34:27'),
(176,N'1999',N'2',18,N'Brazilian Grand Prix',N'11/04/1999',N'\N',N'2022-09-19 22:34:27'),
(177,N'1999',N'3',21,N'San Marino Grand Prix',N'02/05/1999',N'\N',N'2022-09-19 22:34:27'),
(178,N'1999',N'4',6,N'Monaco Grand Prix',N'16/05/1999',N'\N',N'2022-09-19 22:34:27'),
(179,N'1999',N'5',4,N'Spanish Grand Prix',N'30/05/1999',N'\N',N'2022-09-19 22:34:27'),
(180,N'1999',N'6',7,N'Canadian Grand Prix',N'13/06/1999',N'\N',N'2022-09-19 22:34:27'),
(181,N'1999',N'7',8,N'French Grand Prix',N'27/06/1999',N'\N',N'2022-09-19 22:34:27'),
(182,N'1999',N'8',9,N'British Grand Prix',N'11/07/1999',N'\N',N'2022-09-19 22:34:27')

SELECT * FROM races

Thanks

Johan Bijnens

SSC Guru

Points: 135368
More actions
September 20, 2022 at 8:38 am
Answer
#4089698
Because you use "AND races.race_year = 2019" you turn your query result into the results of an inner join !!
add "or races.race_year is null" if you want the full results

BTW: you have specified to use "RIGHT OUTER JOIN dbo.races", meaning all results of table dbo.races, add results of dbo.circuits if there is a join-match (ON circuits.circuit_id = races.circuit_id).
```
SELECT races.race_year,circuits.name AS circuit_name
     , circuits.location
     , circuits.country
     , races.name AS race_name
     , races.round
FROM dbo.circuits
LEFT JOIN dbo.races
     ON circuits.circuit_id = races.circuit_id
WHERE circuits.circuit_id < 70
      AND (races.race_year = 2009 or races.race_year is null );
```
why is column "round" declared nvarchar(256)????? ( now try to sort your result based on "round". What do you end up with? )
Same goes for "date", "time", "ingestion_date"
Maybe even data type "geography" can help you out ! Check your options
Johan

Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

carlton 84646 Ten Centuries Points: 1026 More actions · Answer 1

I just learned something today

Because you use "AND races.race_year = 2019" you turn your query result into the results of an inner join !

Thanks

carlton 84646 Ten Centuries Points: 1026 More actions · Answer 2

So, should I always write the code before doing the join for example doing the following will get me the right result:

First create tables / views

CREATE VIEW circuitsv1

AS

SELECT
  circuits.name AS circuit_name
 ,circuits.location
 ,circuits.country
 ,circuits.circuit_id
FROM dbo.circuits
WHERE circuits.circuit_id < 70

CREATE VIEW racesv1

  AS

SELECT
  races.circuit_id
 ,races.race_year
 ,races.name AS race_name
 ,races.round
FROM dbo.races
WHERE races.race_year = 2019

And now do the left outer join - BTW which will give me the correct result

SELECT
  *
FROM dbo.circuitsv1
LEFT OUTER JOIN dbo.racesv1
  ON circuitsv1.circuit_id = racesv1.circuit_id

Coding in the way I have described above seems very long winded. However, as Johan pointed out I can't achieve the result using the following code because of the AND clause

SELECT
circuits.name AS circuit_name
,circuits.location
,circuits.country
,races.name AS race_name
,races.round
FROM dbo.circuits
LEFTOUTER JOIN dbo.races
ON circuits.circuit_id = races.circuit_id
WHERE circuits.circuit_id < 70
AND races.race_year = 2019

Is there any other way to achieve the result without having to first create views or derived tables

Grant Fritchey SSC Guru Points: 398881 More actions · Answer 3

Although, if you move that part of the WHERE clause to the ON clause, you should still get an outer join.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Johan Bijnens SSC Guru Points: 135368 More actions · Answer 4

Lets practice some relational stuff, now your sets are small

visualize what you are doing !

DE-SQL-Join-Typen-1249454618

Moving the year selection to a view is going to modify the results that set(view) will return!

Keep in mind what makes your JOIN ! ( i.e. the ON clause )

and differentiate what makes your WHERE condition(s) taking into account your second object is NULLable !

Any where condition based on a column of the second object, will get validated, but follows a tree part logic due to the NULLs !

ps: No need to over complicate stuff using views !!

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

carlton 84646 Ten Centuries Points: 1026 More actions · Answer 5

Hi Grant, can you give an example please.

Johan, thanks for the detailed explanation. However, the question still remains, how can I achieve the result without creating a derived table or view?

Your code didn't appear to achieve it:

SELECT races.race_year,circuits.name AS circuit_name
, circuits.location
, circuits.country
, races.name AS race_name
, races.round
FROM dbo.circuits
LEFT JOIN dbo.races
ON circuits.circuit_id = races.circuit_id
WHERE circuits.circuit_id < 70
AND (races.race_year = 2009 or races.race_year is null );

However, I really appreciate the lesson - seriously

This reply was modified 2 years, 2 months ago by carlton 84646. Reason: update

Ed B Hall of Fame Points: 3665 More actions · Answer 6

SELECT circuits.name AS circuit_name
 ,circuits.location
 ,circuits.country
 ,races.name AS race_name
 ,races.round
FROM circuits
LEFT OUTER JOIN races on circuits.circuit_id = races.circuit_id
AND races.race_year = 2009
WHERE circuits.circuit_id < 70

There are no races in 2019 in your data, so 2009 is used. 2019 will return all nulls.

This reply was modified 2 years, 2 months ago by Ed B.

Grant Fritchey SSC Guru Points: 398881 More actions · Answer 7

Ed has beat me to it.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

carlton 84646 Ten Centuries Points: 1026 More actions · Answer 8

Perfect Ed

For my educational purposes, can you let me know where the following doesn't work:

SELECT
circuits.name AS circuit_name
,circuits.location
,circuits.country
,races.name AS race_name
,races.round
FROM dbo.circuits
LEFT OUTER JOIN dbo.races
ON circuits.circuit_id = races.circuit_id
AND races.race_year = 2009
AND circuits.circuit_id < 70

The only difference is

WHERE circuits.circuit_id < 70

Zidar SSC Enthusiast Points: 126 More actions · Answer 9

Grant is very right. When you want to apply LEFT OUTER JOIN to a subset of outer table, do not place condition in WHERE clause. Place it in ON clause.

Itzik Ben-Gan explains this in his books. The most efficient solution, based on the way SQL statement is parsed. Of course it is possible to achive the same thing, in indirect ways. One f hem is:

WITH[FilteredOuterTable] AS
(
SELECT..
FROM <outer table>
WHERE <condition> 
)
SELECT ..
FROM <FROM table> 
INNER JOIN [FilteredOuterTable] AS ON <just a JOIN condition>

To avoid hardcoding WHERE in [FilteredOuterTable], in some cases we could wrap this into a table valued function (and make this more complicated and confusing)

🙂

Zidar's Theorem: The best code is no code at all...

Grant Fritchey SSC Guru Points: 398881 More actions · Answer 10

To a degree, the difference between ON & WHERE, can be, but isn't entirely, syntactic sugar. However, what I say is, for clarity sake, put filter criteria in the WHERE clause, and JOIN criteria in the ON clause. The exception is, where you are now, with the OUTER join.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

carlton 84646 Ten Centuries Points: 1026 More actions · Answer 11

Grant, sorry to be going on about this.

But just to be clear, are you saying that if not for the fact that I'm working with a OUTER JOIN everything would be fine?

That is to say, I would not have any problems if working with INNER JOIN?

Ed B Hall of Fame Points: 3665 More actions · Answer 12

carlton 84646 wrote:

For my educational purposes, can you let me know where the following doesn't work:

If you filter the right side of a left outer join using WHERE, the filter is applied after the join. Any nulls on the right side of the join are dropped because null circuit_id cannot be compared to 70. This basically undoes the outer join and makes it an inner join. You can add "OR circuit_id IS NULL", but I prefer not to.

You can go entirely the other way and put all the criteria in the join, but as Grant said, ideally the ON is used for joining the tables on related columns and WHERE is used for filtering. The exception is filtering the right side of a left outer join.

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 13

Grant Fritchey wrote:

To a degree, the difference between ON & WHERE, can be, but isn't entirely, syntactic sugar. However, what I say is, for clarity sake, put filter criteria in the WHERE clause, and JOIN criteria in the ON clause. The exception is, where you are now, with the OUTER join.

I would argue that this is not an exception - the condition AND races.race_year = 2009 is part of the join criteria and not part of the filtering criteria. With that said, I agree that filtering (WHERE) should be done in the where clause - and not in the JOIN. I have seen many queries where the filtering was also done in the join to such a degree that the same filtering was applied to many joins. That just causes the code to be much harder to read, manage and maintain.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs