How Sql Joins Works
Submitted by shantanu on Wed, 07/13/2011 - 02:14
To make this article, I have taken Table A and Table B as reference tables for explaining.
SELECT * FROM A id name RowState ----------- ------------------------------ -------- 1 A1 1 2 A2 1 3 A3 0 4 A4 0 5 A5 1 (5 row(s) affected) SELECT * FROM B id name RowState aid ----------- ------------------------------ -------- ----------- 1 B1 1 1 2 B2 0 2 3 B3 1 3 4 B4 0 NULL 5 B5 1 NULL 6 B6 0 1 (6 row(s) affected) SELECT A.id, A.name AS ANAME, b.name AS BNAME FROM a LEFT JOIN B ON A.id = b.aid WHERE A.RowState =0 id ANAME BNAME ----------- ------------------------------ ------------------------------ 3 A3 B3 4 A4 NULL (2 row(s) affected) SELECT A.id, A.name AS ANAME, b.name AS BNAME FROM a LEFT JOIN B ON A.id=b.aid AND A.RowState =0 id ANAME BNAME ----------- ------------------------------ ------------------------------ 1 A1 NULL 2 A2 NULL 3 A3 B3 4 A4 NULL 5 A5 NULL (5 row(s) affected)
Left join specifies, all the rows on the left hand side of the join (A LEFT JOIN B i.e. A) will always be the part of an output until filtered in Where Clause. Tables getting joined(A & B here) will be matched on the basis of All Records from 'A' after getting filtered from conditions within join i.e. A.RowState = 0 + All Records from 'B' after getting filtered from conditions within join i.e. (B.RowState not used right now) Both merged on the basis of joining condition i.e. A.id=b.aid For query given in the beginning, Following process will carry on Records from A, that will surely come in output unless filtered by WHERE clause which is i.e.
id name RowState ----------- --------------- -------- 1 A1 1 2 A2 1 3 A3 0 4 A4 0 5 A5 1
From Table A, following records will contribute in joining as a result of A.RowState = 0
id name RowState ----------- -------------- -------- 3 A3 0 4 A4 0
+ From Table B, all records will contribute in joining as no filter is applied(if B.RowState = 0 would have been applied Row(2,4,6) would have been a part of join)
id name RowState aid ----------- ------------ -------- ----------- 1 B1 1 1 2 B2 0 2 3 B3 1 3 4 B4 0 NULL 5 B5 1 NULL 6 B6 0 1
Select Records A.id=b.aid
id name RowState aid ----------- ---------- -------- ----------- 1 B1 1 1 2 B2 0 2 3 B3 1 3 Matched IN above two result SET 4 B4 0 NULL 5 B5 1 NULL 6 B6 0 1
When selected all columns, Result of above matches would look like
TABLE B TABLE A id name RowState aid id name RowState --- ----- --------- ----- ---- ------ ---------- 3 B3 1 3 3 A3 0
Now we comes back to (A LEFT JOIN B) Records from A, that will surely come in output(i.e. 5 records in this case specifed above) but only some/all of the rows will contribute in joining both tables(record came as an output of joining conditions).
id name RowState BName --- ------- ---------- ------- 1 A1 1 NULL 2 A2 1 NULL 3 A3 0 B3 4 A4 0 NULL 5 A5 1 NULL
But if we applies WHERE A.RowState =0, It will result me following result after going with above process
id name RowState BName --- ------- ---------- ------- 3 A3 0 B3 4 A4 0 NULL
| Attachment | Size |
|---|---|
| Download script | 2.21 KB |
Nice Explanation
Thanks Dude.. Nice explanation
Website Design
Example is very well explained and that too with clarity. I use to hate Joins, as it was confusing me to write queries and fetch data from various tables. Every time I used to fetch opp data that are needed.
Well Explained
Hello shantanu ,
Thanks for explaining this very important knowledge, by using a simple but complete example.
Thanks again.
Post new comment