Search This Blog

Joins Behaviors with More than one On Condtions in SQL

    Joins Behaviors with More than one On Conditions

Yesterday in my company a fresher asked me can we write more then one On statement in Join Clause
So i thought to write a blog on this.
Yes we can write more than one On condition in a Join clause 
but the thing to  keep in mind that 
if there are only two tables to join then you cannot write ON condition more than once simultaneously if you will try to write you will get the below error.

Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'on'.

You should have minimum of three table to write more than one On condition simultaneously in a Query.

Let Me Explain You through an example

--create three tables

create table table1
(empid int ,empname varchar(50),orderid int,MangrID int)


create table table2
(orderid int,orderdate  datetime,MangrId int)


create table table3
(empid int,MangrID int)


--inserting value in all three tables
insert into table1 values(1,'Test',10,2),(2,'Test1',9,3),(3,'Test2',8,4),(4,'Test3',7,5),(5,'Test4',6,7),(6,'Test5',5,8),(7,'Test6',4,9),(8,'Test7',null,null),(9,'Test8',null,null)

insert into table2 values(4,getdate(),1),(5,getutcdate(),2),(6,getdate()-1,3),(7,getdate()+2,4)

insert into table3 values(4,1),(5,2),(6,3),(7,4),(9,9)


--Join condition fetching all common Record from all three tables  with more than 1 On Statement
--Script 1 

select t1.empid ,t1.empname,t1.MangrID ,t2.orderid,t3.MangrID,t2.orderdate  from table1 t1 
join table2 t2  join table3 t3 on t2 .MangrID =t3.MangrID on t1.orderid =t2.orderid 

empid empname MangrID orderid MangrID orderdate
4 Test3 5 7 4                               2013-08-01 11:19:01.173
5 Test4 7 6 3                               2013-07-29 11:19:01.173
6 Test5 8 5 2                               2013-07-30 18:19:01.173
7 Test6 9 4 1                              2013-07-30 11:19:01.173

--Left Join condition with join  from all three tables  with more than 1 On Statement
--Script 2

select t1.empid ,t1.empname,t1.MangrID ,t2.orderid,t3.MangrID,t2.orderdate  from table1 t1 
left outer join table2 t2  join table3 t3 on t2 .MangrID =t3.MangrID on t1.orderid =t2.orderid 

empid empname MangrID orderid MangrID orderdate
1 Test 2 NULL NULL NULL
2 Test1 3 NULL NULL NULL
3 Test2 4 NULL NULL NULL
4 Test3 5 7 4 2013-08-01 11:19:01.173
5 Test4 7 6 3 2013-07-29 11:19:01.173
6 Test5 8 5 2 2013-07-30 18:19:01.173
7 Test6 9 4 1 2013-07-30 11:19:01.173
8 Test7 NULL NULL NULL NULL
9 Test8 NULL NULL NULL NULL

-- Left Join condition followed by Right join  from all three tables with more than 1 On Statement
--Script 3

select t1.empid ,t1.empname,t1.MangrID ,t2.orderid,t3.MangrID,t2.orderdate  from table1 t1 
left outer join table2 t2 right outer join table3 t3 on t2 .MangrID =t3.MangrID on t1.orderid =t2.orderid 

empid empname MangrID orderid MangrID orderdate
1 Test 2 NULL NULL NULL
2 Test1 3 NULL NULL NULL
3 Test2 4 NULL NULL NULL
4 Test3 5 7 4 2013-08-01 11:19:01.173
5 Test4 7 6 3 2013-07-29 11:19:01.173
6 Test5 8 5 2 2013-07-30 18:19:01.173
7 Test6 9 4 1 2013-07-30 11:19:01.173
8 Test7 NULL NULL NULL NULL
9 Test8 NULL NULL NULL NULL

If you will notice in above script 2 and script 3 the result is same
 in script 2 we have left join table2 join table 3
and Script 3 left join table t2 right join table 3

The Reason Behind this is whenever you are writing more than one On condition with different table 
It will take data based on first join mean if the first join between table is Left Join then it will 
fetch data based on Left Join irrespective of  second Join condition 

The Point to Remember is You can write Multiple On  in a Join Clause Depending on no.of  tables you are joining but it will consider only the Join Clause  of First two  Joining tables other tables it will show only matched data.



IF you want to know more on this topic or have some confusion try to run below queries and match the Output with above Scripts:


select t1.empid ,t1.empname,t1.MangrID ,t2.orderid,t3.MangrID,t2.orderdate from table1 t1 left outer join table2 t2 on t1.orderid =t2.orderid  left outer join table3 t3 on t2 .MangrID =t3.MangrID 


select t1.empid ,t1.empname,t1.MangrID ,t2.orderid,t3.MangrID,t2.orderdate from table1 t1 left outer join table2 t2 on t1.orderid =t2.orderid  right outer join table3 t3 on t2 .MangrID =t3.MangrID 

select t1.empid ,t1.empname,t1.MangrID ,t2.orderid,t3.MangrID,t2.orderdate from table1 t1 left outer join table2 t2 on t1.orderid =t2.orderid  join table3 t3 on t2 .MangrID =t3.MangrID 

select t1.empid ,t1.empname,t1.MangrID ,t2.orderid,t3.MangrID,t2.orderdate from table1 t1  join table2 t2 on t1.orderid =t2.orderid  join table3 t3 on t2 .MangrID =t3.MangrID 






Introduction to Case Statement in SQL

               Introduction to Case Statement in SQL

Many times there is a requirement like you have to display data based on some condition 
Let me explain by a example
Suppose in your table you have a column Gender and you have inserted value like
M which denotes Male
F which denotes Female
If no values then Unknown

So for this type of scenarios we use case Statement 

Below is the Example of Case Statement

create table employee
(name nvarchar(30),Gender nvarchar(15))
insert into employee values('xyz','M'),('abc','F'),('pqr',Null)

select * from employee 

select name,case gender
when 'M' then 'Male'
when 'F' then 'Female'
else 'Unknown'
end
from employee 

Don't forget to put end after finishing case statement otherwise it will throw an error.

The Case statement will check data for Gender column it will check if M then display Male
if F then Female else display Unknown

Note: you can achieve this result also by using if else if but when no. of condition are more it's a good practice to use Case Statement.

Select * vs Select Columns Names From Table in SQL

             Select * vs Select Columns Names From Table

People often write Select * while fetching Records from table but in fact it's not a good practise

Below are Points that will tell why you should not fetch records using Select * from table


1-Selecting * will fetch all columns name  which in result use more resources, more network and slower queries.

2-Secondly if someone alters the structure of your table SELECT * will suddenly start bringing back a structure you weren't expecting.

3-Fetching all Records using Select * prohibits the query from using Covered Indexes.

4- SELECT * reduces your ability to do research on which columns are used as only the ones in JOIN, WHERE, GROUP BY, and ORDER BY (assuming you aren't silly enough to use the SELECT list position #s) clauses will show up when doing searches. If you need to rename or drop a column you need to be able to determine EVERYWHERE it is being used.

5-SELECT * instead of SELECT field1, field2 then you will also get field3 which you don't need / want but it will be sent over the network to the application so for every row it will have unnecessary transport of field 3.


 If you are lazy enough  for writing column names in query  then use SSMS

Right Click Table Name-Script Table as-Select to- New Query Editor Window.
It will give you all column names remove columns which is not required:

Below is the procedure of Generating Script of a table along with all Column Names.


Conditional Formating In SSRS Using Switch Condition

Changing Color of a Column Data Based on Condition in SSRS

Many Times you have a requirement in SSRS like changing Color of Column Values Based on some Conditions
for this Select the TextBox Holding your Column Values
Right Click Properties-Font-Color-Expression
Write this Expression
=Switch(ReportItems!PCTContribution.Value > .1, "green", ReportItems!PCTContribution.Value < .02, "red")
PCTContribution is my Column Holding all ContributionValues

If You Can see the PCT Cotribution Column the values are Red Greed and Black
where values are greater then .1 they are red
where values are greater then .2 they are green
Rest are black
You can write any number of conditions inside Switch.

This is how we can apply color condition in our Column Data

Note: If you only want to apply if or else ie only two condition you can write this code

=Iif(ReportItems!PctContribution.Value > .1, "Green", "Black")

Satisfying condition will have Green Values rest all will Black .





Difference Between AND Operator with ON Clause and Where while Using with Left Joins in SQL

 Difference Between AND Operator with ON Clause and Where   while Using  Left Joins


Many People have a believe that wriitng filter condition in where clause or  after left join on  Clause are same
There is a big difference between them
I will Explain with an Example
First I am Creating two table to explain difference between them
Script 1

create table table1
(empid int ,empname nvarchar(50),orderid int)


create table table2

(orderid int,orderdate  datetime)

insert into table1 values(1,'Test',10),(2,'Test1',9),(3,'Test2',8),(4,'Test3',7),(5,'Test4',6),(6,'Test5',5),(7,'Test6',4),(8,'Test7',null),(9,'Test8',null)

insert into table2 values(4,getdate()),(5,getutcdate()),(6,getdate()-1),(7,getdate()+2)

Now I will run  3 more script to clear the difference between them.
Script 2

select t1.empid ,t1.empname,t2.orderid from table1 t1 left outer join table2 t2 on t1.orderid=t2.orderid

Output
empid empname orderid
1 Test          NULL
2 Test1 NULL
3 Test2 NULL
4 Test3 7
5 Test4 6
6 Test5 5
7 Test6 4
8 Test7 NULL

9 Test8 NULL

It's  a simple left join where field are matched it's showing result rest Null values

Let's run below script 3


Script 3

select t1.empid ,t1.empname,t2.orderid from table1 t1 left outer join table2 t2 on t1.orderid=t2.orderid and t2.orderid is null

Output
empid empname orderid
1 Test   NULL
2 Test1 NULL
3 Test2 NULL
4 Test3 NULL
5 Test4 NULL
6 Test5 NULL
7 Test6 NULL
8 Test7 NULL

9 Test8 NULL

If you will notice that i have added a and condition after on clause and it changed all order id values to Null
The reason behind this is that on joining the two tables at that time only it is checking that wheather there is any null value in table 2 or not and if you will check in table 2 there is no null 
values so from where null values are coming in order id column
The reason is simple we have performed Left Join wheather codition matches or not it will show the result of left table so as it is showing data of left table completely and as there is null order id in table 2 the condition is not matching so it's showing all null in order id

Now let's run script 4

Script 4

select t1.empid ,t1.empname,t2.orderid from table1 t1 left outer join table2 t2 on t1.orderid=t2.orderid 

where t2.orderid is null

Output

empid empname orderid
1 Test    NULL
2 Test1 NULL
3 Test2 NULL
8 Test7 NULL

9 Test8 NULL

Now in script 4 i have added the same condition in where clause in script 3 the same condition was after on clause
If you will compare output of script 3 and script 4 the output are different
the reason is that where clause is filtering the whole recordset after joining the tables
and showing only those record which matches the where criteria after joining the tables

Hope that you understand the difference between them 

NOTE:There is no difference between the "Where" clause and "On" clause when used with inner join.








Order by Column Names Containing Null Values in SQL

Order by Column Names Containing Null Values in SQL

There are many scenarios where you have to fetch record order by some column names
but what if the order by column contains NULL values
Then it will order the table by placing NULL Values first then order on sequence what you want
I will Explain it through this scenario

create table #tempcustomer
(Custid int,region nvarchar(40))
insert into #tempcustomer values(1,'AK'),(2,'BC'),(3,Null),(4,'CA'),(5,null)

Desired Output:
Custid region
1 AK
2 BC
4 CA
5 NULL
3 NULL


select Custid,region
from #tempcustomer order by region

if you will run the above query you will get output like below

Output:
 Custid region
3 NULL
5 NULL
1 AK
2 BC
4 CA

select Custid,region
from #tempcustomer order by region desc

Now run above query still you are not getting your desired output

Output:
Custid region
4 CA
2 BC
1 AK
3 NULL
5 NULL

But None of our Queries are giving us actual Result in order to get desired result Use Case Statement in Order by Clause

select Custid,region
from #tempcustomer order by case
when region  IS null then 1
else 0
end

Now you will get your desired Output:

Explanation In case statement first it will Check wheather region is null in our table region has null values so it will order by 1 for Null values  and for other non-Null 
values it will order by 0.so 0 order values comes first then 1 order values.

Note:  The default sort behavior of NULLs in T-SQL is to sort first NUll Values  (before non-NULL values).


Concatenation with Nulls in SQL

              Concatenation with Nulls in SQL Server 2008


In many Situtation you have a requirement to concatenate column but if any of column which you are concatenating contains NULL the result will be NULL
as Cocatenating any Value to Null will Result in NULL
In these type of scenario there are two solution
1 Using Coalesce Function

SELECT custid, country, region, city,   country + N',' + COALESCE(region, N'') + N',' + city AS location FROM Sales.Customers;

The Coalesce function will treat NULL as empty String

2 Setting a session option called CONCAT_NULL_YIELDS_NULL to OFF.

NOW SQL Server treats a NULL set to OFF as an empty string for concatenation purposes.

SET CONCAT_NULL_YIELDS_NULL OFF;
SELECT empid, firstname + N' ' + lastname AS fullname FROM HR.Employees;

Point to Remember : Once You have done with your result set Concat null yield back to on
 SET CONCAT_NULL_YIELDS_NULL ON as the Default values is ON

IN SQL SERVER 2012 You can achieve the Desired Result Directly with Concat Function

SET CONCAT_NULL_YIELDS_NULL ON;
SELECT CONCAT('abc', NULL) AS Test1;

output-abc
SET CONCAT_NULL_YIELDS_NULL OFF;
SELECT CONCAT('abc', NULL) AS Test2;

output-abc





Constraints in TSQL

Below are the List of Adding Constraints in a table


To add referential Integrity between tables

--foreign key constraint
alter table sales.orders
add constraint FK_orderrs
Foreign key(empid)
references dbo.employee(empid)

To Check all the values of a column are unique it can take one NULL Values

--unique constraint
alter table dbo.employee
add constraint UQ_Emp_SN
unique (ssn)

To Set Default value to a column if no value will be inserted or updated it will take default values


--default constraint

alter table orders
add constraint df_dt
default(getdate()) for orders

To Check Value of a column every time with insert and update it will check for the value


--check constaint

ALTER TABLE dbo.Employees 
ADD CONSTRAINT CHK_Employees_salary 
CHECK(salary > 0);


To add Primary key Constraint in a table it gaurntee uniqueness without taking any Null Value

--primary key constraint
ALTER TABLE dbo.Employees 
ADD CONSTRAINT PK_Employees 
PRIMARY KEY(empid);



To add Primary Key Constraint while creating a table

--Primary key constraints while creating table
CREATE TABLE dbo.Orders ( orderid INT  NOT NULL, empid INT  NOT NULL,
custid VARCHAR(10) NOT NULL, orderts  DATETIME NOT NULL, qty INT  NOT NULL, 
CONSTRAINT PK_Orders PRIMARY KEY(OrderID) );

SSRS Order BY with Null Statement

             Order by Issue with Null Values in SSRS

There have been Situation where u have to order a column in SSRS but it contail Null Values
Even after sorting them A-Z the Null Values will Come first

In This type of Scneraio
Select Tablix Properties-Sorting-Order by
in Expression Write the Below Expression

=IIf(IsNothing(Fields!TerritoryGroup.Value), "NULL", "NOTNULL")

Note: If you have any group in your report Go to Group Properties -Sorting-Order by
and Remove default order by which is A-Z