Fetch all rows and leave column value empty if no equality match

All we need is an easy explanation of the problem, so here it is.

I have a query where I want to fetch all rows where the value in column x is between 400-600.
Including in this I want a matching column value from antoher table.

But I want all 200 rows between 400 and 600 regardless of if the value in the second table column exists for the corresponding column value in the first table.

Oracle SQL:

SELECT h.*, k.customer_id
FROM EVENTS h, CUSTOMERS k
WHERE k.id = h.ident
AND h.eventid BETWEEN 400 AND 600 ORDER BY h.eventid;

This limits the results to where k.id = h.ident is true. But on false I just want an empty value aswell so that I get all 200 rows.

TL;DR – How can I default to a value if Equal to is false (a.b=c.d), instead of filtering out that row?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

I want all 200 rows between 400 and 600 regardless of if the value in the second table column exists

So given two joined tables, "left" and "right", you want to include every row from the "left" table and matching rows in the "right" table if they happen to be there?
That’s called a Left [Outer] Join.

You’ll have to use explicit join syntax, not the "comma" form that you currently have, because that always does an "inner" join, requiring matching values in both tables.

Try something like this:

SELECT 
  h.*                   <-- Personally, I'd avoid this and state every column name explicitly
, k.customer_id
FROM events h 
LEFT JOIN customers k
     ON h.ident = k.id 
WHERE h.eventid BETWEEN 400 and 600
ORDER BY h.eventid 
;

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply