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
