What is the difference in these two queries as getting two different result set?

Question

I am getting different result set for these two queries and second result set seems to be correct. What is the difference in these queries.

What type of inner join query second is?

1)

FROM TABLE1 t1
 INNER JOIN TABLE2 t2 ON t1.Id = t2.Id
WHERE
 t1.StatusId = 12

2)

FROM TABLE1 t1
 INNER JOIN TABLE2 t2 ON t1.Id = t2.Id
 AND t1.StatusId = 12

Answer 1

It really makes no difference when you do this in the INNER JOIN.

However, when you use LEFT or RIGHT JOIN, it does make a difference whether you put the additional filter into the JOIN or into the WHERE clause.

When you put the filter into the WHERE clause, SQL Server does the join first, and then completely filters out the rows where the filter does not fit.
--> this will reduce the number of rows which are returned

When you put the filter into the JOIN, SQL Server does the filtering during the join, but only on the table where you put the filter.
You still get all the rows from the other tables, but only those have the data from the filtered table where the filter fits.
--> this will not reduce the number of rows, but the columns with data from the filteres table will be empty in more rows

It's difficult to explain...to make it more clear, here's an example:

Take the sample data from RedFilter's answer:

CREATE TABLE [dbo].[t1](
    [ID] [int] NULL,
    [StatusID] [int] NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[t2](
    [ID] [int] NULL
) ON [PRIMARY]
INSERT INTO t1 (ID, StatusID) VALUES (1, 10)
INSERT INTO t1 (ID, StatusID) VALUES (2, 11)
INSERT INTO t1 (ID, StatusID) VALUES (3, 12)
INSERT INTO t1 (ID, StatusID) VALUES (4, 12)
INSERT INTO t2 (ID) VALUES (1)
INSERT INTO t2 (ID) VALUES (3)
INSERT INTO t2 (ID) VALUES (5)

...and run the following queries on it:

/* this returns four rows, but only two will have data 
from the second table in the second column */
SELECT t1.ID, t2.ID
FROM t1 
LEFT JOIN t2 ON t1.Id = t2.Id 

/* this returns only one row: the one where t2.ID = 1 */
SELECT t1.ID, t2.ID
FROM t1 
LEFT JOIN t2 ON t1.Id = t2.Id 
WHERE t2.ID = 1 

/* this returns four rows as in the first query, but only one 
row will have data in the second column: the one where t2.ID = 1 */
SELECT t1.ID, t2.ID
FROM t1 
LEFT JOIN t2 ON t1.Id = t2.Id 
AND t2.ID = 1 

Note the different results as indicated in the comments.

Answer 2

These queries should be returning identical results. Please post the complete queries.

Below is a sample demonstrating the same output from the two variations:

Sample:

CREATE TABLE [dbo].[t1](
    [ID] [int] NULL,
    [StatusID] [int] NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[t2](
    [ID] [int] NULL
) ON [PRIMARY]
INSERT INTO t1 (ID, StatusID) VALUES (1, 10)
INSERT INTO t1 (ID, StatusID) VALUES (2, 11)
INSERT INTO t1 (ID, StatusID) VALUES (3, 12)
INSERT INTO t1 (ID, StatusID) VALUES (4, 12)
INSERT INTO t2 (ID) VALUES (1)
INSERT INTO t2 (ID) VALUES (3)
INSERT INTO t2 (ID) VALUES (5)

SELECT t1.ID, t1.StatusID
FROM t1 
INNER JOIN t2 ON t1.Id = t2.Id 
WHERE t1.StatusId = 12 

SELECT t1.ID, t1.StatusID
FROM t1 
INNER JOIN t2 ON t1.Id = t2.Id 
    AND t1.StatusId = 12 

Output:

ID          StatusID
----------- -----------
3           12

(1 row(s) affected)

ID          StatusID
----------- -----------
3           12

(1 row(s) affected)