sql - Table Join condition -


i have 2 tables   table       :                   item    lookup                          x                   b       null                   c        y                   d        k  table b       :                       lookup                      x                      y                      z  want join these tables , output                 item    lookup                      x                b       null                c       y 

i wanna pick matching lookup , null lookups in output view. can tell me join condition

it looks want rows in a either match b or have null lookup. inner join , special condition:

select distinct a.item, a.lookup tablea join      tableb b      on (a.lookup = b.lookup) or (a.lookup null); 

if have index on lookup, exists better performance:

select a.item, a.lookup tablea a.lookup null or       exists (select 1 tableb b b.lookup = a.lookup); 

edit:

using left join where condition possible:

select distinct a.item, a.lookup tablea left join      tableb b      on a.lookup = b.lookup (a.lookup null , b.lookup null) or       b.lookup not null; 

Comments

Popular posts from this blog

How has firefox/gecko HTML+CSS rendering changed in version 38? -

android - CollapsingToolbarLayout: position the ExpandedText programmatically -

Listeners to visualise results of load test in JMeter -