0

I executed these Queries

SELECT *
FROM A
INNER JOIN B ON coalesce(A.a1,'') = coalesce(B.a1,'') and A.a1 <> '';
                                                       /\
                                                       ||
                                              Condition in On Clause

and

SELECT *
FROM A
INNER JOIN B ON coalesce(A.a1,'') = coalesce(B.a1,'') WHERE A.a1 <> '';
                                                       /\
                                                       ||
                                              Condition in Where Clause

and get different Result ?

I want to understand what is the Difference between putting filtering condition in On Clause vs Condition in Where claues ,

which one is better in terms of performance ?

Update

Sample Data

a1 is blank '' in both Tables .

Now with 1st Query i am getting no Rows --> 0 Rows

but with my 2nd Queries i am getting multiple Rows --> 1251 Rows
Neeraj Jain
  • 7,643
  • 6
  • 34
  • 62

2 Answers2

1

They are not same.

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.

I meant:

-Does not matter for inner joins

-Matters for outer joins

a. 'where' clause: After joining. Records after join would be filtered.

b. 'on' clause - Before joining. Records (from right Table) would be filtered before joining, this may end up as null in the result (since OUTER join).

Please refer these articles: http://weblogs.sqlteam.com/jeffs/archive/2007/05/14/criteria-on-outer-joined-tables.aspx

and

https://sites.google.com/site/nosuchmethodexception/database/join/join-vs-where-clause

Tushar
  • 3,527
  • 9
  • 27
  • 49
  • You can refer http://stackoverflow.com/questions/219046/help-with-a-where-on-a-left-join-sql-query#219053 this too – Tushar Mar 10 '15 at 07:19
  • Oh... Can you post some of your sample data in post? – Tushar Mar 10 '15 at 07:24
  • 1
    Sample Data Added , I know for Left Join , searching for Inner Join as per my understanding both queries in case of Inner Join should give me 0 Result – Neeraj Jain Mar 10 '15 at 07:31
  • Yes... Even am wondering :) Let me too check in my local database – Tushar Mar 10 '15 at 07:32
  • 1
    @Avidan To clarify, the first *is effectively* an inner join! In fact, from MySQL's point of view it *is* an inner join. – Strawberry Mar 10 '15 at 08:57
  • @NeerajJain I tried equivalent queries on my three different tables. They yield me same results for inner joins. :) What values your `a1` columns contain in both the tables ? and what is it's datatype? – Tushar Mar 10 '15 at 09:41
0

In your first query, the condition " and A.a1<>'' " is applied only to table B. However, the where condition in your second query is applied to A and B.

It doesnt have any affect on Inner Join but it has affects ont Left Join

Arif YILMAZ
  • 5,754
  • 26
  • 104
  • 189