-2

How can I extract columns by column name from all columns selected with SELECT *

Table1 cols = x,y,z,t
Table2 cols = a,b,z,t

sample query like;

Select *
EXTRACT t
LEFT JOIN Table1
ON CAST(Table1.t AS SMALLDATETIME) = CAST(Table2.t AS SMALLDATETIME)

i want output x,y,a,b,t

Emre Oz
  • 45
  • 5
  • 1
    What do you mean "extract" ? Why not SELECT Table1.t FROM Table2 LEFT JOIN.... – Max Jul 26 '22 at 10:10
  • There is no "select all but these columns" functionality. Start typing – SMor Jul 26 '22 at 10:35
  • There is no way to say “select all columns except these.” You can build the list dynamically using STRING_AGG against sys.columns.name where name not in (columns,to,ignore) but an even easier way is to drag the columns node onto your query window and manually remove the trouble columns (see examples [here](https://sqlblog.org/2009/10/10/bad-habits-to-kick-using-select-omitting-the-column-list)). – Aaron Bertrand Jul 26 '22 at 10:53

2 Answers2

1

There are three ways to specify the columns you want in your result set.

  1. select *, which will return all columns available in the query.
  2. select MyTable.* which will return all columns from MyTable. If you have used an alias for the table, you can also use MyAlias.*, which is equivalent.
  3. Specify all of the columns you want individually, by name.

You can also combine techniques (2) and (3).

It is a good idea to always use technique 3 only.

There is no way to specify columns that you don't want to return, unfortunately.

Examples:

This query will return all columns {T.a, T.b, U.c, U.d}:

create table T (a int, b int);
create table U (c int, d int);

select *
from   T
join   U on t.a = u.c

This query will return only the columns on T, ie {T.a, T.b}

select T.*
from   T
join   U on T.a = U.c

This query will return the columns {T.a, T.b, U.d}

select T.a, T.b, U.d
from   T
join   U on T.a = U.c

This query will return all columns from T, plus column d from U:

select  T.*, U.d
from    T
join    U on t.a = u.c

There is no way to do anything like this:

-- syntax error
select * except U.c
from   T
join   U on T.a = U.c
allmhuran
  • 4,154
  • 1
  • 8
  • 27
  • In 10 different tables, there are an average of 10 columns each, and they all have 3-4 empty columns, all with the same empty column name. But the names of the desired columns are different, if I need to specify a name instead of Select *, I have to select 70 columns with select. this seems too long and wrong. – Emre Oz Jul 26 '22 at 10:27
  • @EmreOz A table can't have an empty column name. But if you put an expression in your query, for example `select Column1 + Column2`, you need to provide a name for the result: `select MyResultColumName = Column1 + Column2`, or `select Column1 + Column2 as MyResultColumnName`. It may "seem" too long and wrong, but that's how it is. Thare are good reasons to specify all of your column names and never use `*`. See [here](https://stackoverflow.com/questions/321299/what-is-the-reason-not-to-use-select) – allmhuran Jul 26 '22 at 10:31
  • I'm sorry for the problem I had in the translation, what I mean when I say empty column name is the unwanted column, there are 3-4 unwanted columns in each table and these columns have the same name. For example, columns named 1,2,3 in each table are empty/unnecessary and I don't want them in the result of my query. – Emre Oz Jul 26 '22 at 10:33
  • 1
    @EmreOz Specify all of the columns you want in the result. There is no way to specify the columns you don't want (and there are good reasons to specify all of the columns you want, and never use `*`. I added a link in my previous comment and in the answer) – allmhuran Jul 26 '22 at 10:34
0

I guess you want

SELECT Table1.x, Table1.y, Table2.a, Table2.b, Table1.t
  FROM Table2
  LEFT JOIN Table1 
      ON CAST(Table1.t AS SMALLDATETIME)
       = CAST(Table2.t AS SMALLDATETIME)

You don't extract columns from tables, you SELECT them.

O. Jones
  • 103,626
  • 17
  • 118
  • 172
  • In the example I gave, the number of columns is 5, but in my database it is close to 100, so I have to delete it from the column name. – Emre Oz Jul 26 '22 at 10:18