Sometimes you think you have something down. With years of SQL Server experience, you would think you’d know at least the basics of the SELECT statement no matter the relational database. Well, my friends, you’d be wrong.
With Snowflake, we could do many different things that we’re not used to seeing with a SELECT statement. We’re all used to seeing this – SELECT * and it shows all kinds of columns.
With Snowflake, we can tell Snowflake NOT to show certain columns by using the EXCLUDE operator.
Notice that we took out several columns, but still showed a lot more. Yes, we do have to use parenthesis after the EXCLUDE operator if there is more than 1 column. I would just get in the habit of using parenthesis even if there is only one column to exclude.
Now, we can also rename columns when doing this – since it’s not quite the same as just giving a column an alias. But, Snowflake has thought of a way to make it happen:
You must remember that if you are using the EXCLUDE statement you, you can not also use the RENAME statement. You also should use the parenthesis around the RENAME statement whether you are using 1 or more columns. But, most importantly, you should not try to rename a column that you are excluding.
Now sometimes I want to look at a column by its position. In SQL Server, I’d have to do some fun combination of INFORMATION_SCHEMA.Columns and the table you’re working with. In Snowflake, I only have to use the “$” symbol and the position I want, for example:
Notice, that is $1 and $2 that I’m looking at. I could also use an alias here – like I did for the first column in this query. I didn’t do it for the second column just so that you – my friendly reader – can see that the resultset shows $2 instead of anything else.
So many things quite like SQL Server. So many things that are just not like SQL Server at the same time. That is Snowflake.
And that my friends, is the end of our time together for this week. Join us next week when we’ll talk about the INSERT query and the differences that we need to get used to. Until then!
1 comment