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

CodePudding user response：

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

CodePudding user response：

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.