Deciding on techniques to use

Data Manipulation in SQL

Mona Khalil

Data Scientist, Greenhouse Software

Different names for the same thing?

  • Considerable overlap...

Untitled presentation.png

  • ...but not identical!
Data Manipulation in SQL

Differentiating Techniques

Joins

  • Combine 2+ tables
    • Simple operations/aggregations

Correlated Subqueries

  • Match subqueries & tables
    • Avoid limits of joins
    • High processing time

Multiple/Nested Subqueries

  • Multi-step transformations
    • Improve accuracy and reproducibility

Common Table Expressions

  • Organize subqueries sequentially
  • Can reference other CTEs
Data Manipulation in SQL

So which do I use?

  • Depends on your database/question
  • The technique that best allows you to:
    • Use and reuse your queries
    • Generate clear and accurate results
Data Manipulation in SQL

Different use cases

Joins

  • 2+ tables (What is the total sales per employee?)

Correlated Subqueries

  • Who does each employee report to in a company?

Multiple/Nested Subqueries

  • What is the average deal size closed by each sales representative in the quarter?

Common Table Expressions

  • How did the marketing, sales, growth, & engineering teams perform on key metrics?
Data Manipulation in SQL

Let's Practice!

Data Manipulation in SQL

Preparing Video For Download...