CYBER WEEK 50% SALE 📈 🚀
FORUM Everything about SQL INNER JOIN vs LEFT JOIN performance

INNER JOIN vs LEFT JOIN performance

Are there any performance differences between INNER JOIN and LEFT JOIN? For instance, this following subquery from lesson 207 could have been written with an INNER JOIN.

WITH user_activity AS (
  SELECT
    u.user_id,
    u.created_at::date AS signup_date,
    e.created_at::date AS activity_date,
    COUNT(*) AS events_counts
  FROM mobile_analytics.events u
  LEFT JOIN mobile_analytics.events e
    ON e.user_id = u.user_id  
  WHERE
    u.action = 'signup'  
  GROUP BY 1, 2, 3
  ORDER BY signup_date ASC, user_id ASC
)

SELECT *
FROM user_activity

REPLIES

Hi @tangjm 👋

That’s an awesome question, my first guess is that LEFT JOIN should be a bit slower. LEFT JOIN produces more data because there are event records without user_id:

SELECT 
  'left_join_records' AS label,
  COUNT(*) AS records_count
FROM mobile_analytics.events u
LEFT JOIN mobile_analytics.events e
  ON e.user_id = u.user_id

UNION

SELECT
  'inner_join_records' AS label,
  COUNT(*) AS records_count
FROM mobile_analytics.events u
INNER JOIN mobile_analytics.events e
  ON e.user_id = u.user_id

Altough I’m sure that the SQL planner in PostgreSQL counts WHERE conditions and the final result set size is the same:

SELECT 
  'left_join_records' AS label,
  COUNT(*) AS records_count
FROM mobile_analytics.events u
LEFT JOIN mobile_analytics.events e
  ON e.user_id = u.user_id
WHERE
  u.action = 'signup'

UNION

SELECT
  'inner_join_records' AS label,
  COUNT(*) AS records_count
FROM mobile_analytics.events u
INNER JOIN mobile_analytics.events e
  ON e.user_id = u.user_id
WHERE
  u.action = 'signup'  

I believe the speed for INNER JOIN and LEFT JOIN queries should be equal. I see this when I run a benchmark on my computer:

benchmark

Simulation with a big dataset

I also generated several gigabytes of events data to test our assumption with slow queries (~30 sec in this experiment).

As you can see, both query duration distributions look very similar:

INNER JOIN

LEFT JOIN

WRITE A REPLY

“no fuss, just things you actually need”

Start learning with SQL Habit today

Master Data Analysis with SQL through the story of how a startup succeeded through data.