Tech-Discussion

              LATERAL JOIN We often need to use different JOINS along with joining conditions to pull data from multiple tables. MS-SQL Server   We can use any JOIN, here I have used INNER JOIN. For any join, you must mention columns in which you will join both tables. In  Snowflake,  we can use a similar approach as above.  However, Snowflake has an additional JOIN called  LATERAL JOIN.  This looks like a sub-query. You can use lateral join along with other joins like inner join & outer join. For more details, you can refer to the following  LATERAL JOIN . Yogesh Shinde LinkedInProfile <<< Back   Next >>>

             NATURAL JOIN To pull data from multiple tables, we often need to use different JOINS along with joining conditions. MS-SQL Server   We can use any JOIN, here I have used INNER JOIN. For any join, you must mention columns in which you will join both tables. In  Snowflake,  we can use a similar approach as above.  However, Snowflake has an additional JOIN called  NATURAL JOIN.  You can use this clause when both tables have a column with the same name. In this case, Store_ID is present in both tables.  You don't need to mention the column name when you are joining both tables.  For more details, you can refer to the following  NATURAL JOIN . Yogesh Shinde LinkedInProfile <<< Back   Next >>>

             Using Many times to pull data from multiple tables we need to use different JOINS along with joining conditions. MS-SQL Server   We can use any JOIN, here I have used INNER JOIN. For any join, you have to mention columns in which you will join both tables. In  Snowflake,  we can use a similar approach as above.  However, Snowflake is a clause  USING.  You can use this clause when both tables have a column with the same name. In this case, Store_ID is present in both tables.  For more details, you can refer to the following  USING . Yogesh Shinde LinkedInProfile <<< Back   Next >>>

            DUPLICATE_COUNT If you want to determine how many duplicate groups are present under any particular column. In the dataset below, Ontario presents 3 times, Saskatchewan presents 2 times, and LA only once.  It shows  Ontario  &  Saskatchewan  are duplicate values, and we expect 2 as duplicate groups. + ----------+--------------+ | STORE_ID | Province     | | ----------+--------------+ |         1 | Ontario      | |         2 | Saskatchewan | |         3 | Ontario      | |         4 | Ontario      | |         5 | Saskatchewan | |         6 | LA           | + ----------+--------------+ MS-SQL Server   Using the aggregate function we can achieve as below: In  Snowflake,  we can use a similar appr...

            RATIO_TO_REPORT If you have a table in which amounts are present and you want to calculate the percentage distribution of those amounts as below: + ----------+--------+----------------+ | STORE_ID | PROFIT | PERCENT_PROFIT | | ----------+--------+----------------| |         1 | 500 . 00 |     42          | |         2 | 250 . 00 |     21          | |         3 | 450 . 00 |     38          | |         4 |   NULL |     NULL        | + ----------+--------+----------------+ MS-SQL Server   Using the SUM function and OVER clause, you can achieve the above output as below: In  Snowflake,  we can use a similar approach as above.  However, Snowflake has a different function  RATIO_TO_REPORT : ...

           Select data from a table If you want to select all columns from a table then we use the SELECT command.  MS-SQL Server   Using the  SELECT command we can fetch the data from the table below: In  Snowflake,  we can use a similar approach as above.  However, Snowflake has a different approach i.e you can mention the below command:  Alternatively, you can use the below command as well. For more details, you can refer to the following  Literals-Table . Yogesh Shinde LinkedInProfile <<< Back   Next >>>

           Select a Column using column order If you want to select any column from a table then you have to specify the column name in the SELECT clause.  You need to select the third column from this table. MS-SQL Server   You need to perform multiple steps using  SP_RENAME . You can mention the column name in the SELECT clause as below: In  Snowflake,  we can use a similar approach as above.  However, Snowflake has a different approach i.e you can mention column order instead of column name as below:  For more details, you can refer to the following  ColumnOrder . Yogesh Shinde LinkedInProfile <<< Back   Next >>>

           Swap Tables Suppose y ou have table Main_table,  if you want to do a lot of business logic on existing data for temporary purposes, which needs approval before committing.  Hence you create another table  Main_Table_Staging  from table  Main_Table.  Once you receive the approval, you must replace this table  Main_Table_Staging  with the existing table  Main_Table . In this case, you need to swap both tables. MS-SQL Server   You need to perform multiple steps using SP_RENAME . In  Snowflake,  we can use a similar approach as above. However, Snowflake has a different operation  SWAP . For more details, you can refer to the following  SWAP . Yogesh Shinde LinkedInProfile <<< Back   Next >>>