0

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?

2
  • Hi and welcome to the forum! The question isn't clear. Please go to dbfiddle.uk and give your tables and some sample data and your desired result. Maybe something like SELECT C(h.ident, 0), C(h.eventid, 0), h.*, k.customer_id FROM events h JOIN customers k ON k.id = h.ident ORDER BY C(h.eventid); where C() is the COALESCE() function. If this answers your problem, I can write it up as answer. You seem to want something like a.b = c.d OR a.b != c.d which is all the records match the JOIN! But removing the WHERE altogether might solve your problem AIUI? Assume k.id != NULL!
    – Vérace
    May 4, 2021 at 10:07
  • Hi @Vérace! I should have explained more clearly. You made me look into COALESCE() for which I am grateful :) But I struggled with the syntax and wasnt able to test it out. Seems like I have found my answer though with LEFT JOIN
    – Burry
    May 4, 2021 at 19:19

1 Answer 1

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 
;
2
  • Good answer. Not to be pedantic, just to clarify for future readers who may not know better, but the comma syntax for a JOIN clause is actually a CROSS JOIN. Adding a WHERE clause that compares on the equality of the key field(s) between the two tables then filters the rows down to the equivalent results of an INNER JOIN (which is just a subset of the CROSS JOIN). Without that added WHERE clause, or by comparing other fields within the WHERE clause could result in unexpected other subset results of the CROSS JOIN, which is why it's bad practice to use the comma syntax.
    – J.D.
    May 4, 2021 at 12:12
  • Great answer Phill! It worked. Thank you!
    – Burry
    May 4, 2021 at 19:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.