Using SQL check to see if an ID in Table A exists in Table B. If that ID exists in Table B return co-CodePudding

I have a table call it Table A. Table A with an ID column ID1 associated with a row. That table looks like the following:

ID1
A1.0
B2.0
C3.0
A1.0
...

I have a second table call it Table B that has an ID and a corresponding value. That table looks like the following:

Value	ID
Apple	A1.0
Banana	B2.0
Peach	C3.0
...	...

I want to run a SQL query to see if the ID in Table A has a match to an ID in Table B. If there is a match return the Value in Table B as a new column in Table A. I was thinking of using some form of a CASE. The final table A should look like the following:

ID1	Value
A1.0	Apple
B2.0	Banana
C3.0	Peach
A1.0	Apple
...	...

CodePudding user response：

Assuming you want to skip any values in TableA that do not have a corresponding value in TableB, an INNER JOIN would suffice:

SELECT 
    TABLEA.ID1,
    TABLEB.Value
FROM
    TABLEA 
    INNER JOIN TABLEB ON TABLEA.ID1 = TableB.ID

If you do want to show values existing in TableA, that do not exist in TableB, change the INNER JOIN to a LEFT JOIN:

SELECT 
    TABLEA.ID1,
    TABLEB.Value
FROM
    TABLEA 
    LEFT JOIN TABLEB ON TABLEA.ID1 = TableB.ID

If you have a situation like prior to the edit, where multiple columns exist in tableA that can correspond with the ID in TableB, you could go with Andrew's suggestion of using OR in the join condition.