There are two tables table1 and table2 i want to join these tables and expect to retrieve rows which are not equal to table1 as there are rows which are in the both tables.
Table1
ID NAME Dept
1 B Finance
2 R HR
3 B CDU
Table2
ID PASSPORT
1 Yes
2 No
it is not working
SELECT table1.ID, table1.NAME, table1.Dept FROM TABLE1 INNER JOIN TABLE2 ON
TABLE1.ID != TABLE2.ID
Expected Result
ID NAME DEPT
3 B CDU
Use a left join to and where clause to grab the values that are only in table1
select
table1.id,
table1.name,
table1.Dept
from
table1
left join table2 on table2.id = table1.id
where
table2.id is NULL;
Output for the above Query:
A left join will insert a null value if the record from the second table cannot be found. So add in the where clause to grab all results that could not be found in the second table.
Here is a better description of using a left join https://www.w3schools.com/sql/sql_join_left.asp
Try this query --
SELECT ID
,NAME
,Dept
FROM TABLE1
WHERE ID NOT IN (
SELECT DISTINCT ID
FROM Table2
WHERE ID IS NOT NULL
);
WHERE ID IS NOT NULL is unnecessary on since it is likely ID is a primary key and would always have a value.
NOT IN will fail when sub-query returns any NULL value... at least in Sql Server