Consolidate SQL Union With JOIN

Let’s say I have 2 tables (bar_helper and bar). I have a query that retrieves records based on a JOIN between the tables and some condition.

Essentially, my first result set is joined and I want my second one to be unconditional and just based on one table.

SELECT DISTINCT a.barId, b.foo 
FROM bar_helper a 
    JOIN bar b ON a.barId=b.id
WHERE a.color='blue' AND b.type='wood';

​x
 
SELECT DISTINCT a.barId, b.foo FROM bar_helper a     JOIN bar b ON a.barId=b.idWHERE a.color='blue' AND b.type='wood';​

This works fine. However, now I need to also get a second set of bar records (unconditional of bar_helper). I don’t want to execute a second query and then merge results. It looks like I can solve this with UNION (not using All bc deduped is fine)

So now my query would be something like.

SELECT DISTINCT a.barId, b.foo 
FROM bar_helper a 
    JOIN bar b ON a.barId=b.id
WHERE a.color='blue' AND b.type='wood'

UNION

SELECT id, foo
FROM bar where magnitude=10;

 
SELECT DISTINCT a.barId, b.foo FROM bar_helper a     JOIN bar b ON a.barId=b.idWHERE a.color='blue' AND b.type='wood'​UNION​SELECT id, fooFROM bar where magnitude=10;​

This seems to work and I get 1 resultset that combines both. Is there a more consolidated way to do this though? Can I just use OR. The JOIN is what’s confusing me. I’m looking for something that’s like.. “get these first set of results”…..”but…also…get these too”.

Any insights?

Answer

Use EXISTS:

SELECT b.id, b.foo
FROM bar b 
WHERE b.magnitude = 10
   OR 
   (b.type = 'wood' AND EXISTS (SELECT 1 FROM bar_helper a WHERE a.color = 'blue' AND a.barId = b.id))

 
SELECT b.id, b.fooFROM bar b WHERE b.magnitude = 10   OR    (b.type = 'wood' AND EXISTS (SELECT 1 FROM bar_helper a WHERE a.color = 'blue' AND a.barId = b.id))​

Advertisement

Answer