Learning Snowflake SQL and Scripting

Chapter 9. From Clause Revisited

While the different types of joins (inner, outer, cross) were discussed in Chapter 3, there is a lot more that can be done in the from clause in Snowflake. This chapter will explore some of these options.

Note

This chapter makes use of the Employee table, which was created and populated back in Chapter 3. If you would like to run the example queries from this chapter but don’t have this table in your schema, you can run the following statement:

create table employee
 (empid number, emp_name varchar(30), mgr_empid number)
as select *
   from (values
          (1001, 'Bob Smith', null),
          (1002, 'Susan Jackson', 1001),
          (1003, 'Greg Carpenter', 1001),
          (1004, 'Robert Butler', 1002),
          (1005, 'Kim Josephs', 1003),
          (1006, 'John Tyler', 1004));

Hierarchical Queries

Some data is hierarchical in nature, such as a family tree, where each data point has a relationship with other data points above and/or below. Another example is an employee table, where each employee’s row contains a value to identify the employee’s manager. There is already an Employee table in the learning_sql schema, so let’s take another look at the data:

PUBLIC>select empid, emp_name, mgr_empid
       from employee;
      

 +-------+----------------+-----------+ | EMPID | EMP_NAME | MGR_EMPID | |-------+----------------+-----------| | 1001 | Bob Smith | NULL | | 1002 | Susan Jackson | 1001 | | 1003 | Greg Carpenter | 1001 | | 1004 | Robert Butler | ...