Which of these SQL queries is better and why?

Question

I'm still in the process of learning SQL. I've done similar queries in two different ways, and was wondering which is better and why.

UPDATE R
    SET R.something = 1
    FROM Table1 R
    JOIN Table2 U
    ON R.value1 = U.value2
    WHERE
        U.value3 BETWEEN 1 AND 5

or

UPDATE R
    SET R.something = 1
    WHERE R.value1 IN
    (SELECT U.value2
    FROM U
    WHERE
        U.value3 BETWEEN 1 AND 5
    )

Answer 1

Your question does not have a single answer. SQL is a descriptive language, not a procedural language. It depends on the database engine which is going to be more efficient. In addition, indexes can have a big effect on performance.

Your two queries, by the way, are not equivalent. The first can return multiple rows, when there are multiple values in "U". The version with the "IN" does an implicit "DISTINCT". To fix this you would need to add in a specific "DISTINCT".

UPDATE R
    SET R.something = 1
    FROM Table1 R
         JOIN (select distinct value2
               from Table2 U
               WHERE U.value3 BETWEEN 1 AND 5 
              ) u
         ON R.value1 = U.value2  

Also, although I personally like the "FROM" statement in the update, not all databases support it. The version with the "IN" is compatible across a wider range of database engines.

Answer 2

It all depends on the database that you plan to use (Oracle, SQL Server and etc), its version and sometimes on the amount of data in your tables. But generally you should prefer JOINs, as they are easier for most optimizers and has less gotchas with nulls.