Difference between where and INNER JOIN comparing a value

  • developerlearn999

    SSC Enthusiast

    Points: 174

    what is the difference between :

    Declare Phone varchar(50)
    UPDATE e
    Set e.[State]=2
    FROM Events e
    INNER JOIN AccoutsAndPhones c on e.ClientID=c.ClientID
    And c.phone=@Phone
    WHERE e.Schedule>GETDATE()

     

    and

    Declare Phone varchar(50)
    UPDATE e
    Set e.[State]=2
    FROM Events e
    INNER JOIN AccoutsAndPhones c on e.ClientID=c.ClientID
    WHERE e.Schedule>GETDATE()
    and c.phone=@Phone

    the c.phone=@Phone is in different places.

    is there performance impact or different results?

  • Jeffrey Williams

    SSC Guru

    Points: 88699

    For an INNER join there is no difference - and you can validate that by viewing the execution plan generated from each statement.

     

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Jonathan AC Roberts

    SSCoach

    Points: 17341

    They all produce the same results. There are usually many ways to get the same results from different queries, for example, all of these will also produce the same results:

     FROM Events e
    INNER JOIN AccoutsAndPhones c
    ON e.ClientID=c.ClientID
    AND c.phone=@Phone
    AND e.Schedule>GETDATE()

    FROM Events e
    CROSS JOIN AccoutsAndPhones c
    WHERE e.Schedule>GETDATE()
    AND c.phone=@Phone
    AND e.ClientID=c.ClientID

    FROM Events e
    LEFT JOIN AccoutsAndPhones c
    ON e.ClientID=c.ClientID
    AND e.Schedule>GETDATE()
    WHERE c.phone=@Phone

    FROM Events e
    RIGHT JOIN AccoutsAndPhones c
    ON e.ClientID=c.ClientID
    AND c.phone=@Phone
    WHERE e.Schedule>GETDATE()

    FROM Events e
    CROSS APPLY (SELECT * FROM AccoutsAndPhones c WHERE e.ClientID=c.ClientID) c
    WHERE e.Schedule>GETDATE()
    AND c.phone=@Phone

    FROM Events e
    OUTER APPLY (SELECT * FROM AccoutsAndPhones c WHERE e.ClientID=c.ClientID) c
    WHERE e.Schedule>GETDATE()
    AND c.phone=@Phone

    Because your query is not using any of the data in table [AccoutsAndPhones], it's just checking a row exists, you can write the query like this (which would be my personal choice):

     FROM Events e
    WHERE EXISTS(SELECT *
    FROM AccoutsAndPhones c
    WHERE e.ClientID=c.ClientID
    AND c.phone=@Phone)
    AND e.Schedule>GETDATE()

     

  • Grant Fritchey

    SSC Guru

    Points: 396751

    While the results may be the same, putting the JOIN criteria and the filtering criteria into the WHERE clause is less clear. I'd always default to having the JOIN criteria in the JOIN definition and, at least for an INNER JOIN, the filtering criteria in the WHERE clause (OUTER JOIN, if the filter is on the OUTER table, it needs to go to the JOIN criteria or it converts the OUTER JOIN to an INNER JOIN which isn't good).

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • jcelko212 32090

    SSCrazy Eights

    Points: 9047

    The short answer is that both the in fixed operator and set oriented where clause forms of an inner join produce the same results. An inner join is an inner join.. A more complete answer is that we don't need a varying length string of 50characters for a phone number. You're just asking for a disaster. Next, there is no such thing as a generic "state" because in a valid data model it would have to be the state of something in particular or alternately, a state code which would be two letters in the US. In a valid data model. There would never be such a thing as accounts and phones as a table in itself; there would be a relationship table with a better name. Please read the metadata committee rules ISO 11179 and any book on basic data modeling. The old Sybase "getdate()" has been replaced with the ANSI/ISO standard current_timestamp. Likewise, we need to have "schedule_<something>" for a column name; maybe this was supposed to be a date?

    But most important of all, the old Sybase UPDATE ..FROM..   syntax does not work. It has cardinality problems! When the join returns multiple rows, the row that is physically last in physical storage (notice the word physical over and over in this!) will be used. Any change in the index, or just the execution order by the query processor can pull out a completely different row. You also need to look at the MERGE statement.

    UPDATE Events AS E

    SET E.foobar_state = 2

    WHERE E.schedule_date > CAST(CURRENT_TIMESTAMP AS DATE)

    AND EXISTS(SELECT *

    FROM Client_Phonebook AS B

    WHERE B.client_id = E.client_id

    AND B.phone_nbr = @phone_nbr);

    >> is there performance impact or different results? <<

    Was the correct answer important to you :-)?

    Please post DDL and follow ANSI/ISO standards when asking for help. 

  • ScottPletcher

    SSC Guru

    Points: 98610

    Grant Fritchey wrote:

    ...OUTER JOIN, if the filter is on the OUTER table, it needs to go to the JOIN criteria or it converts the OUTER JOIN to an INNER JOIN which isn't good.

    People colloquially say that, but it's not technically true, of course.  What's really happening is that an OUTER join, SQL sets all columns of a missing table to NULL.  Therefore, if you specify a condition in the WHERE against that table, the condition fails, because any comparison against NULL fails.  But it's still not an "INNER JOIN", since you can check for NULL in the WHERE clause and allow the row from the first table to still be shown, even though the other table did not have a matching row.  If it were actually an INNER JOIN, of course that wouldn't be true, since there must be a matching row in the second table for an INNER JOIN to return the row from the first table.

    A description here is not as clear as an example.  When I have time, I'll come back and post an example.

     

    • This reply was modified 9 months, 3 weeks ago by  ScottPletcher. Reason: Reworded, for clarity

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • ScottPletcher

    SSC Guru

    Points: 98610

    Here are queries to demonstrate the points above.  Assume for this example that B.col2 is a NOT NULL column.

    ;WITH cte_A AS ( SELECT * FROM (VALUES(1, 'A', 10),(2, 'B', 20), (3, 'C', 30)) AS data(key_col, col1, col2)
    ), cte_B AS ( SELECT * FROM (VALUES(2, 'B', 20), (4, 'D', 40)) AS data(key_col, col1, col2)
    )
    SELECT A.*, B.*
    FROM cte_A A
    LEFT OUTER JOIN cte_B B ON B.key_col = A.key_col
    WHERE B.col2 >= 1

    --vs.

    ;WITH cte_A AS ( SELECT * FROM (VALUES(1, 'A', 10),(2, 'B', 20), (3, 'C', 30)) AS data(key_col, col1, col2)
    ), cte_B AS ( SELECT * FROM (VALUES(2, 'B', 20), (4, 'D', 40)) AS data(key_col, col1, col2)
    )
    SELECT A.*, B.*
    FROM cte_A A
    LEFT OUTER JOIN cte_B B ON B.key_col = A.key_col
    WHERE (B.col2 IS NULL OR B.col2 >= 1)

    --vs.

    ;WITH cte_A AS ( SELECT * FROM (VALUES(1, 'A', 10),(2, 'B', 20), (3, 'C', 30)) AS data(key_col, col1, col2)
    ), cte_B AS ( SELECT * FROM (VALUES(2, 'B', 20), (4, 'D', 40)) AS data(key_col, col1, col2)
    )
    SELECT A.*, B.*
    FROM cte_A A
    LEFT OUTER JOIN cte_B B ON B.key_col = A.key_col AND B.col2 >= 1


    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • ScottPletcher

    SSC Guru

    Points: 98610

    I left one out, the sample INNER JOIN:


    ;WITH cte_A AS ( SELECT * FROM (VALUES(1, 'A', 10),(2, 'B', 20), (3, 'C', 30)) AS data(key_col, col1, col2)
    ), cte_B AS ( SELECT * FROM (VALUES(2, 'B', 20), (4, 'D', 40)) AS data(key_col, col1, col2)
    )
    SELECT A.*, B.*
    FROM cte_A A
    INNER JOIN cte_B B ON B.key_col = A.key_col
    WHERE (B.col2 IS NULL OR B.col2 >= 1)

    --vs. the LEFT OUTER JOIN below, note the different results. Demonstrating that specifying a WHERE clause on outer table column does not "make it an INNER JOIN". It's a technical point, but it's an important concept to understand.

    ;WITH cte_A AS ( SELECT * FROM (VALUES(1, 'A', 10),(2, 'B', 20), (3, 'C', 30)) AS data(key_col, col1, col2)
    ), cte_B AS ( SELECT * FROM (VALUES(2, 'B', 20), (4, 'D', 40)) AS data(key_col, col1, col2)
    )
    SELECT A.*, B.*
    FROM cte_A A
    LEFT OUTER JOIN cte_B B ON B.key_col = A.key_col
    WHERE (B.col2 IS NULL OR B.col2 >= 1)

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • Jonathan AC Roberts

    SSCoach

    Points: 17341

    You would normally write the left join as:

    ;WITH cte_A AS ( SELECT * FROM (VALUES(1, 'A', 10),(2, 'B', 20), (3, 'C', 30)) AS data(key_col, col1, col2)
    ), cte_B AS ( SELECT * FROM (VALUES(2, 'B', 20), (4, 'D', 40)) AS data(key_col, col1, col2)
    )
    SELECT A.*, B.*
    FROM cte_A A
    LEFT OUTER JOIN cte_B B ON B.key_col = A.key_col
    AND B.col2 >= 1

    or if you wanted rows where the value of col2 in the table is actually stored with a NULL:

    ;WITH cte_A AS ( SELECT * FROM (VALUES(1, 'A', 10),(2, 'B', 20), (3, 'C', 30)) AS data(key_col, col1, col2)
    ), cte_B AS ( SELECT * FROM (VALUES(2, 'B', 20), (4, 'D', 40)) AS data(key_col, col1, col2)
    )
    SELECT A.*, B.*
    FROM cte_A A
    LEFT OUTER JOIN cte_B B ON B.key_col = A.key_col
    AND (B.col2 >= 1 OR B.col2 IS NULL)

     

Viewing 9 posts - 1 through 9 (of 9 total)

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