Picking the ON or WHERE Clause for the SQL Predicate

Problem

Helping people solve T-SQL problems is one of my favorite hobbies. Someone messaged me the other day with a complex query that was almost complete except for one issue. He needed to perform a LEFT OUTER JOIN but had to filter based on a value from the right table. However, when he added the filter, SQL removed rows from the left table. The task was to decide where to place the SQL predicate: in the ON or WHERE clause.

Solution

Knowing where to place the predicate in the ON or WHERE clause is crucial. In this article, I’ll explore how SQL results differ based on where we place the filter.

I’ll answer: Does the behavior change between an INNER and an OUTER JOIN? Does the optimizer use the same execution plan?

Finally, I’ll share an idea from Itzik Ben-Gan for picking where to place the predicate.

Matching and Filtering

As a SQL developer or data analyst, one of the first things we need to do is join tables. We do this by using the JOIN table operator in the FROM clause. Some other table operators in the FROM clause include APPLY, PIVOT, and UNPIVOT. For this article, let’s focus on JOIN.

SQL offers several JOIN types. You’ve likely seen a version of the Venn diagram showing what an INNER JOIN, LEFT OUTER JOIN, or FULL OUTER JOIN returns. I’ll spare you an image, but I’ve seen some funny ones online. If you’re going for a job interview that involves SQL, know about table joins—be able to describe each one with an example.

Before continuing, run the code below to follow along. It creates two tables and adds a few rows—yes, I know my table naming skills need work.

/*
* MSSQLTips.com
*/
DROP TABLE IF EXISTS #TableA;
GO
CREATE TABLE #TableA
(
    Id INT,
    ValueA INT
);
DROP TABLE IF EXISTS #TableB;
GO
CREATE TABLE #TableB
(
    Id INT,
    ValueB INT
);
INSERT INTO #TableA
VALUES (1, 10), (2, 20), (3, 30), (4, 40);
INSERT INTO #TableB
VALUES (1, 100), (2, 200), (3, 300);

Matching

The JOIN operator uses an ON clause to express how the table on the right (TableB) joins to the table on the left (TableA). I’ll assume we’re not using an implicit join, which most agree is a T-SQL anti-pattern, but I still see one every so often. If you don’t know what an implicit join is, check out this one below.

SELECT a.ValueA, b.ValueB
FROM #TableA AS a,
     #TableB b
WHERE b.Id = a.Id;

With that travesty out of the way, below is an example of an explicit INNER JOIN.

SELECT a.ValueA, b.ValueB
FROM #TableA AS a
    INNER JOIN #TableB b
        ON b.Id = a.Id;

Results:

ValueA   ValueB
-------- --------
10       100
20       200
30       300

In the example above, TableA is the left table, and TableB is the right table. The predicate in the ON clause is what SQL uses to match rows between tables. Since my example is an INNER JOIN, SQL returns matching rows from both tables. TableA contained an extra row, so SQL removed it.

I often use ‘predicate’ and ‘filter’ interchangeably. Most non-SQL people don’t know what a predicate is, but saying ‘filter’ helps us speak a common language. Monica Rathbun’s article lays out a clear definition.

A predicate in SQL returns one of three results: TRUE, FALSE, or UNKNOWN. If NULLs appear in TableA or TableB on the join predicate, SQL excludes them from the result set. This exclusion occurs because, to match an INNER JOIN, the condition must be TRUE, and a NULL yields an UNKNOWN.

Filter

What if I need to remove rows from the result set? Let’s add a WHERE clause with a filter predicate, as shown below.

SELECT a.ValueA, b.ValueB
FROM #TableA AS a
    INNER JOIN #TableB b
        ON b.Id = a.Id
WHERE b.ValueB = 200;

Results:

ValueA   ValueB
-------- --------
20       200

We can include that filter in the ON clause, as in this example.

SELECT a.ValueA, b.ValueB
FROM #TableA AS a
    INNER JOIN #TableB b
        ON b.Id = a.Id
           AND b.ValueB = 200;

To me, the filter in the ON clause makes the query less clear, especially with multiple table joins. I’ll admit that this is a matter of personal style.

Looking at the actual execution plan, SQL uses the same plan for both statements.

Execution Plan

SQL doesn’t care what we do. For a simple INNER JOIN, it doesn’t matter whether you include the filter predicate in the WHERE or ON clause. However, the placement does matter for an OUTER JOIN.

OUTER JOIN

We’ll focus on a LEFT OUTER JOIN in this section. There is a RIGHT one, but I can’t recall using it—except to confuse people. If you are a fan of a RIGHT OUTER JOIN, then keep rocking it.

A LEFT OUTER JOIN returns all rows from the left table and matching rows from the right. When a match can’t be found in the right table, SQL uses a NULL reference or placeholder. In daily life, I sometimes say NULL value, but it’s not a value; it’s a symbol of an unknown or missing value. Below is a simple example.

SELECT a.ValueA, b.ValueB
FROM #TableA AS a
    LEFT OUTER JOIN #TableB b
        ON b.Id = a.Id

Results:

ValueA   ValueB
-------- --------
10       100
20       200
30       300
40       NULL

For the OUTER JOIN, an ON clause supplies the matching criteria. The results show TableB is missing one of the values (40).

Now, let’s say I want to apply a filter; I only want to return the value (200) from TableB. Let’s do that by adding a WHERE clause.

SELECT a.ValueA, b.ValueB
FROM #TableA AS a
    LEFT OUTER JOIN #TableB b
        ON b.Id = a.Id
WHERE b.ValueB = 200;

Results:

ValueA   ValueB
-------- --------
20       200

Here’s the issue: I want all rows from TableA, but I only get one. My developer friend had the same problem.

The problem is that SQL processes the WHERE clause with the added filter after the FROM and ON. The WHERE clause affects the results after the matching, and there is one row where the value is 200. I’m asking SQL to return that single row.

When I want to exclude rows, I need to exclude them during the matching phase before the two tables are joined. That means I need to place the exclusion for TableB in the ON clause, as shown in the example below.

SELECT a.ValueA, b.ValueB
FROM #TableA AS a
    LEFT OUTER JOIN #TableB b
        ON b.Id = a.Id AND b.ValueB = 200;

Results:

ValueA   ValueB
-------- --------
10       NULL
20       200
30       NULL
40       NULL

Now, those are the results I was looking for.

Sage Advice

Earlier, I hinted at a simple way to know whether to put the predicate in the ON or WHERE clause. One of my favorite authors, Itzek-Ben Gan, gives a blueprint to follow: “What makes things easy for me is to simply ask myself whether the predicate is a matching predicate or a filtering one. If the former, it belongs in the ON clause; if the latter, it belongs in the WHERE clause.”

Key Points

  • For an INNER JOIN, the predicate’s place in the WHERE or ON clause doesn’t matter; results are the same. I put the filter in the WHERE clause, because it’s hard to override years of SQL habits.
  • With an OUTER JOIN, placing the filter in either the ON or WHERE clause affects the result set.
  • If this concept becomes confusing, recall this rule: the ON is for matching, and the WHERE is for filtering. It might not be entirely accurate, as you can filter in ON, but it helps with understanding the differences.

Next Steps

  • Want to learn about logical query processing? Itzik Ben-Gan talks about it in the informative article, “Logical Query Processing: What It Is and What It Means to You.” He outlines the order of operations and illustrates it with a helpful flowchart. It’s worth the fifteen minutes of time investment.
  • Check out Jeremy Kadlec’s detailed article, “SQL Server Join Types Examples,” where he outlines all the various join types in SQL Server.
  • One join type I don’t often use is a FULL OUTER JOIN; it’s easy to forget how it works. However, Jim Evans has me covered with his article, “FULL OUTER JOIN SQL Examples.”

Related Posts

3 Comments

  2. In the following example we will only join rows from the dbo.Data table with the rows from dbo.Codedata where the status is A(ctive) but want to return all 8 rows from dbo.Data.

    For me the problem is that the tables we are joining is not the tables that we reference. So instead of as in the following example reference the dbo.Codedata table, we should reference a Sub-Select – could also be a CTE or View.

    In this way the join condition is simple and we can use all the Outer Join Types without any problem. The solution with the selection condition in ON, is seen used without understanding what is going on.

    CREATE TABLE dbo.Codedata
    (
    Code CHAR (1) NOT NULL
    CONSTRAINT PK_Codedata PRIMARY KEY,
    CodeTxt VARCHAR (10) NOT NULL,
    ActivePassive CHAR (1) NOT NULL
    );

    CREATE TABLE dbo.Data
    (
    DataID INT NOT NULL
    CONSTRAINT PK_Data PRIMARY KEY,
    Code CHAR (1) NOT NULL
    CONSTRAINT FK_Data_Codedata FOREIGN KEY
    REFERENCES dbo.Codedata (Code)
    );
    GO
    INSERT INTO dbo.Codedata VALUES
    (‘X’, ‘Xxxxx’, ‘P’), (‘Y’, ‘Yyyyyyy’, ‘P’), (‘Z’, ‘Zzzzzzz’, ‘P’),
    (‘A’, ‘Aaaaaaa’, ‘A’), (‘B’, ‘Bbbbbbbb’, ‘A’), (‘C’, ‘Cccccc’, ‘A’);

    INSERT INTO dbo.Data VALUES
    (1, ‘A’), (2, ‘B’), (3, ‘A’), (4, ‘B’),
    (5, ‘Y’), (6, ‘X’), (7, ‘Y’), (8, ‘Y’);
    GO
    SELECT * — Wrong, only 4 rows
    FROM dbo.Data LEFT JOIN dbo.Codedata
    ON Data.Code = Codedata.Code
    WHERE Codedata.ActivePassive = ‘A’ — Only active, but selected before join

    SELECT * — Correct, all 8 rows from dbo.Data
    FROM dbo.Data LEFT JOIN dbo.Codedata
    ON Data.Code = Codedata.Code AND
    Codedata.ActivePassive = ‘A’ — Only active

    SELECT * — Correct, all 8 rows from dbo.Data is returned
    FROM dbo.Data LEFT JOIN (SELECT * FROM dbo.Codedata WHERE Codedata.ActivePassive = ‘A’) AS Cd_Active
    ON Data.Code = Cd_Active.Code;

    SELECT *
    FROM dbo.Data RIGHT JOIN (SELECT * FROM dbo.Codedata WHERE Codedata.ActivePassive = ‘A’) AS Cd_Active
    ON Data.Code = Cd_Active.Code;

    SELECT *
    FROM dbo.Data FULL JOIN (SELECT * FROM dbo.Codedata WHERE Codedata.ActivePassive = ‘A’) AS Cd_Active
    ON Data.Code = Cd_Active.Code;

  3. Thank you for the very detailed explanation. I never realized there was a difference between when the filter was applied, but it makes a lot of sense. Definitely something I will take into consideration in future joins.
    Thank You

Leave a Reply

Your email address will not be published. Required fields are marked *