none
(Sumber: SQL Server) Join Condition RRS feed

  • Pertanyaan

  • Dear all,

    Ada yang bisa menjelaskan kegunaan condition setelah join, kenapa hasilnya berbeda dengan condition yang diletakkan setelah Where statement. Misal,


    ParentID

    1

    2

    3

    4


    ChildID    - ParentID

    1                 - 1

    2                 - 2


    Setelah Where :


    SELECT

                    [p].*

    FROM

                    [dbo].[Parent] AS p

                    LEFT JOIN [dbo].[Child] AS c ON [p].[ParentID] = [c].[ParentID]

    WHERE

                    [c].[ParentID] IS NULL

    ----------------------------------------------------------------------------------------

    3

    4


    Namun jika Condition diletakkan setelah join akan berbeda :


    SELECT

                    [p].*

    FROM

                    [dbo].[Parent] AS p

                    LEFT JOIN [dbo].[Child] AS c ON [p].[ParentID] = [c].[ParentID]

                                    AND [c].[ParentID] IS NULL

    ----------------------------------------------------------------------------------------

    1

    2

    3

    4

                

    Please feel free to contact me if you have any questions or comments.



    Best Regards,
    Agnes Sannie [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.


    Jumat, 26 April 2013 07.41
    Moderator

Jawaban

  • Tabel Parent di left join dengan tabel Child berdasarkan ParentID. Jika ParentID cocok maka c.* akan berisi data, jika tidak cocom maka c.* berisi NULL. Pada query pertama WHERE c.ParentID IS NULL maka akan memunculkan data yang c.* berisi NULL (ada ParentID di tabel Parent tapi tidak ada di Child) Sedangkan query kedua pada kondisi left join (ON ....) tidak ada yang cocok karena tidak ada ParentID yang NULL di tabel Child. Karena operasi left join dan tidak ada filter di WHERE clause maka akan mengembalikan semua data p.* dan c.* berisi NULL.

    Dijawab oleh: Ahmad Masykur


    Best Regards,
    Agnes Sannie [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Jumat, 26 April 2013 07.44
    Moderator