December 31, 2019 at 4:25 pm
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?
December 31, 2019 at 5:21 pm
For an INNER join there is no difference - and you can validate that by viewing the execution plan generated from each statement.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 1, 2020 at 3:56 pm
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()
January 2, 2020 at 1:17 pm
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 2, 2020 at 3:59 pm
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.
January 2, 2020 at 3:59 pm
...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.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
January 2, 2020 at 5:07 pm
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) A socialist is someone who will give you the shirt off *someone else's* back.
January 3, 2020 at 3:58 pm
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) A socialist is someone who will give you the shirt off *someone else's* back.
January 3, 2020 at 4:02 pm
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 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply