Why Your Joins Are Slowing Down Your SQL Queries
Are you tired of waiting two hours for a SQL script to finish just so you can review your daily reports?
Early in my career, I wrote a query that joined tables across two different servers through a linked server. Not only was it painfully slow, it started to drag down the performance of the entire production environment. The fix was simple. Once implemented, the query that took 6 hours finished in 10 minutes.
This article walks you through nine join patterns that make queries faster and easier for the optimizer to run. Each one comes with a short example. Even if you think an AI coding assistant can write an efficient query from a simple prompt, read on. These are the patterns AI tools often miss, because they depend on your data, not just your syntax.
1. Put INNER JOINs Before LEFT JOINs
Query performance is rarely a problem when you join two or three tables. It becomes a problem when you’re building a fact table for a data warehouse and stringing together ten or more.
Here is a reliable pattern. Start with your main fact table, the one your results are built around. Add INNER JOINs next, and finish with LEFT JOINs.
Here’s why. An INNER JOIN keeps only rows that match on both sides, so it shrinks the working set early. Modern optimizers can reorder inner joins on their own, but outer joins limit how much they can reorder. Putting the inner joins first gives the optimizer fewer rows to carry into the LEFT JOINs.
There’s also a correctness trap. An INNER JOIN that depends on a LEFT-joined table quietly turns that LEFT JOIN back into an INNER JOIN and drops rows you needed.
Do this:
SELECT s.order_id, c.customer_name, p.product_name, pr.promo_name
FROM sales s
INNER JOIN customers c ON s.customer_id = c.customer_id
INNER JOIN products p ON s.product_id = p.product_id
Instead of this:
FROM sales s
-- Drops every sale without a promotion: the LEFT JOIN
-- has silently become an INNER JOIN.
INNER JOIN customers c ON s.customer_id = c.customer_id;
The takeaway. Shrink the data with inner joins first, then add optional details with left joins.
2. Join on Keys, and Make Sure They’re Indexed
This one is obvious to most SQL professionals. Join on primary and foreign keys. What’s less obvious is that a foreign key doesn’t guarantee a fast join. Microsoft’s SQL Server documentation is explicit about this.
“Unlike primary key constraints, creating a foreign key constraint doesn’t automatically create a corresponding index.”
-- SQL Server: index the foreign key columns you join on
CREATE INDEX ix_sales_customer_id ON dbo.sales (customer_id);
CREATE INDEX ix_sales_product_id ON dbo.sales (product_id);
Lakehouse platforms like Databricks don’t use traditional indexes. There, the equivalent is organizing data by the columns you join and filter on most:
-- Databricks: cluster the table by its most-used join key
ALTER TABLE silver.sales CLUSTER BY (customer_id);
The takeaway. A key only speeds up a join if the engine can use it to find matching rows quickly.
3. Don’t Glue Columns Together to Make a Key
Here’s where pattern 2 gets muddier. Suppose the table you need has no single key, so you concatenate three columns to make one. This is where performance really suffers. The engine has to convert every column to the same data type and build the combined string for every row. Only then can it compare anything, and no index can help.
The simplest fix is to join on the columns directly. A multi-column join can use a composite index and avoids building strings at all.
SELECT d.*, t.target_amount
FROM daily_sales d
INNER JOIN sales_targets t
AND d.product = t.product
AND d.sub_product = t.sub_product;
If you join on that combination constantly, build a key once, when you load the table, and store it. A hash of the columns gives every row the same key in both tables:
-- Databricks SQL: create the key once, at load time
CREATE OR REPLACE TABLE silver.daily_sales AS
SELECT sha2(concat_ws('|', CAST(sale_date AS STRING),
product, sub_product), 256) AS product_day_key,
*
FROM bronze.daily_sales;
-- Do the same for sales_targets, then join on one column:
-- ON d.product_day_key = t.product_day_key
Two details matter here. The | separator stops different values from producing the same key (“AB” + “C” and “A” + “BC” would otherwise match). And make sure none of the key columns are NULL. A common mistake, even in AI-generated code, is to number rows with ROW_NUMBER() in each table and join on that. Each table numbers its rows independently, so row 42 in one table has nothing to do with row 42 in the other.
The takeaway. Join on the real columns, or build a stored key once. Never rebuild a key on every query.
4. Don’t Transform Your Join Keys
Wrapping a join column in a function such as CAST, TRIM, or UPPER forces the engine to calculate that value for every row. It also usually stops the engine from using an index on that column. Markus Winand, author of SQL Performance Explained, makes this point on his site, Use The Index, Luke. Joining a string to an integer is just as bad, because it forces a conversion on every row.
-- Avoid: converts every row, every time the query runs
ON CAST(r.customer_id AS INT) = c.customer_id
-- Better: fix the data type once, when loading the silver layer
CREATE OR REPLACE TABLE silver.orders AS
SELECT CAST(customer_id AS INT) AS customer_id, order_id, amount
FROM bronze.orders;
I see this most often when joining raw data in the bronze layer, where IDs often arrive as text.
The takeaway. Clean and convert keys once, upstream, so both sides of every join use the same data type.
5. Filter Early, Filter Often
This is the trick junior developers miss most. If you only need a fraction of a large table, filter it before you join, not after. This single change once took one of my queries from 3 hours to 5 minutes.
Optimizers push simple WHERE filters down on their own. Staging pays off when the filter logic is complex, when you reuse the filtered data several times, or when the data lives on another server.
Choose the staging method carefully. A CTE is fine for readability when you reference the result once. It is not stored, though. In many engines, including SQL Server, a CTE runs again every time you reference it. If you reuse the filtered data several times in the same pipeline, use a temp table or a small staging table:
-- SQL Server: stage only the rows and columns you need
SELECT order_id, customer_id, amount
INTO #recent_sales
FROM dbo.sales
WHERE sale_date >= DATEADD(DAY, -30, CAST(GETDATE() AS DATE));
CREATE INDEX ix_recent_customer ON #recent_sales (customer_id);
SELECT r.order_id, c.customer_name, r.amount
FROM #recent_sales r
INNER JOIN dbo.customers c ON r.customer_id = c.customer_id;
On long scripts, repeat this several times to keep shrinking the row count. A small table almost always beats a large, well-indexed one.
The takeaway. The fastest row to join is the one you filtered out first.
6. Prune Your Columns
Pattern 5 reduces rows. Pattern 6 reduces columns. If a column doesn’t belong in the final table, don’t carry it through the process.
-- Avoid
SELECT *
FROM sales s
INNER JOIN customers c ON s.customer_id = c.customer_id;
-- Better
SELECT s.order_id, s.amount, c.customer_name
FROM sales s
INNER JOIN customers c ON s.customer_id = c.customer_id;
This matters even more on columnar platforms like Databricks, Snowflake, and Microsoft Fabric. They read only the columns you ask for, so every unused column in SELECT * is data read, moved, and held in memory for nothing.
The takeaway. Every column you carry costs memory and time, so only bring the ones you need.
7. Avoid Accidental Row Explosions
A CROSS JOIN pairs every row in one table with every row in another. Two tables of 1,000 rows produce 1,000,000. It has real uses, such as pairing a short list of stores with a calendar of dates. On large tables, though, it’s destined to fail.
The more common problem is an accidental explosion, such as joining on a key that isn’t actually unique. If a customer appears twice in customers, every one of their orders is doubled. Check before you join:
-- Find keys that aren't actually unique
SELECT customer_id, COUNT(*) AS copies
FROM customers
GROUP BY customer_id
HAVING COUNT(*) > 1;
If you do need a cross join, build it once from small, pre-filtered tables. Store the result in a temp table rather than a CTE so it isn’t recalculated each time it’s referenced.
The takeaway. Know how many rows each join should return, and check that it does.
8. Use EXISTS to Stop at the First Match
Sometimes you don’t need data from the second table. You only need to know whether a match exists. In Salesforce, for example, one account can have many opportunities. Joining the two tables returns each account once per opportunity, and many developers patch that with DISTINCT.
Instead of this:
-- Returns an account once per opportunity, then removes duplicates
FROM account a
INNER JOIN opportunity o ON o.account_id = a.id;
Do this:
-- Returns each account once; the check can stop at the first match
FROM account a
WHERE EXISTS (
SELECT 1
FROM opportunity o
WHERE o.account_id = a.id
);
EXISTS checks for a match without returning the matching rows. The engine can stop at the first opportunity it finds for each account. You also skip the extra work of creating duplicates and then removing them with DISTINCT.
The takeaway. EXISTS stops at the first match, so there are no duplicates to remove.
9. Read the Query Plan
Every pattern above is easier to apply when you can see what the engine is actually doing. The query plan shows which tables are scanned, which joins are expensive, and where row counts balloon.
-- SQL Server: show reads and time, then view the actual execution plan
SET STATISTICS IO, TIME ON;
-- Databricks / Spark SQL: show the physical plan
EXPLAIN FORMATTED
SELECT s.order_id, c.customer_name
FROM sales s
INNER JOIN customers c ON s.customer_id = c.customer_id;
Look for full scans of large tables, row estimates far from actual counts, and joins that return far more rows than they read.
The takeaway. Don’t guess why a query is slow. The plan will tell you.
The 6-Hour Query, Revisited
Back to the query from the start of this article. It joined a local table to a table on another server through a linked server. Every time the query ran, the database pulled remote rows across the network to do the join. Meanwhile, it competed with everything else in production.
The fix was to copy the data I needed into a local temp table first, then join locally:
-- SQL Server: bring only what you need across the network, once
SELECT customer_id, region
INTO #remote_customers
FROM RemoteServer.CRM.dbo.customers
WHERE is_active = 1;
SELECT o.order_id, o.amount, rc.region
FROM dbo.orders o
INNER JOIN #remote_customers rc ON o.customer_id = rc.customer_id;
With everything on one server, the network delay disappeared. Run time dropped from 6 hours to 10 minutes, about 97% faster. The fix used three patterns from this article. I filtered early, took only the columns I needed, and joined on a key.
Most slow joins come down to the same few habits. Before you blame the server, check these:
• Inner joins come before left joins.
• Keys are indexed, never glued together or transformed.
• Large tables are filtered, and columns pruned, before the join.
• EXISTS replaces a join plus DISTINCT.
• Join keys are truly unique, and you’ve checked the query plan.
The real secret to fast queries isn’t clever SQL. It’s giving the optimizer less to do. AI tools can write a working query in seconds, but making it fast still depends on knowing your data.
The next time someone asks why the daily report is late, you’ll be the one who fixes it.
Further reading
If this helped you speed up a slow query, follow for more practical data engineering.
Comments