Matthew Darton Matthew Darton - 2 months ago 9
SQL Question

Simple WHERE clause but keep extracted rows and fill them will null values

I have a table which basically looks like this one:

Date | Criteria
12-04-2016 123
12-05-2016 1234
...


Now I want to select those rows with values in the column 'Criteria' within a given range but I want to keep the extracted rows. The extracted rows should get the value 'null' for the column 'Criteria'. So for example, if I want to select the row with 'Criteria = 123' my result should look like this:

Date | Criteria
12-04-2016 123
12-05-2016 null


Currently I am using this query to get the result:

SELECT b.date, a.criteria
FROM (SELECT id, date, criteria FROM ABC WHERE criteria > 100 and criteria < 200) a
FULL OUTER JOIN ABC b ON a.id = b.id ORDER BY a.criteria


Someone told me that full outer joins perform very badly. Plus my table has like 400000 records and the query is used pretty often. So anyone has an idea to speed up my query? Btw I am using the Oracle11g database.

Answer

Do you just want a case expression?

SELECT date,
       (case when criteria > 100 and criteria < 200 then criteria end) as criteria
FROM ABC;