Performance Difference: Condition Placed at Inner Join VS Where Clause

Performance difference: condition placed at INNER JOIN vs WHERE clause

The reason that you're seeing a difference is due to the execution plan that the planner is putting together, this is obviously different depending on the query (arguably, it should be optimising the 2 queries to be the same and this may be a bug). This means that the planner thinks it has to work in a particular way to get to the result in each statement.

When you do it within the JOIN, the planner will probably have to select from the table, filter by the "True" part, then join the result sets. I would imagine this is a large table, and therefore a lot of data to look through, and it can't use the indexes as efficiently.

I suspect that if you do it in a WHERE clause, the planner is choosing a route that is more efficient (ie. either index based, or pre filtered dataset).

You could probably make the join work as fast (if not faster) by adding an index on the two columns (not sure if included columns and multiple column indexes are supported on Postgres yet).

In short, the planner is the problem it is choosing 2 different routes to get to the result sets, and one of those is not as efficient as the other. It's impossible for us to know what the reasons are without the full table information and the EXPLAIN ANALYZE information.

If you want specifics on why your specific query is doing this, you'll need to provide more information. However the reason is the planner choosing different routes.

Additional Reading Material:

http://www.postgresql.org/docs/current/static/explicit-joins.html

Just skimmed, seems that the postgres planner doesn't re-order joins to optimise it. try changing the order of the joins in your statement to see if you then get the same performance... just a thought.

INNER JOIN ON vs WHERE clause

INNER JOIN is ANSI syntax that you should use.

It is generally considered more readable, especially when you join lots of tables.

It can also be easily replaced with an OUTER JOIN whenever a need arises.

The WHERE syntax is more relational model oriented.

A result of two tables JOINed is a cartesian product of the tables to which a filter is applied which selects only those rows with joining columns matching.

It's easier to see this with the WHERE syntax.

As for your example, in MySQL (and in SQL generally) these two queries are synonyms.

Also, note that MySQL also has a STRAIGHT_JOIN clause.

Using this clause, you can control the JOIN order: which table is scanned in the outer loop and which one is in the inner loop.

You cannot control this in MySQL using WHERE syntax.

SQL JOIN - WHERE clause vs. ON clause

They are not the same thing.

Consider these queries:

SELECT *
FROM Orders
LEFT JOIN OrderLines ON OrderLines.OrderID=Orders.ID
WHERE Orders.ID = 12345

and

SELECT *
FROM Orders
LEFT JOIN OrderLines ON OrderLines.OrderID=Orders.ID 
    AND Orders.ID = 12345

The first will return an order and its lines, if any, for order number 12345.

The second will return all orders, but only order 12345 will have any lines associated with it.

With an INNER JOIN, the clauses are effectively equivalent. However, just because they are functionally the same, in that they produce the same results, does not mean the two kinds of clauses have the same semantic meaning.

Difference between filtering queries in JOIN and WHERE?

The answer is NO difference, but:

I will always prefer to do the following.

• Always keep the Join Conditions in ON clause
• Always put the filter's in where clause

This makes the query more readable.

So I will use this query:

SELECT value
FROM table1
INNER JOIN table2
        ON table1.id = table2.id
WHERE table1.id = 1

However when you are using OUTER JOIN'S there is a big difference in keeping the filter in the ON condition and Where condition.

Logical Query Processing

The following list contains a general form of a query, along with step numbers assigned according to the order in which the different clauses are logically processed.

(5) SELECT (5-2) DISTINCT (5-3) TOP(<top_specification>) (5-1) <select_list>
(1) FROM (1-J) <left_table> <join_type> JOIN <right_table> ON <on_predicate>
| (1-A) <left_table> <apply_type> APPLY <right_table_expression> AS <alias>
| (1-P) <left_table> PIVOT(<pivot_specification>) AS <alias>
| (1-U) <left_table> UNPIVOT(<unpivot_specification>) AS <alias>
(2) WHERE <where_predicate>
(3) GROUP BY <group_by_specification>
(4) HAVING <having_predicate>
(6) ORDER BY <order_by_list>;

Flow diagram logical query processing

• (1) FROM: The FROM phase identifies the query’s source tables and
  processes table operators. Each table operator applies a series of
  sub phases. For example, the phases involved in a join are (1-J1)
  Cartesian product, (1-J2) ON Filter, (1-J3) Add Outer Rows. The FROM
  phase generates virtual table VT1.

• (1-J1) Cartesian Product: This phase performs a Cartesian product
  (cross join) between the two tables involved in the table operator,
  generating VT1-J1.

• (1-J2) ON Filter: This phase filters the rows from VT1-J1 based on
  the predicate that appears in the ON clause (<on_predicate>). Only
  rows for which the predicate evaluates to TRUE are inserted into
  VT1-J2.
• (1-J3) Add Outer Rows: If OUTER JOIN is specified (as opposed to
  CROSS JOIN or INNER JOIN), rows from the preserved table or tables
  for which a match was not found are added to the rows from VT1-J2 as
  outer rows, generating VT1-J3.
• (2) WHERE: This phase filters the rows from VT1 based on the
  predicate that appears in the WHERE clause (). Only
  rows for which the predicate evaluates to TRUE are inserted into VT2.
• (3) GROUP BY: This phase arranges the rows from VT2 in groups based
  on the column list specified in the GROUP BY clause, generating VT3.
  Ultimately, there will be one result row per group.
• (4) HAVING: This phase filters the groups from VT3 based on the
  predicate that appears in the HAVING clause (<having_predicate>).
  Only groups for which the predicate evaluates to TRUE are inserted
  into VT4.
• (5) SELECT: This phase processes the elements in the SELECT clause,
  generating VT5.
• (5-1) Evaluate Expressions: This phase evaluates the expressions in
  the SELECT list, generating VT5-1.
• (5-2) DISTINCT: This phase removes duplicate rows from VT5-1,
  generating VT5-2.
• (5-3) TOP: This phase filters the specified top number or percentage
  of rows from VT5-2 based on the logical ordering defined by the ORDER
  BY clause, generating the table VT5-3.
• (6) ORDER BY: This phase sorts the rows from VT5-3 according to the
  column list specified in the ORDER BY clause, generating the cursor
  VC6.

it is referred from book "T-SQL Querying (Developer Reference)"

Which SQL query is faster? Filter on Join criteria or Where clause?

Performance-wise, they are the same (and produce the same plans)

Logically, you should make the operation that still has sense if you replace INNER JOIN with a LEFT JOIN.

In your very case this will look like this:

SELECT  *
FROM    TableA a
LEFT JOIN
        TableXRef x
ON      x.TableAID = a.ID
        AND a.ID = 1
LEFT JOIN
        TableB b
ON      x.TableBID = b.ID

or this:

SELECT  *
FROM    TableA a
LEFT JOIN
        TableXRef x
ON      x.TableAID = a.ID
LEFT JOIN
        TableB b
ON      b.id = x.TableBID
WHERE   a.id = 1

The former query will not return any actual matches for a.id other than 1, so the latter syntax (with WHERE) is logically more consistent.

Which performs first WHERE clause or JOIN clause

The conceptual order of query processing is:

1. FROM
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. ORDER BY

But this is just a conceptual order. In fact the engine may decide to rearrange clauses. Here is proof. Let's make 2 tables with 1000000 rows each:

CREATE TABLE test1 (id INT IDENTITY(1, 1), name VARCHAR(10))
CREATE TABLE test2 (id INT IDENTITY(1, 1), name VARCHAR(10))

;WITH cte AS(SELECT -1 + ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) d FROM
(VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t1(n) CROSS JOIN
(VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t2(n) CROSS JOIN
(VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t3(n) CROSS JOIN
(VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t4(n) CROSS JOIN
(VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t5(n) CROSS JOIN
(VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t6(n))

INSERT INTO test1(name) SELECT 'a' FROM cte

Now run 2 queries:

SELECT * FROM dbo.test1 t1
JOIN dbo.test2 t2 ON t2.id = t1.id AND t2.id = 100
WHERE t1.id > 1

SELECT * FROM dbo.test1 t1
JOIN dbo.test2 t2 ON t2.id = t1.id
WHERE t1.id = 1

Notice that the first query will filter most rows out in the join condition, but the second query filters in the where condition. Look at the produced plans:

1 TableScan - Predicate:[Test].[dbo].[test2].[id] as [t2].[id]=(100)

2 TableScan - Predicate:[Test].[dbo].[test2].[id] as [t2].[id]=(1)

This means that in the first query optimized, the engine decided first to evaluate the join condition to filter out rows. In the second query, it evaluated the where clause first.

Performance difference when put WHERE before or after JOIN

Both of your queries depend on several factors like what is indexed and what is not, index strategy, server load, data caching, the way you've written query etc. I assume the columns in where clause are indexed while answering -

I ran both type of queries and found a fairly similar result. (This could probably be because of how my tables and columns are setup).

Explain plan for Plan A

SELECT STATEMENT  FIRST_ROWS
Cost: 9  Bytes: 446  Cardinality: 14    

Explain plan for Plan B

SELECT STATEMENT  FIRST_ROWS
Cost: 12  Bytes: 448  Cardinality: 14   

Now you see both took same amount of time but Ideally Plan A is what is considered under best practice (speaking from experience, I've seen Plan A queries being used almost everywhere while Plan B not so much)

Different queries work differently under different circumstances [and Oracle (or any SQL engine) smartly picks algorithm that would work best for you.]

EDIT - I checked with larger dataset now

Plan A and Plan B both are same

SELECT STATEMENT  FIRST_ROWS
    Cost: 35,413  Bytes: 1,888,512  Cardinality: 59,016 

WHERE Clause vs ON when using JOIN

No, the query optimizer is smart enough to choose the same execution plan for both examples.

You can use SHOWPLAN to check the execution plan.

---

Nevertheless, you should put all join connection on the ON clause and all the restrictions on the WHERE clause.

Condition within JOIN or WHERE

The relational algebra allows interchangeability of the predicates in the WHERE clause and the INNER JOIN, so even INNER JOIN queries with WHERE clauses can have the predicates rearrranged by the optimizer so that they may already be excluded during the JOIN process.

I recommend you write the queries in the most readable way possible.

Sometimes this includes making the INNER JOIN relatively "incomplete" and putting some of the criteria in the WHERE simply to make the lists of filtering criteria more easily maintainable.

For example, instead of:

SELECT *
FROM Customers c
INNER JOIN CustomerAccounts ca
    ON ca.CustomerID = c.CustomerID
    AND c.State = 'NY'
INNER JOIN Accounts a
    ON ca.AccountID = a.AccountID
    AND a.Status = 1

Write:

SELECT *
FROM Customers c
INNER JOIN CustomerAccounts ca
    ON ca.CustomerID = c.CustomerID
INNER JOIN Accounts a
    ON ca.AccountID = a.AccountID
WHERE c.State = 'NY'
    AND a.Status = 1

But it depends, of course.

---

Related Topics