SQL Server - update join - setting specific values in on clause

Question

Novice SQL Server

Can someone explain the logic of the below update with a join. I don't understand the setting of a specific value in the 'on' clause...

(#c is a tiny temp table with fields: cert, prod, cov, i)

update m
set inieff = i
from tmempt m
    inner join #c on clntcode = '01208' and
                     polno = '00000408' and
                     certno = cert and
                     prodcode = prod and
                     covgcode = cov and
                     rcdsts = 'A'

...so how does '..on clntcode='01208' and polno='00000408'' work in the context of a join? I thought that joins work by field relationships...

Thanks J

Hi. Please read the edit help re formatting code. Also please use laguage, not fonts, to make your point. This is unreadable. See the formatted version of your post below the edit box. What does "joins work by field relationships" mean? Or "..."? What textbook definition of join have you read & how is it not answering your question? This is just a faq asking for yet another description of JOIN to be written. Where did you get stuck in some authoritative one that has already been written? Please read & act on [ask], hits googling 'stackexchange homework' & the downvote arrow mouseover text. — philipxy, Oct 12 '18 at 06:24
[CROSS JOIN = (INNER) JOIN = comma (",")](https://stackoverflow.com/a/25957600/3404097) — philipxy, Oct 12 '18 at 07:53

score 1 · Answer 1 · answered Oct 12 '18 at 11:21

An inner join is simple. For each pair of rows in the two tables, the on clause is evaluated. When it evaluates to true (i.e. not false and not NULL), then the pair passes the filter.

Note that there is no specification whatsoever on the condition. The most typical conditions are equality conditions on one or more columns. However, inequalities, function calls, and even subqueries are allowed.

The definition of outer joins is just a slight variation on the inner join definition. For outer joins, rows are output from one or both tables even when the on clause does not evaluate to true.

For inner joins, putting conditions in the on versus where is really a matter of style. For outer joins, some conditions may need to go in the on -- and others in the where.

score 0 · Answer 2 · answered Oct 12 '18 at 06:25

The join conditions can have whatever clauses you like.

The main purpose is to join one column of one table to a column in another table, but it can also be used to limit the rows you look at in the joined tables.

For example something like this is relatively common

select a1.address as postal, a2.address as street
from customer
join address a1 on a1.customerid=customer.id and a1.addresstype='postal'
join address a2 on a2.customerid=customer.id and a2.addresstype='street'

SQL Server - update join - setting specific values in on clause

2 Answers2