[Edit]
Make myself more clear.
Both answers of MT0 and Tejash are the same with my final code.
But I have read some code from my pre-colleague, they prefer to using loop to dynamic create "WHERE" clause.
That is if :id is not null, then add "and id = :id".
If :upload_date is not null, then add "and upload_date > to_date(:upload_date, 'yyyymmdd')"
Is there any performance implicitly concerning?
Or both "Dynamic SQL" and "One SQL" are good solution for my goal because there is no performance difference between them?
[Original]
I have a SQL below:
SELECT id, name file_name, upload_date
FROM A
WHERE id like :id
and upload_date > to_date(:upload_date, 'yyyymmdd')
The conditions:
1. :id => '%' (if :id is null) or specific id
2. :upload_date
=> If the upload_date is null, the default date is '20200101'
My goal is :
1.If both :id and :upload_date is null value, I want to show all the data from Table A.
2.If :id or :upload_date is not null, I want to show the data with conditions.
I have come up with anther SQL below:
SELECT id, name file_name, upload_date
FROM A
WHERE id like :id
and ((:upload_date is not null and upload_date > to_date(:upload_date, 'yyyymmdd'))
or :upload_date is null)
I wonder which SQL is better for large data volume or it's no difference between them
As per your edit, if you can execute SQL that does exactly what you want it will in general be faster than one SQL that does 3 or 4 variations. e.g. if there is no ID sent, then omit that from the SQL. Same with date.
The optimiser can "peek" at variables to work out a plan from a more general query but it is far better to be specific as it can only evaluate so many options per query. This is quite simple so may not matter but it's good practice. Single-responsibility applies to SQL as well as other code! It's also cleaner for humans to debug!