Here's a blog-like post (really a Facebook Note) I made about the differences a while back:
Having the right syntax for databases is critical. Here's the moderately optimized SQL for two DB systems for two different updates:
SQL Server
Update tok
Set spid = est.sid
FROM @Tok tok
INNER JOIN @eST est ON tok.cs = est.cs
For SQL Server -- using the table alias for the update is critical for good performance. Another trick for SQL Server can be to do something like (which is recommended by the SQL Server team -- and it yields excellent speed improvements):
Update tok
Set spid = est.sid
FROM (SELECT * FROM @Tok) tok
INNER JOIN @eST est ON tok.cs = est.cs
Postgres
Update _Tok
Set spid = est . sid
FROM _Tok tok
INNER JOIN _eST est ON tok.cs = est.cs
WHERE tok.cs = _Tok.cs
For Posgres, the performance critical line is the addition of the WHERE clause to bind the Update table to the FROM table. It makes sense -- once you understand (from the SQL Server perspective) that it is equivalent to using the alias in the Update clause
Having the right syntax for databases is critical. Here's the moderately optimized SQL for two DB systems for two different updates:
SQL Server
Update tok Set spid = est.sid FROM @Tok tok INNER JOIN @eST est ON tok.cs = est.cs
For SQL Server -- using the table alias for the update is critical for good performance. Another trick for SQL Server can be to do something like (which is recommended by the SQL Server team -- and it yields excellent speed improvements):
Update tok Set spid = est.sid FROM (SELECT * FROM @Tok) tok INNER JOIN @eST est ON tok.cs = est.cs
Postgres
Update _Tok Set spid = est . sid FROM _Tok tok INNER JOIN _eST est ON tok.cs = est.cs WHERE tok.cs = _Tok.cs
For Posgres, the performance critical line is the addition of the WHERE clause to bind the Update table to the FROM table. It makes sense -- once you understand (from the SQL Server perspective) that it is equivalent to using the alias in the Update clause