雪花连接是否不区分大小写

huangapple
go评论89阅读模式
英文:

Is snowflake join case insensitive

问题

我试图连接两个表，但似乎连接不考虑大小写。Snowflake中的连接是否不区分大小写？

以下查询是不区分大小写地连接的

  1. with cte_1 as 
  2. (select  VIN,YEAR,MAKENAME,MODELNAME from TBL_A),
  4. cte_2 as 
  5. (select YEAR,MAKE_CHROME,MODEL_CHROME from TBL_B)
  7. select * from 
  8. cte_1 a 
  9. inner join cte_2 b on 
  10.     a.YEAR=b.year and 
  11.     a.MAKENAME=b.MAKE_CHROME and 
  12.     a.MODELNAME=b.MODEL_CHROME;

雪花连接是否不区分大小写

英文:

I am trying to join two tables but seems like the join is not considering case. Is the join in snowflake case insensitive?

Following query is being joined case insensitively

  1. with cte_1 as 
  2. (select  VIN,YEAR,MAKENAME,MODELNAME from TBL_A),
  4. cte_2 as 
  5. (select YEAR,MAKE_CHROME,MODEL_CHROME from TBL_B)
  7. select * from 
  8. cte_1 a 
  9. inner join cte_2 b on 
  10.     a.YEAR=b.year and 
  11.     a.MAKENAME=b.MAKE_CHROME and 
  12.     a.MODELNAME=b.MODEL_CHROME;

雪花连接是否不区分大小写

答案1

得分: 0

根据NickW的评论，字符串默认情况下没有匹配，因此：

  1. with cte_1(YEAR, MAKENAME, MODELNAME) as (
  2.     select * from values
  3.         (2022, 'FORD', 'BRONCO')
  4. ), cte_2(YEAR, MAKE_CHROME, MODEL_CHROME) as (
  5.     select * from values
  6.         (2022, 'Ford', 'Bronco')
  7. )
  8. select * 
  9. from cte_1 as a  
  10. join cte_2 b 
  11.     on a.YEAR=b.year 
  12.         and a.MAKENAME = b.MAKE_CHROME 
  13.         and a.MODELNAME = b.MODEL_CHROME;

没有结果：

雪花连接是否不区分大小写

所以如果我们使用COLLATE更改数据类型，它现在将“匹配”

  1. with cte_1(YEAR, MAKENAME, MODELNAME) as (
  2.     select 
  3.         column1
  4.         ,COLLATE(column2, 'en-ci')
  5.         ,COLLATE(column3, 'en-ci')
  6.     from values
  7.         (2022, 'FORD', 'BRONCO')
  8. ), cte_2(YEAR, MAKE_CHROME, MODEL_CHROME) as (
  9.     select
  10.         column1
  11.         ,COLLATE(column2, 'en-ci')
  12.         ,COLLATE(column3, 'en-ci')
  13.     from values
  14.         (2022, 'Ford', 'Bronco')
  15. )
  16. select * 
  17. from cte_1 as a  
  18. join cte_2 b 
  19.     on a.YEAR=b.year 
  20.         and a.MAKENAME=b.MAKE_CHROME 
  21.         and a.MODELNAME=b.MODEL_CHROME;

给出结果：

雪花连接是否不区分大小写

这意味着当创建表时，它为列集合设置了排序规则

  1. create table TBL_A(
  2.     YEAR number, 
  3.     MAKENAME text COLLATE 'en-ci',
  4.     MODELNAME text COLLATE 'en-ci');
  6. insert into tbl_a values (2022, 'FORD', 'BRONCO');
  8. with cte_1 as (
  9.     select * from TBL_A
  10. ), cte_2(YEAR, MAKE_CHROME, MODEL_CHROME) as (
  11.     select
  12.         column1
  13.         ,COLLATE(column2, 'en-ci')
  14.         ,COLLATE(column3, 'en-ci')
  15.     from values
  16.         (2022, 'Ford', 'Bronco')
  17. )
  18. select * 
  19. from cte_1 as a  
  20. join cte_2 b 
  21.     on a.YEAR=b.year 
  22.         and a.MAKENAME=b.MAKE_CHROME 
  23.         and a.MODELNAME=b.MODEL_CHROME;

因此，要查找这个信息，我们可以查看信息模式中的COLUMNS视图：

  1. select 
  2.     table_name,
  3.     column_name,
  4.     data_type,
  5.     collation_catalog,
  6.     collation_name,
  7.     collation_schema
  8. from information_schema.columns
  9. where table_name = 'TBL_A';

雪花连接是否不区分大小写

英文:

As per NickW comment, by default strings have no matching, thus:

  2. with cte_1(YEAR, MAKENAME, MODELNAME) as (
  3.     select * from values
  4.         (2022, 'FORD', 'BRONCO')
  5. ), cte_2(YEAR, MAKE_CHROME, MODEL_CHROME) as (
  6.     select * from values
  7.         (2022, 'Ford', 'Bronco')
  8. )
  9. select * 
  10. from cte_1 as a  
  11. join cte_2 b 
  12.     on a.YEAR=b.year 
  13.         and a.MAKENAME = b.MAKE_CHROME 
  14.         and a.MODELNAME = b.MODEL_CHROME;

gives no results:

雪花连接是否不区分大小写

So if we change the data type with COLLATE it will now "match"

  1. with cte_1(YEAR, MAKENAME, MODELNAME) as (
  2.     select 
  3.         column1
  4.         ,COLLATE(column2, 'en-ci')
  5.         ,COLLATE(column3, 'en-ci')
  6.     from values
  7.         (2022, 'FORD', 'BRONCO')
  8. ), cte_2(YEAR, MAKE_CHROME, MODEL_CHROME) as (
  9.     select
  10.         column1
  11.         ,COLLATE(column2, 'en-ci')
  12.         ,COLLATE(column3, 'en-ci')
  13.     from values
  14.         (2022, 'Ford', 'Bronco')
  15. )
  16. select * 
  17. from cte_1 as a  
  18. join cte_2 b 
  19.     on a.YEAR=b.year 
  20.         and a.MAKENAME=b.MAKE_CHROME 
  21.         and a.MODELNAME=b.MODEL_CHROME;

gives:

雪花连接是否不区分大小写

This implies that when your table was created it had collation for the column set

  1. create table TBL_A(
  2.     YEAR number, 
  3.     MAKENAME text COLLATE 'en-ci',
  4.     MODELNAME text COLLATE 'en-ci');
  6. insert into tbl_a values (2022, 'FORD', 'BRONCO');
  8. with cte_1 as (
  9.     select * from TBL_A
  10. ), cte_2(YEAR, MAKE_CHROME, MODEL_CHROME) as (
  11.     select
  12.         column1
  13.         ,COLLATE(column2, 'en-ci')
  14.         ,COLLATE(column3, 'en-ci')
  15.     from values
  16.         (2022, 'Ford', 'Bronco')
  17. )
  18. select * 
  19. from cte_1 as a  
  20. join cte_2 b 
  21.     on a.YEAR=b.year 
  22.         and a.MAKENAME=b.MAKE_CHROME 
  23.         and a.MODELNAME=b.MODEL_CHROME;

so to find that out we can look at the COLUMNS view in the information schema:

  1. select 
  2.     table_name,
  3.     column_name,
  4.     data_type,
  5.     collation_catalog,
  6.     collation_name,
  7.     collation_schema
  8. from information_schema.columns
  9. where table_name = 'TBL_A';

雪花连接是否不区分大小写

huangapple
  • 本文由 发表于 2023年7月13日 19:25:59
  • 转载请务必保留本文链接：https://go.coder-hub.com/76678842.html
  • snowflake-cloud-data-platform
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定