Intersecting - 1

  • Thanks for the reply, Toreador.

  • RLilj33 (2/26/2014)


    Equivalent to:

    SELECT X AS 'Intersecting'

    FROM A

    JOIN b ON b.y = a.x;

    Except the ordering. INTERSECT returns the values in numeric order; JOIN returns the values in the order in which they were found in the left table (A).

    I didn't see anything in the BOL documentation on this. Anyone shed some light on this? Thanks.

    And the performance is better using INTERSECT, even without adding an ORDER BY to your statement (to get identical record sets).

    Can someone explain the 'why' of the performance difference?

  • Equivalent to:

    SELECT X AS 'Intersecting'

    FROM A

    JOIN b ON b.y = a.x;

    It's not equivalent, INTERSECT returns DISTINCT values.

    SELECT DISTINCT X AS 'Intersecting'

    FROM A

    JOIN b ON b.y = a.x;

  • Curious: the winner is?

    "EXISTS" with absolute less reads.

    SET STATISTICS IO ON

    GO

    SELECT DISTINCT x AS 'EXISTS'

    FROM A

    WHERE exists(SELECT * FROM B WHERE Y=x )

    GO

    SELECT x AS 'Intersecting'

    FROM A

    INTERSECT

    SELECT Y

    FROM B

    go

    SELECT DISTINCT x AS 'JOIN'

    FROM A

    JOIN B

    ON Y=x

    go

    Result:

    EXISTS

    1

    2

    20

    (3 row(s) affected)

    Tabella 'B'. Conteggio analisi 1, letture logiche 1, letture fisiche 0, letture read-ahead 0, letture logiche LOB 0, letture fisiche LOB 0, letture read-ahead LOB 0.

    Tabella 'A'. Conteggio analisi 1, letture logiche 1, letture fisiche 0, letture read-ahead 0, letture logiche LOB 0, letture fisiche LOB 0, letture read-ahead LOB 0.

    Intersecting

    1

    2

    20

    (3 row(s) affected)

    Tabella 'B'. Conteggio analisi 1, letture logiche 5, letture fisiche 0, letture read-ahead 0, letture logiche LOB 0, letture fisiche LOB 0, letture read-ahead LOB 0.

    Tabella 'A'. Conteggio analisi 1, letture logiche 1, letture fisiche 0, letture read-ahead 0, letture logiche LOB 0, letture fisiche LOB 0, letture read-ahead LOB 0.

    JOIN

    1

    2

    20

    (3 row(s) affected)

    Tabella 'Worktable'. Conteggio analisi 3, letture logiche 160, letture fisiche 0, letture read-ahead 0, letture logiche LOB 0, letture fisiche LOB 0, letture read-ahead LOB 0.

    Tabella 'B'. Conteggio analisi 1, letture logiche 1, letture fisiche 0, letture read-ahead 0, letture logiche LOB 0, letture fisiche LOB 0, letture read-ahead LOB 0.

    Tabella 'A'. Conteggio analisi 1, letture logiche 1, letture fisiche 0, letture read-ahead 0, letture logiche LOB 0, letture fisiche LOB 0, letture read-ahead LOB 0.

  • easy one thanks..

  • I always use Exists and Not Exists instead of Intersect and Except. I have never seen Intersect used in any DB that I have worked on.

  • Nice and easy - thanks, Ron!

  • LIKE the question on INTERSECT. Basic, but very useful. Thanks Ron!

    [font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
    Connect to me on LinkedIn

  • Easy one, Thanks Ron.

    ---------------------------------------------------
    "Thare are only 10 types of people in the world:
    Those who understand binary, and those who don't."

  • Nice to recollect INTERSECT. Basic, but I've almost forgotten to use it. Thanks Ron!

  • Carlo Romagnano (2/26/2014)


    First time I see 99% OK.

    😀

    Now it`s 95% still one of the easiest QotD ever.

  • paul.knibbs (2/26/2014)


    I had to go and run this one because I couldn't believe the answer was as simple as it first appeared. Lo and behold, it *was* that simple! :laugh:

    I thought there was some trick; but it is that simple 🙂

  • Good question.

    Thanks,

  • not completely 100% of right answer

  • Pretty straightforward. 🙂

Viewing 15 posts - 16 through 30 (of 31 total)

You must be logged in to reply to this topic. Login to reply