Oracle 11g – Fetch All Rows and Leave Column Value Empty if No Match

conditionoracleoracle-11goracle-11g-r2query

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?

Best Answer

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 
;
Related Question