Learning Snowflake SQL and Scripting

Chapter 3. Joins

The first two chapters introduced the from clause and showed a number of examples with two tables joined together. This chapter delves deeper into multitable joins, including the use of the join types inner, outer, and cross.

What Is a Join?

Some people find joins to be confusing, so I find it helpful to start with a very brief overview of database design. The sample database used for many of the examples in this book includes data about customers submitting orders for a set of parts available from a number of suppliers. Such a database might be used by an online car parts or appliance repair business. If you were the database architect charged with designing the database for one of these businesses, you would likely start with the business definition (customers submitting orders for a set of parts available from a number of suppliers) and break it into a number of business entities, such as:

• Customer
• Part
• Supplier
• Orders

Your next step would be to determine all the things you need to know about each of these entities, create columns to hold this data, and build database tables for each entity. Some of these columns would be informational, such as a supplier’s name and address, and other columns would be used for traversal between tables, such as the custkey column in the Orders table to link to the corresponding row in the Customer table.

Now let’s say that you have been hired by the business and your first task is to write a report showing all of the orders ...