SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL Query Experiment - "WHERE" & "ON"

Sometimes the mistakes you make in the coding lead you to a better understanding of a particular concept.

This happened recently with me. I accidentally replaced the 'WHERE' clause in a SQL query with "AND" and the results confused the heck out of me. 

Lets look at the coding part now. Suppose we have two tables #t1 and #t2. 

#t1 consists of id and name
#t2 consist of id and department

Now the requirement is to fetch name and department of id = 2. 

CREATE TABLE #T1 (ID INT, NAM VARCHAR(100))
INSERT INTO #T1 VALUES (1, 'AMIT')
INSERT INTO #T1 VALUES (2, 'AMITT')
INSERT INTO #T1 VALUES (3, 'AMITTT')

CREATE TABLE #T2 (ID INT, DEP VARCHAR(100))
INSERT INTO #T2 VALUES (1, 'HR')
INSERT INTO #T2 VALUES (2, 'FINANCE')

SELECT * FROM #T1 
LEFT JOIN #T2 ON #T1.ID = #T2.ID 
where #T1.ID = 2 

This is quite simple 






But what if the select query becomes 

SELECT * FROM #T1 
LEFT JOIN #T2 ON #T1.ID = #T2.ID 
AND #T1.ID = 2

Any guesses of what the result set would look like









Explanation:-
1. Its a left join and therefore all the rows from #t1 table are retrieved.
2. "ON" clause has 2 joins one on id column of table #t2 and another one on a integer value 2. This indirectly implies that the #t2 table is filtered out for a single row where id is 2.

Make sure to use where and on clause carefully.

Thanks
Lokesh








EVERYDAY MSBI/SQL CHALLENGES

My name is Lokesh Sharma, I have 5 years of experience working with SQL and am really interested in exploring more and more about this fascinating language. My aim here is to share some of the challenges that I faced while working with SQL and also to get feedback whether the approach I followed is an optimal one or not.

Comments

Leave a comment on the original post [itchallengesdaily.blogspot.com, opens in a new window]

Loading comments...