CODEWITHSUNDEEP
sql
chris_techno25
chris_techno25

Reputation: 2487

How do I use INNER JOIN and still query rows without reference

I have the following sample tables...

Table1:

F1   F2     
 2   Chris  
 5   John   
 8   Smith

Table2:

F1   F2
 2   New
 5   Old
 8   New
 9   New

My tables might not make too much sense but they're only samples. Anyways... If I use this SQL statement...

SELECT 
    T2.*,T1.F2
FROM 
    Table2 T2
INNER JOIN 
    Table1 T1 ON T2.F1 = T1.F1

I should be able to get the rows shown below...

T2.F1  T2.F2  T1.F2
 2     New    Chris
 5     Old    John
 8     New    Smith

However, this is not what I want... I'd like to get the rows without the reference from doing the INNER JOIN too. If no reference is found, a null should be returned for that field, but still have the same # of rows returned like this...

T2.F1  T2.F2  T1.F2
 2     New    Chris
 5     Old    John
 8     New    Smith
 9     New    NULL

How do I do this? Thank you very much in advance :)

Upvotes: 0

Views: 47

Answers (1)

marc_s
marc_s

Reputation: 755073

You cannot do this using an INNER JOIN - you need to use a LEFT OUTER JOIN instead :

SELECT 
    T2.*,T1.F2
FROM 
    Table2 T2
LEFT OUTER JOIN 
    Table1 T1 ON T2.F1 = T1.F1

Upvotes: 3

Related Questions