Which join i want to use to get rows from left table that not found in right table in SQL Server

Question

I have two tables has below

Table Left:

ID   | Visit No 
---  | --------
 1   |   1
 1   |   2
 2   |   1
 3   |   1

If Table Right has

ID  | Visit No | Place
--- | -------- | -----
 1  |  1       | Chennai

Then Results will be like

 ID  | Visit No 
---  | --------     
 1   |   2
 2   |   1
 3   |   1

If Table Right is Empty, Then the Result will be

ID   | Visit No 
---  | --------
 1   |   1
 1   |   2
 2   |   1
 3   |   1

Based on ID and Visit No. Is it possible to get output using joins in SQL Sever 2012.

Check out this answer: http://stackoverflow.com/questions/406294/left-join-and-left-outer-join-in-sql-server#406333 especially the graphics part, they're very useful for understanding the types of joins — evilpenguin, Aug 12 '16 at 12:10

score 1 · Accepted Answer · answered Aug 12 '16 at 12:16

1

SELECT left.[ID], left.[Visit No]
FROM LeftTable left
LEFT JOIN RightTable right
  ON left.[ID] = right.[ID]
  AND left.[Visit No] = right.[Visit No]
WHERE right.[ID] IS NULL

answered Aug 12 '16 at 12:16

Geert Immerzeel

564
4
21

Which join i want to use to get rows from left table that not found in right table in SQL Server

1 Answers1