Where Predicate in “JOIN ON” Clause

Today it happened that my junior Engineer came to my desk and asks me a good question, his statement was like this

“What difference it makes if we write filter condition with ON CLAUSE along with join”

His mean to say; All those condition which we write in “WHERE” clause, if we write all those condition along with join, will it make any difference in the output of query?

Let’s stimulate the same, by doing a small practical.

Here we are creating two table, table1 as parent table and table2 as child with common field for joining “id” in parent table and “p_id” as child field as foreign table.

create table table1

(id int primary key,

name varchar(10))

Command(s) completed successfully.

create table table2

(id int primary key,

address varchar(15),

p_id int references table1(id))

Command(s) completed successfully.

Let’s insert some records in table1

insert into table1

select 1,'Lopa' union all

select 2, 'Abhipsha' union all

select 3,'Rubi' union all

select 4,'Soumya' union all

select 5,'Diksha'

Now lets insert some child records in table2

insert into table2

select 1,'Vegas', 1 union all

select 2,'New Jersey', 2 union all

select 3,'San Diego',3 union all

select 4,'San Francisco',3 union all

select 5,'Los Angeles',5

select * from table1

id          name

----------- ----------

1           Lopa

2           Abhipsha

3           Rubi

4           Soumya

5           Diksha

select * from table2

id          address         p_id

----------- --------------- -----------

1           Vegas           1

2           New Jersey      2

3           San Diego       3

4           San Francisco   3

5           Los Angeles     5

Lets write inner join query joining table1 with table2 on id of table1 with p_id of table2

select * from table1

inner join table2

on table1.id = table2.p_id

id          name       id          address         p_id

----------- ---------- ----------- --------------- -----------

1           Lopa       1           Vegas           1

2           Abhipsha   2           New Jersey      2

3           Rubi       3           San Diego       3

3           Rubi       4           San Francisco   3

5           Diksha     5           Los Angeles     5

Now here we will be adding a condition along with the join.  The condition is the p_id should be 3. And the condition is written with WhERE clause.

select * from table1

inner join table2

on table1.id=table2.p_id

where table2.p_id=3

id          name       id          address         p_id

----------- ---------- ----------- --------------- -----------

3           Rubi       3           San Diego       3

3           Rubi       4           San Francisco   3

Now we will write the same condition p_id equals 3 but this time the condition is written along with the JOIN clause.

select * from table1

inner join table2

on table1.id = table2.p_id and table2.p_id=3

id          name       id          address         p_id

----------- ---------- ----------- --------------- -----------

3           Rubi       3           San Diego       3

3           Rubi       4           San Francisco   3

Here we saw writing condition either with JOIN clause or WHERE did not make any difference with INNER JOIN.

Lets try the same with OUTER join.

select * from table1

left outer join table2

on table1.id = table2.p_id

id          name       id          address         p_id

----------- ---------- ----------- --------------- -----------

1           Lopa       1           Vegas           1

2           Abhipsha   2           New Jersey      2

3           Rubi       3           San Diego       3

3           Rubi       4           San Francisco   3

4           Soumya     NULL        NULL            NULL

5           Diksha     5           Los Angeles     5

Now we will put some filter criteria.

select * from table1

left outer join table2

on table1.id=table2.p_id

where table2.p_id=3

id          name       id          address         p_id

----------- ---------- ----------- --------------- -----------

3           Rubi       3           San Diego       3

3           Rubi       4           San Francisco   3

Here we saw after left outer join we have added a criteria of p_id =3… it filter the result set to p_id  equals 3.

Now we will try to write the same query but remove the where predicate and write the table2.p_id=3 condition along with join clause and check the result.

select * from table1

left outer join table2

on table1.id = table2.p_id and table2.p_id=3

id          name       id          address         p_id

----------- ---------- ----------- --------------- -----------

1           Lopa       NULL        NULL            NULL

2           Abhipsha   NULL        NULL            NULL

3           Rubi       3           San Diego       3

3           Rubi       4           San Francisco   3

4           Soumya     NULL        NULL            NULL

5           Diksha     NULL        NULL            NULL

Result are not same in case of Outer Join.

Conclusion: According to Logical Query Processing, the ON clause executed first then the where clause, Now here with the above Outer Join example, the table2 was filtered with p_id =3 condition then the result was joined with table1. So be aware if you are writing condition with ON CLAUSE with OUTER JOIN.

Blend your code with TEA, toast up your logic and enjoy your life with programming forever! J

 

Post Reference: Vikram Aristocratic Elfin Share

Warning: Null value is eliminated by an aggregate or other SET operation.

Whenever you are using Aggregate function, keep a special cleansing activity for NULL value before you apply aggregate function on it.

By default SQL Server escape null value while calculating aggregate. Let’s see with an example.

Here we are creating a table to hold some null value in salary column.

create table testNullAggregate_TB

(id int identity(1,1),

dept_id int,

ename varchar(10),

salary int)

Command(s) completed successfully.

Lets insert some records with null values in Salary field

insert into testNullAggregate_TB values(1,'Suprabha',2000)

insert into testNullAggregate_TB values(2,'Lopamudra',null)

insert into testNullAggregate_TB values(1,'Sourabhi',2000)

insert into testNullAggregate_TB values(1,'Akansha',null)

insert into testNullAggregate_TB values(2,'Pragya',2000)

insert into testNullAggregate_TB values(1,'Sulagna',2000)

insert into testNullAggregate_TB values(3,'Priya',2000)

Testing Aggregating Function with Null Values

Here we are checking total row count on the basis of salary field.

select count(salary) from testNullAggregate_TB

RowsCount

-----------

5

Here we saw actual Number of records present in table is 7 but count(salary) escape the record count where it found null in salary equal null.

Here we are trying to find the number of employee in each department.

select dept_id, count(salary) as 'No of Employee' from testNullAggregate_TB

group by dept_id

dept_id     No of Employee

----------- --------------

1           3

2           1

3           1

Here again we saw, it produce wrong result. Dept 1 has 4 employee whereas result shows 3, similarly Department 2 has 2 employee whereas Result shows 1.

Here we are trying to find average salary for each department.

select dept_id, avg(salary) as 'Avg Salary' from testNullAggregate_TB

group by dept_id

dept_id     Avg Salary

----------- -----------

1           2000

2           2000

3           2000

Here we saw the average salary for department 1 and 2 are not correct.

How to Rectify the Problem occurred while using Aggregate function with null values.

ISNULL function we will be using to rectify the null problems.

Total Number of Rows on the basis of salary

select count(isnull(salary,0)) as RowsCount from testNullAggregate_TB

RowsCount

-----------

7

Total employee for each department.

select dept_id, count(isnull(salary,0)) as 'No of Employee' from testNullAggregate_TB

group by dept_id

dept_id     No of Employee

----------- --------------

1           4

2           2

3           1

Average salary for each department.

select dept_id, avg(isnull(salary,0)) as 'Avg Salary' from testNullAggregate_TB

group by dept_id

dept_id     Avg Salary

----------- -----------

1           1500

2           1000

3           2000

Let  your code speak, you be silent!!!

Post Reference: Vikram Aristocratic Elfin Share

With SQL Server 2014, New way to create NONCLUSTERED Index on Persisted Table Object

With SQL Server 2014 we can create NONCLUSTERED Index at table level while creating table object., Lets see how we can do by doing a small practical.

In SQL Server 2014

We are creating a table and trying to define NONCLUSTERED index at table creation.

create table testDemo

(col1 int primary key,

col2 int index idx_col2_testDemo,

col3 int index idx_col3_testDemo)

Command(s) completed successfully.

Since command has successfully executed, lets see indexes created by querying Sys.indexes Catalog view.

select name, index_id, type_desc

from sys.indexes where object_id = OBJECT_ID('testDemo')

name                           index_id    type_desc

------------------------------ ----------- -------------

PK__testDemo__357D0D3EF2E23FAC 1           CLUSTERED

idx_col3_testDemo              2           NONCLUSTERED

idx_col2_testDemo              3           NONCLUSTERED

By querying Sys.indexes we are sure, we have three indexes on testDemo table. So this is how you can create NC index at table creation.

Just for our approval, lets see whether the same syntax is working in SQL Server 2008?

In SQL Server 2008 R2

create table testDemo

(col1 int primary key,

col2 int index idx_col2_testDemo,

col3 int index idx_col3_testDemo)

Msg 1018, Level 15, State 1, Line 3

Incorrect syntax near 'index'. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required. See SQL Server Books Online for proper syntax.

So here we saw in SQL Server 2008 R2 we don’t have option to created NC indexes at table creation.

The essence of code; it binds programmer in a non-fragile bond and never let his hand go without her!!!

Post Reference: Vikram Aristocratic Elfin Share