Thursday, 2 November 2017

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








3 comments:

  1. That is a subtle gotcha! Thanks for explaining it.

    ReplyDelete
  2. This really has covered a great insight on SQL Query . I found myself lucky to visit your page and came across this insightful read on Sql tutorial. Please allow me to share similar work on MSBI training course :-
    https://www.youtube.com/watch?v=mrv0e6Qa4_M

    Further check on this lecture on Pl Sql as well:-
    https://www.youtube.com/watch?v=phvcwekT9ZA

    ReplyDelete

SSIS Issues : A Day of Learning

Seldom are the days when you run into complex issues but resolve them in the shortest interval of time. Thanks to Larry Page and my fello...