T-sql – Filters Joins or Where clauses TSQL

t-sql

Where should I put the filters on a TSQL query?

SELECT a.colum FROM A 
JOIN B  ON A.ID =  B.ID AND B.STATUS  = 1

SELECT a.colum FROM A 
JOIN B  ON A.ID =  B.ID 
WHERE  B.STATUS  = 1

I'm looking for performance issues or good practices.

Best Answer

There is no performance difference
Best practice is explicit JOIN and separate filters (your 2nd example)

Note:

This changes for OUTER JOINs because you'd change it to a inner join

The quick way:

SELECT a.colum
FROM
   A 
   LEFT JOIN 
   B  ON A.ID =  B.ID AND B.STATUS  = 1
WHERE
   a.foo = 'bar'

With separate filters:

SELECT a.colum
FROM
   A 
   LEFT JOIN 
   (SELECT * FROM B WHERE STATUS  = 1) B ON A.ID =  B.ID
WHERE
   a.foo = 'bar'

Related Solutions

Sql-server – Dissecting Joins

Try this query without the joins first:

select  
 level1.res_name as level1

from resource as level1

where isnull(level1.man_id,0)=0

This returns the rows where man_id isn't referencing anything. In other words, this returns the topmost-level managers only. For your particular example it works like this:

the FROM clause returns the entire table:

level1.Res_id  level1.Res_name  level1.Man_id
-------------  ---------------  -------------
1              sam              3
2              Biju             4
3              adrian           NULL
4              Helen            3
5              Micah            4

the WHERE clause leaves just one row:

level1.Res_id  level1.Res_name  level1.Man_id
-------------  ---------------  -------------
3              adrian           NULL

finally, the SELECT takes from it only one column:
```
level1
------
adrian
```

Adding one self-join like in Chris's query,

select  
 level1.res_name as level1,
 level2.res_name as level2

from resource as level1
left join resource as level2 on level1.res_id=level2.man_id

where isnull(level1.man_id,0)=0

gives you every top-level manager's direct subordinates. More specifically, the join results in the following row set:

level1.Res_id  level1.Res_name  level1.Man_id  level2.Res_id  level2.Res_name  level2.Man_id
-------------  ---------------  -------------  -------------  ---------------  -------------
1              sam              3              NULL           NULL             NULL
2              Biju             4              NULL           NULL             NULL
3              adrian           NULL           1              sam              3
3              adrian           NULL           4              Helen            3
4              Helen            3              2              Biju             4
4              Helen            3              2              Biju             4
5              Micah            4              NULL           NULL             NULL

The WHERE clause filters it down to just this:

level1.Res_id  level1.Res_name  level1.Man_id  level2.Res_id  level2.Res_name  level2.Man_id
-------------  ---------------  -------------  -------------  ---------------  -------------
3              adrian           NULL           1              sam              3
3              adrian           NULL           4              Helen            3

And finally the SELECT clause returns only the names:

level1  level2
------  ------
adrian  sam
adrian  Helen

In the same manner, adding one more join, like this:

select  
 level1.res_name as level1,
 level2.res_name as level2,
 level3.res_name as level3

from resource as level1
left join resource as level2 on level1.res_id=level2.man_id
left join resource as level3 on level2.res_id=level3.man_id

where isnull(level1.man_id,0)=0

brings about the next level of subordinates:

this is the result of joining the third instance of the table to the results of the first join:

1.Res_id  1.Res_name  1.Man_id  2.Res_id  2.Res_name  2.Man_id  3.Res_id  3.Res_name  3.Man_id
--------  ----------  --------  --------  ----------  --------  --------  ----------  --------
1         sam         3         NULL      NULL        NULL      NULL      NULL        NULL
2         Biju        4         NULL      NULL        NULL      NULL      NULL        NULL
3         adrian      NULL      1         sam         3         NULL      NULL        NULL
3         adrian      NULL      4         Helen       3         2         Biju        4
3         adrian      NULL      4         Helen       3         5         Micah       4
4         Helen       3         2         Biju        4         NULL      NULL        NULL
4         Helen       3         5         Micah       4         NULL      NULL        NULL
5         Micah       4         NULL      NULL        NULL      NULL      NULL        NULL

(I've shortened the table aliases for convenience: 1 stands for level1, 2 for level2, and 3 for level3)

this is what remains after the WHERE filter:

1.Res_id  1.Res_name  1.Man_id  2.Res_id  2.Res_name  2.Man_id  3.Res_id  3.Res_name  3.Man_id
--------  ----------  --------  --------  ----------  --------  --------  ----------  --------
3         adrian      NULL      1         sam         3         NULL      NULL        NULL
3         adrian      NULL      4         Helen       3         2         Biju        4
3         adrian      NULL      4         Helen       3         5         Micah       4

and this is what SELECT extracts from the above and gives you back:

level1  level2  level3
------  ------  ------
adrian  sam     NULL
adrian  Helen   Biju
adrian  Helen   Micah

Same continues for the rest of the joins. With your example, however, there will be no more rows, as the hierarchy in the table doesn't go deeper than two levels. Consequently, the other two columns in Chris's original query, level4 and level5, will return NULLs:

level1  level2  level3  level4  level5
------  ------  ------  ------  ------
adrian  sam     NULL    NULL    NULL
adrian  Helen   Biju    NULL    NULL
adrian  Helen   Micah   NULL    NULL

If Chris's assumption that four joins should be enough for most cases does not work for your particular case (and I don't mean the simple example in your question, of course), you can throw in more self-joins using the same pattern.

Sql-server – Should you use the schema name in WHERE, ON, and other clauses

SELECT Col1, Col2, Col3
FROM dbo.Table
WHERE Col1 = Value;

In the first scenario should I add dbo.Table to each column name?

For single tables, table aliases are OK to be excluded.

SELECT Col1, Col2, Col3,
FROM dbo.Table
INNER JOIN dbo.Table2
ON Col1 = Col5
WHERE Col6 = Value;

IN the second scenario if I added an alias to each table would that automatically include the schema data too or would I still need to pre-fix the column names with the schema.

Table alias will include the FULLY qualified table name.

e.g. dbo.Table as T1 will reference the dbo.Table.

And in both scenarios do I even NEED to prefix the columns with schema data or is that pointless and gives no performance boost at all?

First your query example is wrong. It should be (carefully see the aliases I have used, else it will be syntactically wrong - SQL Server will throw errors).

 SELECT T1.Col1, T1.Col2, T2.Col3,
    FROM dbo.Table T1
    INNER JOIN dbo.Table2 T2
    ON T1.Col1 =  T2.Col5
    WHERE T2Col6 = Value;

Hopefully, the above query will answer your 3rd question as well.

Best Answer

Related Solutions

Sql-server – Dissecting Joins

Sql-server – Should you use the schema name in WHERE, ON, and other clauses

Related Question