What's the difference between sql_create: and sql: in LookML?

[Nan_Lin]

Both seem to work fine: for sql_create: I can add  CREATE OR REPLACE TABLE ${SQL_TABLE_NAME} as

Thanks.

[izzymiller · Accepted solution]

I believe the main difference here is that sql_create: will execute the statement exactly as provided, meaning you’d need to include CREATE OR REPLACE TABLE in the statement you provide, whereas sql: will accept a standard (ie: non DDL) SQL query beginning with SELECT and wrap it in our autogenerated CREATE OR REPLACE statement. 

This means that if you want to do some kind of custom DDL statement or a UDF or something, you’ll want to use sql_create: so you have more control over the sql executed, but for 99% of derived tables, you’ll want to use sql: since it’s just a bit more readable and lets Looker do it’s autogeneration magic. 

It’s a similar thing to the distinction between sql: and sql_on: for joins. 

using sql: in a join means you have to say sql: LEFT JOIN table1 ON table1.field = table0.field;

whereas with sql_on:, Looker does it’s autogeneration and all you have to say is sql_on: table1.field = table2.field. Same principle at work with sql_create / sql, if that makes sense.