SQL Simple example of LEFT JOIN With A WHERE Clause one to many force nulls

SQL Simple example of LEFT JOIN With A WHERE Clause one to many force nulls

Overview:

A LEFT JOIN will normally force nulls on the primary table (with the list of all rows whether there is data or not) results but when you add a WHERE clause on secondary table with the data you no longer get all the values in the primary table so you need to:

1. Send the data table results, using the WHERE clause, into a TABLE variable.
2. Use the Left JOIN on the TABLE variable in step 1 above.

Setup SQL:

use TestRunTracker_T

go  

CREATE TABLE Team_Names (

Team_Name nvarchar(100) NOT NULL

)

use TestRunTracker_T

go

insert into Team_Names select 'one'

insert into Team_Names select 'two'

insert into Team_Names select 'three'

insert into Team_Names select 'four'

use TestRunTracker_T

go  

CREATE TABLE Team_Names_Data (

[RecNumId] [int] IDENTITY(1,1) NOT NULL,

Team_Name nvarchar(100) NOT NULL

)

use TestRunTracker_T

go

insert into Team_Names_Data select 'one'

insert into Team_Names_Data select 'two'

insert into Team_Names_Data select 'four'

Execution SQL:

use TestRunTracker_T

go

declare @Resultsnow TABLE (

[RecNumId] [int] IDENTITY(1,1) NOT NULL,

Team_Name nvarchar(100) NOT NULL

)

INSERT

       @Resultsnow (Team_Name)

     SELECT

Team_Name

     FROM

       Team_Names_Data

     WHERE

       Team_Names_Data.Team_Name = 'two'

select *

from Team_Names A  

left join @Resultsnow B on A.Team_Name = B.Team_Name

Results:

Meta: Outer