You are hereBlogs / Aakash's blog / How Sql Joins Works

How Sql Joins Works


By Aakash - Posted on 13 July 2011

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

AttachmentSize
Download script2.21 KB

Thanks Dude.. Nice explanation  

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.

Hello shantanu ,

Thanks for explaining this very important knowledge, by using a simple but complete example.

Thanks again.

 

Post new comment

The content of this field is kept private and will not be shown publicly. If you have a Gravatar account, used to display your avatar.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Allowed HTML tags: <a> <p> <span> <div> <h1> <h2> <h3> <h4> <h5> <h6> <img> <map> <area> <hr><br> <br /> <ul> <ol> <li> <dl> <dt> <dd> <table> <tr> <td> <em> <b> <u> <i> <strong><font> <del> <ins> <sub> <sup> <quote> <blockquote> <pre> <address> <code> <cite> <embed> <object> <param> <strike> <caption>
  • You can enable syntax highlighting of source code with the following tags: <c>, <cpp>, <csharp>, <drupal5>, <drupal6>, <java>, <javascript>, <mysql>, <php>, <python>, <ruby>, <sql>, <tsql>, <vbnet>, <xml>. Beside the tag style "<foo>" it is also possible to use "[foo]". PHP source code can also be enclosed in <?php ... ?> or <% ... %>.
  • Lines and paragraphs break automatically.

More information about formatting options