SQL SERVER – Interesting Observation of ON Clause on LEFT JOIN – How ON Clause affects Resultset in LEFT JOIN

Today I received email from Yoel from Israel. He is one smart man always bringing up interesting questions. Let us see his latest email first.

Hi Pinal,

I am subscribed to your blog and enjoy reading it. I have a question which has been bothering me for some time now.

When I want to filter records in a query, I usually put the condition in the WHERE clause. When I make an inner join, I can put the condition in the ON clause instead, giving the same result. But with left joins this is not the case. Here is a quote from the SQL Server documentation:

Although the placement of such predicates does not make a difference for INNER joins, they might cause a different result when OUTER joins are involved. This is because the predicates in the ON clause are applied to the table before the join, whereas the WHERE clause is semantically applied to the result of the join.

I still can’t fully grasp the difference. Perhaps you could explain this better? I am sure that many of your readers could benefit from this too.
Thank you.

Yoel,

You have asked very good question. Let us go through following example first. Download the script for this example here and execute in SSMS. I will explain the behavior as we go through the example. If any user does not want to read complete explanation, just go last paragraph of article which is in bold fonts.

USE AdventureWorks
GO
-- Create Table1
CREATE TABLE Table1
(ID INT, Value VARCHAR(10), Flag
INT)
GO
-- Populate Table1
INSERT INTO Table1 (ID, Value, Flag)
SELECT 1, 'First', 1
UNION ALL
SELECT 2, 'Second', 1
UNION ALL
SELECT 3, 'Third', 2
UNION ALL
SELECT 4, 'Fourth', 1
UNION ALL
SELECT 5, 'Fifth', 2
UNION ALL
SELECT 6, 'Sixth', 1
UNION ALL
SELECT 7, 'Seventh', 2
GO
-- Create Table2
CREATE TABLE Table2
(ID INT, Value VARCHAR(10), Flag
INT)
GO
-- Populate Table2
INSERT INTO Table2 (ID, Value, Flag)
SELECT 1, 'First', 1
UNION ALL
SELECT 2, 'Second', 1
UNION ALL
SELECT 3, 'Third', 2
UNION ALL
SELECT 8, 'Eightth', 1
UNION ALL
SELECT 9, 'Nineth', 2
GO
-- Check the data in Table1 and Table2
SELECT *
FROM Table1
SELECT *
FROM Table2
GO
-- INNER JOIN with WHERE Condition
SELECT *
FROM Table1 t1
INNER JOIN Table2 t2 ON t1.ID = t2.ID
WHERE t2.Flag = 1
GO
-- INNER JOIN with Additional Condition on ON clause
SELECT *
FROM Table1 t1
INNER JOIN Table2 t2 ON t1.ID = t2.ID AND t2.Flag = 1
GO
-- LEFT JOIN with WHERE Condition
SELECT *
FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.ID = t2.ID AND t2.Flag = 1
GO
-- LEFT JOIN with Additional Condition on ON clause
SELECT *
FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.ID = t2.ID
WHERE t2.Flag = 1
GO
-- Clean up tables
DROP TABLE Table1
DROP TABLE Table2
GO

Let us see the example in parts. Let us see the result of INNER JOIN clause. First of all we will run the query without WHERE clause and ON clause. Our result of join without any clause has to be same as they are essentially same query.

Now we will run the same query with WHERE clause and ON clause and compare our result with earlier result and later resultset.

As mentioned earlier it really does not matter for INNER JOIN if WHERE condition is moved to ON clause. In case of JOIN if ON clause (evaluated first) of WHERE clause (evaluated later) the result is same.

Now let us see the example of LEFT JOIN. First we will run both the queries without WHERE clause and ON clause. Our result of join without any clause has to be same as they are essentially same query.

Now we will run the same query with WHERE clause and ON clause and compare our result with earlier result and later resultset.

Now let us understand ON clause it is apply before JOIN that is why it retrieves all the result of Table2 where there are Flag = 1 but it does not affect Table1 so it retrieves all the rows of table1. When WHERE clause is applied it applies to complete result so it removes all the rows from Table1 and Table2 where Flag is not equal to 1, essentially keeping flag = 1 rows from Table1 and Table2.

I hope now this explanation is clear to Yoel. I am really waiting for feedback from my readers about this article. If you think this is interesting article, please share it on your social network and leave your comment here.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

63 thoughts on “SQL SERVER – Interesting Observation of ON Clause on LEFT JOIN – How ON Clause affects Resultset in LEFT JOIN

  1. Hi Pinal,

    As always you gave very simple and strait forward defination and example. Generally people would love to read strait forward defination which directly hit the mind. this is why I love to visit your blog very frequently.

    Thanks for your precious support to the community.

    Ritesh Shah

    • Beginner here, but I’m confused with the left join and the where clause vs ON clause. Are those reversed in the example?

  2. I blogged about this myself about a 1½ ago, when I noticed the behavior, mostly because at that time I had recently been told by a “database expert” that the location of conditions in “on” clause didn’t matter. And then when my own experiences differed, I decided to look into why.

    It can quickly cause problems for people writing SQL if they aren’t aware of the order that SQL gets “executed” in, because it isn’t an obvious situation when it doesn’t fail the execution. You just get a different result and all seems to be “perfect”.

    There’s an excellent explanation for this in the book T-SQL Querying from Microsoft Press as well.

  3. Checking for NULL will bring those missing rows back when it’s on the WHERE clause.

    Unfortunately, I deal with dynamic SQL for a legacy reporting system, and it’s easier to swap out WHERE clauses for certain scenarios than it is to deal with conditions contained within an ON clause.

    Just curious if you would generally recommend checking for NULL?

  4. “the ON clause are applied to the table before the join, whereas the WHERE clause is semantically applied to the result of the join.”

    Does it means that JOIN statement is preferred to use in SQL Server also because of speed? (result set is smaller when other conditions will apply)

  5. we can consider

    – LEFT JOIN with WHERE Condition
    SELECT *
    FROM Table1 t1
    LEFT JOIN Table2 t2 ON t1.ID = t2.ID AND t2.Flag = 1
    GO

    as

    – LEFT JOIN with WHERE Condition
    SELECT *
    FROM Table1 t1
    LEFT JOIN Table2 t2 ON (t1.ID = t2.ID AND t2.Flag = 1)
    GO

    so all the rows from t1 will be get.

  6. Hi Pinal, something I’ve previously come across that I thought was of relevance…

    The differences described are apparent when considering the Logical Query Processing Phases. This is the order in which the conditions of a query are evaluated. These phases generate a series of virtual tables with each virtual table feeding into the next phase (virtual tables not viewable). These phases and their order are:

    1. FROM
    2. ON
    3. OUTER
    4. WHERE
    5. GROUP BY
    6. CUBE | ROLLUP
    7. HAVING
    8. SELECT
    9. DISTINCT
    10 ORDER BY
    11. TOP

    Because the outer join is applied after the on clause, any rows eliminated by the on clause will still be included by the outer join.

  7. Pingback: SQL SERVER - Logical Query Processing Phases - Order of Statement Execution Journey to SQL Authority with Pinal Dave

  8. Hi all
    I looked at actual execution plans that have been generated.
    For

    SELECT *
    FROM Table1 t1
    LEFT JOIN Table2 t2 ON t1.ID = t2.ID AND t2.Flag = 1

    Table T1 is scaned
    Table T2 is scaned with predicate t2.Flag = 1

    then nested loop is applied with t1 as outer table and t2 as inner table, and logical operation applied with this nested loop is left outer join as expected

    but for

    SELECT *
    FROM Table1 t1
    LEFT JOIN Table2 t2 ON t1.ID = t2.ID
    WHERE t2.Flag = 1

    Table T1 is scaned
    Table T2 is scaned with predicate t2.Flag = 1

    and hash match operator is used as join with significant difference logical operation applied with this has match is inner join!!

    So as far as i have concluded in both cases table2 will be scanned with predicate Flag = 1 but during joining logical operation in second case by optimizer will be switched to inner join instead left outer join due to WHERE condition. This way query becomes same as if we have written

    SELECT *
    FROM Table1 t1
    INNER JOIN Table2 t2 ON (t1.ID = t2.ID AND t2.Flag = 1)

  9. Hi sir,
    I read your post and enjoying it very much.
    I have some questions, not able to find answer so finally I am asking with you.

    I have one doubt regarding inline UDF(user defined function).
    I am trying to see execution plan(Actual/Estimated) of inline UDF but it didnt display, why?

    Also, could you please describe the performence impact for sequence of Inner Join and Left Outer join in a Select Query in inline UDF.

    Regards,
    Satyanarayan Agrawal
    Software Developer

  10. Hi Pinal ,

    Thank you for this information . In your example you have mentioned something like this.

    SELECT *
    FROM Table1 t1
    INNER JOIN Table2 t2 ON t1.ID = t2.ID
    WHERE t2.Flag = 1
    GO
    — INNER JOIN with Additional Condition on ON clause
    SELECT *
    FROM Table1 t1
    INNER JOIN Table2 t2 ON t1.ID = t2.ID AND t2.Flag = 1

    Both of these will give you same result set, but is there any performance difference between the two?

    Regards,
    Joxin

    • For the inner join case, the results and the actual query plans are identical, so there should be no difference in performance.

      That being said, my stylistic preference is to put filters in the WHERE clause. Then when I see filters in a JOIN clause, I expect to see an outer join and a reason for the filter being there instead of its normal location.

  11. Hi Pinal,
    I saw ur blog its very helpful indeed, but i have some questions:
    I just altered ur left join and checking for is NUll in the same join.
    SELECT *
    FROM Table1 t1
    LEFT JOIN Table2 t2 ON t1.ID = t2.ID AND t2.Flag is null

    it results in some weired output. it displays everything as NULL from table2 . Any reason ??

  12. dear pinal,

    i want to know ,when we are joining more than three or more tables .In this scenario i want to use the left join on particular two table.In such condition what is the rule for putting join.

  13. Hi Mayur,
    I think the article

    http://blog.sqlauthority.com/2009/04/13/sql-server-introduction-to-joins-basic-of-joins/

    by Pinal himself gives more explanation on such scenarios. Please read that it may be helpful.
    It did help me a lot.

    The results that you get by the query
    SELECT *
    FROM Table1 t1
    LEFT JOIN Table2 t2 ON t1.ID = t2.ID AND t2.Flag is null

    Are correct and as expected.

    NOTE :
    1. Both conditions are in the ON clause so result will be created based on both.
    2. Since no WHERE clause all records of Left table will be part of result set.

    Step 1:
    t1.ID = t2.ID

    Results :
    1 First 1 1 First 1
    2 Second 1 2 Second 1
    3 Third 2 3 Third 2
    4 Fourth 1 NULL NULL NULL
    5 Fifth 2 NULL NULL NULL
    6 Sixth 1 NULL NULL NULL
    7 Seventh 2 NULL NULL NULL

    Step 2 :
    AND t2.Flag is null
    as per this condition no rows of Table2 from step 1 qualifies hence you see all values as NULL for Table2.
    Hope this helps.

    –Nilesh

  14. If your explaination is correct , why folling query comes back with all rows from the table1

    SELECT *
    FROM Table1 t1
    LEFT JOIN Table2 t2 ON t1.ID = t2.ID
    AND (t1.flag = 1 and t2.Flag = 1)

    As per your explaination , it should bring only

    1,2,4,6

  15. Ok … I got the answer for the question I asked .. try this query and you will see the difference

    SELECT *
    FROM Table1 t1
    LEFT JOIN Table2 t2 ON t1.ID = t2.ID
    AND (t1.flag = 2 and t2.Flag = 1)

    In an outer join, the join filters (expressions) that you specify in the ON clause determine which rows of the subordinate table join to the dominant (or outer) table. The dominant table, by definition, returns all its rows in the joined table. That is, a join filter in the ON clause has no effect on the dominant table.

    If the ON clause specifies a join filter on the dominant table, the database server joins only those dominant table rows that meet the criterion of the join filter to rows in the subordinate table. The joined result contains all rows from the dominant table.

  16. I find your site too good. This particular post realy helped me clear my confusions related to ON clause and WHERE clause in LEFT JOINS.

  17. This really good article with easy explaination. I got lot of confusion on joins populating wiered result, when I join more then 2 tables and put condition on (ON clause) and where clause.

  18. Hi Pinal,
    it would be great if you answer separately.

    point 1 )

    SELECT *
    FROM Table1 t1
    LEFT JOIN Table2 t2 ON t1.ID = t2.ID AND t2.Flag = 1

    you saying that in above select statement , rows from table2 will be filtered where flag = 1 (i.e. 1 , 2 . 8 will be there ) before joining. Now left join of table1 and table2( rows: 1,2,8) will be there and will get the required result.

    Right ?

    point 2 )
    what if

    SELECT *
    FROM Table1 t1
    LEFT JOIN Table2 t2 ON t1.ID = t2.ID AND t1.Flag = 1

    i could not get correct result based on previous assumption.
    please explain

    • “point 2 )
      what if

      SELECT *
      FROM Table1 t1
      LEFT JOIN Table2 t2 ON t1.ID = t2.ID AND t1.Flag = 1″

      Even i have this question.. Did you get any solution for this?

      • Notice above comments by Amit by Mar 27, 2010.
        “In an outer join, the join filters (expressions) that you specify in the ON clause determine which rows of the subordinate table join to the dominant (or outer) table. The dominant table, by definition, returns all its rows in the joined table. That is, a join filter in the ON clause has no effect on the dominant table.

        If the ON clause specifies a join filter on the dominant table, the database server joins only those dominant table rows that meet the criterion of the join filter to rows in the subordinate table. The joined result contains all rows from the dominant table.”

        You will get all rows of left table and rows from right table will base on ‘On’ filter. Here only t1.ID = t2.ID. Note “t1.Flag =1″ apply to right table actually do nothing.

        • SELECT *
          FROM Table1 t1
          LEFT JOIN Table2 t2 ON t1.ID = t2.ID AND t1.Flag = 1″

          vs.

          SELECT *
          FROM Table1 t1
          LEFT JOIN Table2 t2 ON t1.ID = t2.ID
          where t1.Flag = 1″

          return different rows

          • also run following queries

            –LEFT JOIN with WHERE Condition
            SELECT *
            FROM Table1 t1
            LEFT JOIN Table2 t2 ON t1.ID = t2.ID AND t1.Flag = 2
            GO

            ID Value Flag ID Value Flag
            —————————————————-
            1 First 1 NULL NULL NULL
            2 Second 1 NULL NULL NULL
            3 Third 2 3 Third 2
            4 Fourth 1 NULL NULL NULL
            5 Fifth 2 NULL NULL NULL
            6 Sixth 1 NULL NULL NULL
            7 Seventh 2 NULL NULL NULL
            —————————————————————

            –LEFT JOIN with Additional Condition on ON clause
            SEL–LEFT JOIN with Additional Condition on ON clause
            SELECT *
            FROM Table1 t1
            LEFT JOIN Table2 t2 ON t1.ID = t2.ID
            WHERE t1.Flag = 2
            GO

            ID Value Flag ID Value Flag
            —————————————————-
            3 Third 2 3 Third 2
            5 Fifth 2 NULL NULL NULL
            7 Seventh 2 NULL NULL NULL
            ————————————————————-

          • First query return

            ID Value Flag ID Value Flag
            —————————————————-
            1 First 1 1 First 1
            2 Second 1 2 Second 1
            3 Third 2 3 NULL NULL NULL
            4 Fourth 1 NULL NULL NULL
            5 Fifth 2 NULL NULL NULL
            6 Sixth 1 NULL NULL NULL
            7 Seventh 2 NULL NULL NULL
            —————————————————

            Second query return

            ID Value Flag ID Value Flag
            —————————————————-
            1 First 1 1 First 1
            2 Second 1 2 Second 1
            4 Fourth 1 NULL NULL NULL
            6 Sixth 1 NULL NULL NULL
            ————————————————

  19. hi

    i have one query
    i want to update the data in table 1 based on certain condition which must be satisfy by table 2 how should i do this please give small example
    waiting for u r reply

    thanks

  20. Thanks Pinal..

    Your article saved me from committing a big mistake while writing a query.

    Thanks for the good work

  21. A very small grammatical error but sadly as it is in the title, I thought I would bring it to your attention.

    I realise that English is not your main language and totally admire your ability to master it and write such fantastic articles using it. I hope that you are not offended to be informed of this slight error in what is a very misunderstood area of the language.

    “SQL SERVER – Interesting Observation of ON Clause on LEFT JOIN – How ON Clause Effects Resultset in LEFT JOIN”

    Should be

    “SQL SERVER – Interesting Observation of ON Clause on LEFT JOIN – How ON Clause affects Resultset in LEFT JOIN”

    The difference being the word Affects (Effects).
    Affect is a verb where one thing affects another.
    Effect is a noun where we see the effect that one thing had on the other.
    (Effect can also be an adjective depending on how it is used however affect is always a verb os it is doing something to something else).

    I think you have done a really good job of explaining the issue. I have often had to explain this weird behaviour to DB developers but in future I think I will simply point them at your example.

    Regards

    Dave

  22. David Bridge, you are right. Pinal Dave is a hero!

    But we do all make mistakes with grammar and punctuation at times, especially when there is no spelling or grammar checker. For example, you failed to punctuate the word ‘however’ correctly in your comment and misplaced the period as well. Your sentende should have read:

    (Effect can also be an adjective depending on how it is used; however, affect is always a verb os it is doing something to something else.)

    So it goes. I think the meaning of your comment as well as the meaning of Pinal Dave’s title are completely clear. So I would call your comment and mine both nitpicks.

    :>)

  23. SELECT *
    FROM Table1 t1
    LEFT JOIN Table2 t2 ON t1.ID = t2.ID AND t2.Flag is null

    ———-

    How To Hanle Left Join …
    If i want want to eliminate Resultant Record when t2.Id is Not Available in Table t2 from the above Query Result

  24. hai Pinal…

    Please Give me Solution for the below Problem!!!!!!!!

    SELECT *
    FROM Table1 t1
    LEFT JOIN Table2 t2 ON t1.ID = t2.ID AND t2.Flag is null

    ———-

    How To Hanle the Query…

    If i want want to eliminate Resultant Record when t2.Id is Not Available in Table t2 and t1.Id is Not Null from the above Query Result

  25. hai Pinal…

    Please Give me Solution for the below Problem!!!!!!!!

    SELECT *
    FROM Table1 t1
    LEFT JOIN Table2 t2 ON t1.ID = t2.ID AND t2.Flag is null

    ———-

    How To Hanle the Query…

    If i want want to eliminate Resultant Record when t2.Id is Not Available in Table t2 and t1.Id is Not Null from the above Query Result

    Note : t1.Id is Nullable Column in table t1
    t2.Id is Not Nullable and Identity Column in table t2

  26. Create table #temp1(EmpNo int Identity Not Null, BadgeID int Null )
    Insert into #temp1 values (1)
    Insert into #temp1 values (NULL)
    Insert into #temp1 values (3)

    Create table #temp2(BadgeID int Identity Not NUll, Description varchar(20) Not NUll)
    Insert into #temp2 values ( ‘Mixing’)
    Insert into #temp2 values ( ‘Simplex’)
    Insert into #temp2 values ( ‘Combing’)
    Insert into #temp2 values ( ‘Adidas’)

    Delete from #temp2 where Description = ‘Combing’

    ——————————————–

    Select * from #temp1

    Result:
    EmpNo BadgeID
    1 1
    2 NULL
    3 3
    ———————————————

    Select * from #temp2

    Result :
    BadgeID Description
    1 Mixing
    2 Simplex
    4 Adidas
    ——————————————–

    Select a.EmpNo , b.Description from #temp1 a
    left outer join
    #temp2 b on a.BadgeID = b.BadgeID

    Result:
    EmpNo Description
    1 Mixing
    2 NULL
    3 NULL

    From the above Result, i want to eliminate the last record EmpNo = 3, since its Corresponding BadgeID 3 is not in table #temp2.

    I want the result as :

    EmpNo Description
    1 Mixing
    2 NULL

    • How do you get ‘NULL’ in the Description column if there isn’t such record in that column. May be you want the result as:

      EmpNo Description
      ————————–
      1 Mixing
      2 Simplex

      If it is so, then you can try out the query this way:

      SELECT a.EmpNo , b.Description FROM #temp1 a
      INNER JOIN #temp2 b ON a.EmpNo = b.BadgeID

      Regards,
      Raghav

      • This is not i Expect….
        By the by,… EmpNo and BadgeID can’t be equal all the time..

        Any way, .. thanks…. for your try..

        I need to get TextDescription from Master table for the corresponding ID in transaction table.
        Thats all the concept.. if the corresponding ID is not available in Master table.,, we have to omit that record While fetching from Trasaction table.

        Thats it………..

    • Hi janani,
      Please try this….

      Select a.EmpNo , b.Description
      from #temp1 a INNER JOIN #temp2 b
      on (a.BadgeID = b.BadgeID OR A.BadgeID IS NULL)

  27. Hi Pinal,
    First of all anything, I really appreciate your efforts and authority on Sql server; I was doing oracle and recently I started playing hands on MS Sql; one of my friend referred your blog and believe me this is the first place I look if I have any question.

    Today, I had question using conditions within ON clause and I landed up here; This article is exactly my question and your explanation was superb but….. the part which I did not understood and challenge I am facing is ..

    What if I apply conditions on first table
    relating to your above example –
    SELECT *
    FROM Table1 t1
    LEFT JOIN Table2 t2 ON t1.ID = t2.ID AND t1.Flag != 1 — or using t1.Flag = 2

    I expected the result to be:
    T1 T2
    Value Flag Value Flag
    Third 2 Third 2
    Fifth 2 Null Null
    Seventh 2 Null Null

    but its also returning all values of table-1 with Flag =1 and joining with table – 2.

    I did not run your tables but I have a similar example. Please guide me if I am missing anything; I thought using condition within ON clause improves performance since it reduces the number of rows to be joined based on the conditions. Please suggest and clarify.

    Thanks,
    Sam.

  28. A way to understand this is to think of an inner join as a filter only showing rows where the on condition match.

    A outer join keeps all records of the outer table and fill in data on the outer table based on the on condition. if there is not a match in the on condition data will not be joined on the outer table.

    This is how I understand it and mabe it can help others

  29. Pingback: SQL SERVER – Weekly Series – Memory Lane – #020 | SQL Server Journey with SQL Authority

  30. CREATE TABLE #DEL1(ID INT IDENTITY(1,1),VALUE VARCHAR(MAX),FLAG CHAR(1))

    CREATE TABLE #DEL2(ID INT IDENTITY(1,1),VALUE VARCHAR(MAX),FLAG CHAR(1))

    INSERT INTO #DEL1 VALUES(‘1ST’,1),(‘2ND’,1),(‘3RD’,2),(‘4TH’,1),(‘5TH’,2),(‘6TH’,1)

    INSERT INTO #DEL2 VALUES(‘1ST’,1),(‘2ND’,1),(‘3RD’,2),(‘4TH’,2),(‘5TH’,2)

    SELECT * FROM #DEL1
    SELECT * FROM #DEL2

    —————————————————————
    — LEFT JOIN with Additional Condition on ON clause
    SELECT *
    FROM #DEL1 A
    LEFT JOIN #DEL2 B ON A.ID=B.ID AND B.FLAG=1
    —————————————————————
    /* EQUILENT TO THIS QRY */
    SELECT *
    FROM #DEL1 A
    LEFT JOIN (SELECT * FROM #DEL2 WHERE FLAG=1) B ON A.ID=B.ID
    /* SO I OBSERVED THAT HERE ORDER OF EXECUTION IS FROM ,()PARANTHESIS,ON */
    —————————————————————

    /*
    NOTE : LEFT OUTER JOIN MEANS THAT MATCHING THE VALUES OF RIGHT SIDE TABLE AND UNMATCHING VALUES OF LEFT SIDE TABLE
    .SO NOW THE QRY WILL BE LIKE THIS

    */
    —————————————————————
    SELECT *
    FROM
    (
    SELECT * FROM #DEL2 WHERE FLAG=1
    ) A
    LEFT JOIN
    (
    SELECT * FROM #DEL1
    ) B ON A.ID=B.ID AND A.FLAG=1
    —————————————————————
    /* EQUILENT TO THIS QRY */
    SELECT *
    FROM #DEL1 A
    LEFT JOIN #DEL2 B ON A.ID=B.ID
    WHERE B.FLAG=1
    —————————————————————
    –CLEAN UP THE TABLES
    DROP TABLE #DEL1
    DROP TABLE #DEL2

  31. if i want the result grouped by t2.Flag instead of passing it as default value then how to do?
    for example
    i have
    tableA tableB
    id student id data year
    1 s1 1 d1 2010
    2 s2 2 d2 2010
    1 d3 2012

    now if i join i want the result like
    id student data year
    1 s1 d1 2010
    1 s1 d3 2010
    2 s2 d2 2012
    2 s2 NULL 2012

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s