Join two tables in SQL to rule out non matching values-CodePudding

My question is very basic, so does my knowledge in SQL. I'm getting very panicky when I see joins especially such confusions. I've two tables, table1 and table2. I want a list of IDs which are not present in table1 and present in table2.

table1

ID|NAME|ADDR|X|Y|Z

table2

ID|NAME|A|B|C|D|E|F|ETC

What I tried

select t1.* from table1 join table2 ON t1.id <> t2.id

What I want is like table1 with all the ID are not present in table1 but in table2. Thank you very much if you're reading this.

CodePudding user response：

Personally, I think a Not Exists query would most clearly reflect your requirements:

SELECT ID, NAME, ...
FROM table2
WHERE Not Exists
(
    SELECT 1 
    FROM table1
    WHERE table1.ID = table2.ID
)

CodePudding user response：

You can also use RIGHT OUTER JOIN or LEFT OUTER JOIN

For example using RIGHT OUTER JOIN:

SELECT t1.id AS ID1, t2.id AS ID2
FROM table1 t1
RIGHT OUTER JOIN table2 t2 ON t1.id = t2.id

In above query, ID1 will be NULL if it does not have data for id present in table2.