All the SQL You Need For Data Science, Part 1

The scientific method is the most important skill for a data scientist. But once you have a basic understanding of it, and even to learn and practice it, you need some data. And in practice, that almost always means using SQL.

In this series of posts, I will go through all the SQL you need to know, based on my experience using it day to day and what I have seen come up in technical interviews focusing on SQL.

There are lots of courses, tutorials and practice sites. Choose what works best for your style. But keep in mind that many of them will try to teach you the entire language, instead of focusing on what is most useful for data science.

So my series does not cover all of SQL, just the parts I think you need. I want you to get up to speed quickly and focus on the most important parts.

Use this guide as a checklist of what to focus on. This part covers selecting, filtering, joining, and aggregating. The next part will cover organizing complex queries, window functions, dates and strings, sampling, a few tips and tricks, and, what you don’t need to know.

We will use PostgreSQL throughout. Keep in mind that SQL syntax requires the different statements to appear in a specific order when you try these out.

Basic selection and exploration #

Always use limit when exploring and developing, but almost never use it in your final query. Typically when you are selecting the exact data you need for your analysis, you will be limiting the amount of data by filtering the rows on specific criteria (e.g. date ranges) and then aggregating.

select
    *
from
    orders
limit
    10 -- limit rows returned while exploring
;

It’s OK to use * when exploring, but never use it in your final query. Always list exactly the columns you need. Otherwise you might not notice if and when the underlying table changes.

I like to put each column on its own line, with the comma in front. Some people prefer it in the back.

select
    order_id -- comma goes in front of each column
    , customer_id
    , order_date
    , amount
from
    orders
limit
    10
;

Large data is often partitioned by date, so filter to a short time window when exploring.

select
    *
from
    events
where
    -- filter to a short window on partitioned data
    event_date between '2026-07-01' and '2026-07-02'
limit
    10
;

Ordering #

Sorting is called ordering in SQL, and you can do it ascendingly (smallest first) or descending (largest first). For strings the order is alphabetical.

select
    *
from
    orders
order by
    order_date desc -- desc means largest/most recent first
limit
    10
;

You can also apply several orderings, where the ones after the first will be used to break ties within the first. In this example, rows will first be sorted by amount, then by order_date.

select
    *
from
    orders
order by
    amount asc
    , order_date desc -- breaks ties within amount
;

Filtering rows #

Filtering is done using the where clause. Notice that in SQL checking for equality uses just one equal sign, =.

select
    *
from
    orders
where
    status = 'completed' -- single =, not ==
    and amount > 100
;

in is useful if you want to check that the value is one of several values, without having to write multiple checks. You can also invert this check with not in.

select
    *
from
    orders
where
    customer_id in (1, 2, 3) -- shorthand for several OR conditions
;

Use is null to keep only the rows where a column is null (not = null, which never matches anything).

select
    *
from
    orders
where
    cancelled_at is null -- is null, not = null
;

Joins #

It wouldn’t be SQL if there weren’t any joins! Joins are the bread and butter of SQL. To join is to take rows from two tables, one left and one right, and combine them, according to specific logic that you specify. To do a join you need to specify two things: the columns that map the rows between the tables, and the join type, which decides what happens to rows that don’t have a match.

The columns are specified with the on clause, which introduces how the columns from the left table should relate to the columns from the right table. In this case, customer_id from the customers table must equal the customer_id from the orders table. While you can use any operator, you will almost always use equals (=).

Because with joins you are selecting columns from at least two tables, you have to specify the table name and then the column, separated by a period (.). Often table names are long and can be tedious to type and read, so you can alias them like in this example.

select
    a.customer_id -- refers to `customer_id` in the `customers` table
    , a.first_name
    , b.order_id
from
    customers a -- customers table is aliased as `a`
left join
    orders b -- orders table is aliased as `b`
    on b.customer_id = a.customer_id
;

Join Types #

The join type is what decides what happens to rows that don’t have a match:

The most common joins are inner and left joins. In addition to these typical joins, there are a few special ones that come up: anti join, self join, cross join, and range join.

Anti Join #

An anti join is where you want all the rows in the left table that do not exist in the right table.

select
    a.customer_id
    , a.first_name
from
    customers a
left join
    orders b
    on b.customer_id = a.customer_id
where
    b.order_id is null -- no matching order means no orders at all
;

Self Join #

A self join is where you join one table to itself. This can be useful if you want to relate rows to other rows in the same table, for example, matching a reply to the message it’s replying to.

select
    a.message_id
    , a.text as reply_text
    , b.text as original_text -- b is the same table, aliased to represent the original message
from
    messages a
inner join
    messages b
    on a.reply_to_message_id = b.message_id
;

Note that this is as far as you should go with self joins. SQL does not handle recursion well. If you are working with graphs or networks, I recommend using SQL to extract just the events, and then use a dedicated data science language (Python or R, Spark) with a graph library.

Cross Join #

A cross join is where all the rows in the left table are matched with all the rows in the right table. This is very rare to do on purpose (but is easy to do by mistake). The most common reason to do it is to generate all the combinations of dates and the unit you’re interested in, for example products.

select
    dates.date
    , products.product_id
from
    dates
cross join
    products -- every date paired with every product
;

Range Join #

A range join is where rows are matched not just on equality of column values, but on whether a column falls within a specific range. This is achieved by adding additional conditions to the on clause, not by changing the join type.

select
    orders.order_id
    , price_history.price
from
    orders
inner join
    price_history
    on price_history.product_id = orders.product_id
    and orders.order_date >= price_history.valid_from -- range join: matches on a window, not equality
    and orders.order_date < price_history.valid_to
;

Grouping and aggregating #

Very often when extracting data for analysis, you will want to aggregate the data by the units you care about. For example, counting the number of orders by customer. Often, aggregating and then extracting will allow you to work in your local machine’s memory, which is the fastest way to make progress.

To aggregate you use an aggregation function, such as count, together with a group by clause. You can group by several columns, but they all have to be present in the select statement. Note also the use of distinct within the count: without the distinct all rows will be counted, which can inflate aggregate numbers if there are duplicates. It is always useful to check both with and without distinct to make sure.

select
    customer_id
    , count(distinct order_id) as nr_orders -- distinct avoids inflated counts from duplicates
from
    orders
group by
    customer_id
;

Aggregate functions #

This is an example of common aggregate functions.

select
    customer_id
    , count(*) as nr_orders -- counts rows
    , count(distinct product_id) as nr_distinct_products -- counts unique values
    , sum(amount) as total_amount
    , avg(amount) as avg_amount
    , min(amount) as min_amount
    , max(amount) as max_amount
from
    orders
group by
    customer_id
;

Median #

Use percentile_cont(0.5) to calculate the median. It can of course be used to calculate any other percentile as well. Notice the within group (order by amount) additional keywords, this is a window function that we will cover in the next part.

select
    customer_id
    , percentile_cont(0.5) within group (order by amount) as median_amount -- 0.5 = median, change for other percentiles
from
    orders
group by
    customer_id
;

Handling nulls #

Be careful with null values, in the second example we use coalesce, which takes the first non-null value, to replace null discount_amount with zero.

select
    customer_id
    , count(*) as nr_rows -- counts all rows, including nulls
    , count(discount_code) as nr_non_null_discount_code -- count(col) skips nulls
    , 1.0 - count(discount_code) / count(*) as proportion_null
from
    orders
group by
    customer_id
;

select
    customer_id
    , avg(coalesce(discount_amount, 0)) as avg_discount -- coalesce replaces null with a default value
from
    orders
group by
    customer_id
;

In actual data there are often other values that should be null but aren’t. For numeric columns, make sure to check the minimum and maximum, to see if there are other special values that really should be null. For string columns, you can count the number of rows per value to identify these.

In the next installment we’ll cover organizing complex queries, window functions, dates and strings, sampling, a few tips and tricks, and most importantly, what you don’t need!