Question :
A question that was asked among a few developers, now deferred to DBA experts:
Is there a way to select multiple columns within a table instead of SELECT‘ing columns piecemeal? For example, let’s say example table contains 26 columns respectively titled by letters (e.g. A, B, C, etc.). Suppose I would like to retrieve all rows from columns H, I, J, K, L, M, N, O, P- is there a more efficient way to begin the SELECT statement of the following query?
SELECT H, I, J, K, L, M, N, O, P FROM table;
Perhaps something like so?
SELECT H-P FROM table;
Thank you!
Answer :
This is a crazy idea and would makes future maintenance really tricky.
It is not possible to guarantee that column order will never change. There are plenty of situations where column order changes could potentially be beneficial. It may happen accidentally (update the wrong database) or it may be done for performance reasons (e.g. moving mostly NULL columns to the end).
Even if it is possible – don’t do it.
Since answers were sparse, I relayed this question on the twittersphere, which flagged the attention of Jeff Smith, Senior SQL Developer Product Manager at Oracle. Although he confirmed that no such feature exists yet, he recommended a similar approach referred to as “SELECT * Territory”, demonstrated in the following YouTube Screencast, and detailed thusly:
- Construct a query like the one in the screenshot below, as if it began with
SELECT *:
- Delete the
*, then highlight the statement within the query-builder window.
- Next within the table-view window, select the desired columns (on OS X, CMD + mouse-click) to be displayed piecemeal within the resulting query-result window and drop within the query builder, in lieu of
*placement.
- The “Drag and Drop Effects” will prompt; upon selecting “Select” + “Individual Statements” and “Apply” within the aforementioned window, the customized query-view will output as specified.
I have verified the above process within my local environment; one thing to note for OS X/Mac users with Retina displays – there’s a bug – verify your environment is running Java 8 u40 JDK – that should remediate any freezing when clicking “Apply” within the “Drag and Drop Effects” window. Please feel free to comment herein-thank you!
No it’s not possible but you can create view and use SQL>select * from view_name and you get your result .