In SQL is there a way to use select * on a join?-CodePudding

Using Snowflake,have 2 tables, one with many columns and the other with a few, trying to select * on their join, get the following error:

SQL compilation error:duplicate column name

which makes sense because my joining columns are in both tables, could probably use select with columns names instead of *, but is there a way I could avoid that? or at least have the query infer the columns names dynamically from any table it gets?

CodePudding user response：

You can prefix the name of the column with the name of the table:

select table_a.id, table_b.name from table_a join table_b using (id)

The same works in combination with *:

select table_a.id, table_b.* from table_a join table_b using (id)

It works in "join" and "where" parts of the statement as well

select table_a.id, table_b.* from table_a join table_b 
on table_a.id = table_b.id where table_b.name LIKE 'b%'

You can use table aliases to make the statement sorter:

select a.id, b.* from table_a a join table_b b 
on a.id = b.id

Aliases could be applies on fields to use in subqueries, client software and (depending on the SQL server) in the other parts of the statements, for example 'order by':

select a.id as a_id, b.* from table_a a join table_b b 
on a.id = b.id order by a_id

CodePudding user response：

I am quite sure snowflake will let you choose all from both halves of two tables via

SELECT a.*, b.*
FROM table_a AS a
JOIN table_b AS b 
  ON a.x = b.x

what you will not be able to do is refer to the named of the columns in GROUP BY indirectly, thus this will not work

SELECT a.*, b.*
FROM table_a AS a
JOIN table_b AS b 
  ON a.x = b.x
ORDER BY x

even though some databases know because you have JOIN ON a.x = b.x there is only one x, snowflake will not allow it (well it didn't last time I tried this)

but you can with the above use the alias name or the output column position thus both the following will work.

SELECT a.*, b.*
FROM table_a AS a
JOIN table_b AS b 
  ON a.x = b.x
ORDER BY a.x

SELECT a.*, b.*
FROM table_a AS a
JOIN table_b AS b 
  ON a.x = b.x
ORDER BY 1 -- assuming x is the first column

in general the * and a.* forms are super convenient, but are actually bad for performance.

when selecting you are now are risk of getting the columns back in a different order if the table has been recreated, thus making reading code unstable. Which also impacts VIEWs.

It also means all meta data for the table need to be loaded to know what the complete form of the data will be in. Where if you want x,y,z only and later a w was added to the table, the whole query plan can be compiled faster.

Lastly if you are selecting SELECT * FROM table in a sub-select and only a sub-set of those columns are needed the execution compiler doesn't need to prune these. And if all variables are attached to a correctly aliased table, if later a second table adds the same named column, naked columns are not later ambiguous. Which will only occur when that SQL is run, which might be an "annual report" which doesn't happen that often. wow, what a long use alias rant.

CodePudding user response：

If you're after a result that includes all the distinct non-join columns from each table in the join with the join columns included in the output only once (given they will be identical for an inner-join) you can use NATURAL JOIN.

e.g. select * from d1 natural inner join d2 order by id;

See examples: https://docs.snowflake.com/en/sql-reference/constructs/join.html#examples