CODEWITHSUNDEEP
sqlsql-servert-sql
bmsqldev
bmsqldev

Reputation: 2735

Data Difference Between Where Clause and AND Clause

I tried these queries in our application. Each returned different result sets for me.

Query Set 1

SELECT * 
FROM TABLE1 T1
LEFT OUTER JOIN TABLE2 T2 ON (T1.ID = T2.ID
                              AND T1.STATUS = 'A'
                              AND T2.STATUS = 'A')
INNER JOIN TABLE3 T3 ON (T2.ID = T3.ID)
WHERE T3.STATUS = 'A'

Query Set 2

SELECT *
FROM TABLE1 T1
LEFT OUTER JOIN TABLE2 T2 ON (T1.ID = T2.ID
                              AND T2.STATUS = 'A')
INNER JOIN TABLE3 T3 ON (T2.ID = T3.ID)
WHERE T3.STATUS = 'A'
  AND T1.STATUS  = 'A'

I couldn't find out why each query returns different outputs. Also please guide me about which approach is best when we use multiple joins (left, right, Inner) with Filtering clauses.

Thanks for any help

Upvotes: 0

Views: 79

Answers (3)

A. Greensmith
A. Greensmith

Reputation: 375

You are putting a filter on the right table of a left join, creating an inner join. Your first query will return less results, whilst your second will have NULLS against non matching rows on the right table. This link helped me to understand joins much better, as I really struggled for a long time to grasp the concept. HERE

If my answer is not clear please ask me for a revision.

Upvotes: 0

paparazzo
paparazzo

Reputation: 45096

On the first 

SELECT * 
FROM TABLE1 T1
LEFT OUTER JOIN TABLE2 T2 ON (T1.ID = T2.ID
                              AND T1.STATUS = 'A'
                              AND T2.STATUS = 'A')
INNER JOIN TABLE3 T3 ON (T2.ID = T3.ID)
WHERE T3.STATUS = 'A'

AND T1.STATUS = 'A' has zero effect
It is a left join - you are gong to get all of T1 period

When you move AND T1.STATUS = 'A' to the where then it is applied

Upvotes: 1

Elli Andrikopoulou
Elli Andrikopoulou

Reputation: 1

The difference in your code is the T1.STATUS = 'A' location.

Query 1: You join the T1 and T2 tables on all the common IDS and only if both T1.STATUS = 'A' = T2.STATUS.

Query 2: You join the T1 and T2 tables on all the common IDS and only if T2.STATUS = 'A'

Basically, query 1: you filter T1's data first and then you join. query 2: you join the tables first and then you filter the data on the already joined table.

Also regarding the joins, usually the left and inner joins return the same results. Have a look here and here I found both links really useful.

Finally, my personal preference is to use inner joins unless I definitely need all the rows from either left or right table. I believe it makes my queries simpler to read and maintain.

I hope that helps.

Upvotes: 0

Related Questions