This site is currently read-only as we are migrating to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 31st, when you will be able to use this site as normal.

    Forum Stats

  • 3,890,899 Users
  • 2,269,649 Discussions
  • 7,916,821 Comments

Discussions

Select all but one column from a table

Danilo Piazzalunga
Danilo Piazzalunga Member Posts: 8 Bronze Badge
edited Feb 1, 2018 9:10AM in Database Ideas - Ideas

It would be very convenient if the Oracle Database provided a simple syntax to select every attribute from a table except one column.

For example:

SELECT *EXCEPT COMMISSION_PCTFROM EMPLOYEES;

This idea was inspired by this thread.

Sven W.Raj_insightKarishma Mungara-Oracleblessed DBAWilliam RobertsonManik3830667
14 votes

Active · Last Updated

Comments

  • Mike Kutz
    Mike Kutz Member Posts: 6,317 Gold Crown
    edited Feb 1, 2018 10:03AM

    SELECT * EXCEPT ... is about as bad as SELECT *.

    I don't think this is necessary.

    I recommend that you use a tool that can auto-expand "SELECT *" and manually eliminate the column(s) you don't want.

    SQL*Developer:

    pastedImage_0.png

    BEDEBPeaslandDBA
  • Sven W.
    Sven W. GermanyMember Posts: 10,562 Gold Crown

    SELECT * EXCEPT ... is about as bad as SELECT *.

    I don't think this is necessary.

    I recommend that you use a tool that can auto-expand "SELECT *" and manually eliminate the column(s) you don't want.

    SQL*Developer:

    pastedImage_0.png

    I like the idea. Although I think we might have a similar suggestion already here in the idea space.

    @Mike: Expanding the column list is not the same as using select * except.

    One is static, whereas the * option will react if the column structure changes .

    Imho this is a very good thing (many developers think differently). I say it has its place, although it is often used in a wrong way.

    Also remember that the base feed for the select * don't have to be a table, it could be a subquery.

    Danilo PiazzalungaBrunoVroman
  • BEDE
    BEDE Oracle Developer Bucharest, RomaniaMember Posts: 2,484 Gold Trophy

    If there were such a feature it would be poor practice to use such a feature in stored procedures, just as it is poor practice to use something like insert into dest_table select * from source_table...

    Wouldn't this be one too many features?

    Better generate the list of columns in your select using select column_name||',' from user_tab_columns t where t.table_name=&table order by t.column_id, copy and paste that to your code and then delete what you do not wish to select. There is some work to do, but I think only some graphical user interface or some AI might be preferable to this classical approach of writing code.

    [Deleted User]
  • [Deleted User]
    [Deleted User] Jack of all (Geo-) trades Hoorn, The NetherlandsPosts: 0 Silver Trophy

    And the next Idea would be for Except to support a list of column names instead of just one....

  • BrunoVroman
    BrunoVroman BelgiumMember Posts: 1,848 Silver Crown

    I like the idea. Although I think we might have a similar suggestion already here in the idea space.

    @Mike: Expanding the column list is not the same as using select * except.

    One is static, whereas the * option will react if the column structure changes .

    Imho this is a very good thing (many developers think differently). I say it has its place, although it is often used in a wrong way.

    Also remember that the base feed for the select * don't have to be a table, it could be a subquery.

    Hi Sven,

    the "*" is not always dynamic... It can be "silently expanded" (unfortunately IMHO)
    CREATE TABLE t ( n1 NUMBER, n2 NUMBER );
    CREATE VIEW v AS SELECT * FROM t;
    ALTER TABLE t ADD ( n3 NUMBER );
    INSERT INTO t VALUES ( 1, 2, 3 );
    SELECT * FROM v;
            N1         N2
    ---------- ----------
             1          2
    In fact the "*" has been silently expanded when the view was created:
    SQL> select text from user_views where view_name = 'V';
    TEXT
    ---------------------------------------------------
    SELECT "N1","N2" FROM t
    (I think that this is a behavior that changed between Oracle 9.2.0.4 (no expansion) and 9.2.0.7 (expansion))

    Best regards,

    Bruno.

    Sven W.
  • BPeaslandDBA
    BPeaslandDBA Member Posts: 4,615 Blue Diamond

    "SELECT *" should only be used for ad-hoc queries, not for application usage. My fave ad-hoc query tool, SQL Developer, already handles this for me as @Mike Kutz showed us earlier.

  • Sven W.
    Sven W. GermanyMember Posts: 10,562 Gold Crown

    And the next Idea would be for Except to support a list of column names instead of just one....

    Stefan Jager wrote:And the next Idea would be for Except to support a list of column names instead of just one.... 

    Of cause. And it would be a good idea to do so.

    Using * in queries can avoid a lot of problems that are created by repeating column list again and again and again. Especially when using large selects with a lot of subqueries.

  • Thorsten Kettner
    Thorsten Kettner Member Posts: 44 Blue Ribbon
    edited Sep 14, 2018 3:22AM

    We shouldn't dismiss the idea so easily. There are cases for which this idea actually makes much sense.

    One thing is views. There may be views where you want all columns except certain ones. You could of course list all those other columns, but by doing so, you would have to have in mind this view everytime you add a column to your table (because you want to add that column also to your view, as it is supposed to show the complete table only except for certain columns). Say you work for a company that sells products. Many apps select and show products, but a great part of them is not allowed to know the product's purchasing price:

        create view product_without_purchasing_price as select * except purchasing_price from product;

    Another typical scenario where we want all columns except one is when ranking rows with window functions:

        select a, b, c, ...
        from
        (
          select x.a, y.b, y.c, x.d, ..., row_number() over (partition by x.a order by y.b) as rn
          from x
          join y on y.id_x = x.id_x
          Join ...
        )
        where rn = 1

        order by x.a;

    where the select list is of course actually

        select * except rn

    Sven W.