Oracle select columns on condition

Databases
1

Is it possible (in Oracle) to select a column from a table only when some condition is met?

For example, I have a SELECT query that selects columns col1, col2, col3 and I want to select col4 only if some condition is met?

2

well, since a query cannot select a variable number of columns, you will either have to retrieve col4 anyway and only use it if the condition is met, or run two different queries

3

Yes that makes sense. I used CASE WHEN after all and when the condition is not met I set the column to null:


...
CASE WHEN (SELECT COUNT(*) FROM table2 t2 WHERE t2.id = t1.id) >= 2 THEN (SELECT MIN(col4) FROM table3 t3 WHERE t3.id = t1.id) ELSE null END column4
...
4

All those nested selects look horribly inefficient. It’s usually preferrable to use joins instead. I would left join to the other tables.

Something like:


select
  col1, col2, col3,
  case
    when t2.id is not null min(t3.col4)
    else null
  end col4
from t1
  left join t2 on t1.id = t2.id and count(t2.*) >= 2
  left join t3 on t1.id = t3.id

That’s the best I could make sense of your obfuscated sample query but the general approach is probably what you’re after.

Cheers,
D.

5

Joins are more efficient but it’s easier for me to use sub selects when writing a query. It seems more logical to me and I have to think less than with joins.

But in real applications I prefer joins of course :slight_smile:

6

If you want to see the whole query here it is (but I warned you :D):


SELECT 
DISTINCT a.typ
, (SELECT COUNT(*) FROM Auta WHERE typ = a.typ) pocet_aut
, (SELECT AVG(poplatok_denny) FROM Auta WHERE typ = a.typ) priemerny_denny_poplatok
, CASE WHEN (SELECT COUNT(*) FROM Auta WHERE typ = a.typ) >= 2 THEN (SELECT MIN(poplatok_denny) FROM Auta WHERE typ = a.typ) ELSE null END minimalny_denny_poplatok
, CASE WHEN (SELECT COUNT(*) FROM Auta WHERE typ = a.typ) >= 2 THEN (SELECT MAX(poplatok_denny) FROM Auta WHERE typ = a.typ) ELSE null END maximalny_denny_poplatok 
FROM Auta a;
7

Oh, it’s all the same table you’re working on? That makes it a lot simpler then. I’ve never worked with Oracle before but I can’t see why this wouldn’t work:

select
	typ,
	count(*) pocet_aut,
	avg(poplatok_denny) priemerny_denny_poplatok,
	case
		when count(*) > 1 then min(poplatok_denny)
		else null
	end minimalny_denny_poplatok,
	case
		when count(*) > 1 then max(poplatok_denny)
		else null
	end maximalny_denny_poplatok,
from
	Auta
group by
	typ
8

writing that type of repetitive correlated subquery has the potential of being ~seriously~ inefficient

try this –


SELECT DISTINCT a.typ
     , m.pocet_aut
     , m.priemerny_denny_poplatok
     , CASE WHEN m.pocet_aut >= 2
            THEN m.minimalny_denny_poplatok
            ELSE null END   minimalny_denny_poplatok
     , CASE WHEN m.pocet_aut >= 2
            THEN m.maximalny_denny_poplatok
            ELSE null END   maximalny_denny_poplatok
  FROM Auta a
INNER
  JOIN ( SELECT type
              , COUNT(*)   pocet_aut
              , AVG(poplatok_denny)   priemerny_denny_poplatok
              , MIN(poplatok_denny)   minimalny_denny_poplatok
              , MAX(poplatok_denny)   maximalny_denny_poplatok
           FROM Auta
         GROUP
             BY typ )   m
    ON m.typ = a.typ
9

I’m not sure why you need any join at all.

Cheers,
D.

10

Hmm, this gives me this error (and I added semicolon on the end of the query, in Oracle semicolons are required to end each query):


ORA-00936: missing expression

But it’s not important. This is just an assigment for my Oracle class and the query I made works good.

11

This works the same as my query :slight_smile:

There’s just one typo (type instead of typ).

12

The reason my query failed is because there was a comma after the last column. Remove that, add the semicolon and you should be right to go. I would be interested in the performance difference between my query and r937’s with the inner join. There probably won’t be any perceptible difference unless the data set is massive, but you can check out the execution plan and/or statistics.

Cheers,
D.

13

Performance:

My query: 0.02s, 0.01s, 0.02s, 0.02s, 0.02s

rudy’s query: 0.02s, 0.02s, 0.02s, 0.02s, 0.01s

disgracian’s query: 0.01s, 0.01s, 0.00s, 0.02s, 0.02s

:smiley:

EDIT: I have run each query 5 times.

EDIT2: Sorry, now it’s correct.

14

Seem’s like yours is the fastest, disgracian :wink:

15

Interesting thing is that all three queries return the rows in different order. But they all return the same rows.

16

I would say that the performance results are inconclusive overall. You would either need 5-10 times more data before performance started to really diverge.

That is quite interesting about the order, and I’m not sure why that would happen. You can simply use an ORDER BY clause on any of the SELECT fields if you want to enforce a particular order.

Cheers,
D.

17

The different order is probably caused by different programming of subselects and joins in MySQL. Subselects must be using different algorithm.