locked
I need to Fix LEFT OUTER JOIN RRS feed

  • Question

  • User1700504264 posted

    Hello

    this is my query 

    SelectCommand="SELECT [ID], [recordDate] FROM [Contact] LEFT JOIN [Employee] On Contact.ID=Employee.Contact WHERE @ID = 0 OR (@ID <> 0 AND ID=@ID) ORDER BY [ID]"

    it supposed to select all records from (Contacts table) that do not have a join with (Employees table) 

    but I noticed that it returns all records from (Contacts table) Exactly like LEFT JOIN

    may you please help me solve this?

    thank you

    Monday, April 9, 2018 6:00 PM

Answers

  • User475983607 posted

    I think you want this.

    SELECT [ID], [recordDate] 
    FROM [Contact] 
    LEFT JOIN [Employee] On Contact.ID=Employee.Contact
    WHERE Employee.Contact IS NULL
    ORDER BY [ID]

    Reference doc.

    https://www.w3schools.com/sql/sql_join_left.asp

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, April 9, 2018 6:16 PM
  • User475983607 posted

    Using WHERE NOT IN is not recommended as a NULL values in the IN will cause unexpected behavior. 

    You can use NOT EXISTS which is semantically the same as a LEFT JOIN.  Avoid using NOT IN.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, April 10, 2018 1:29 PM

All replies

  • User475983607 posted

    I think you want this.

    SELECT [ID], [recordDate] 
    FROM [Contact] 
    LEFT JOIN [Employee] On Contact.ID=Employee.Contact
    WHERE Employee.Contact IS NULL
    ORDER BY [ID]

    Reference doc.

    https://www.w3schools.com/sql/sql_join_left.asp

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, April 9, 2018 6:16 PM
  • User-492460945 posted

    Hello,

    This can also be implemented as follows:

    SELECT [ID], [recordDate] 
    FROM [Contact] WHERE [ID] NOT IN (SELECT [Contact] FROM [Employee])

    Thanks,

    RajeshV

    Tuesday, April 10, 2018 12:47 PM
  • User475983607 posted

    Using WHERE NOT IN is not recommended as a NULL values in the IN will cause unexpected behavior. 

    You can use NOT EXISTS which is semantically the same as a LEFT JOIN.  Avoid using NOT IN.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, April 10, 2018 1:29 PM
  • User-492460945 posted

    Using WHERE NOT IN is not recommended as a NULL values in the IN will cause unexpected behavior. 

    You can use NOT EXISTS which is semantically the same as a LEFT JOIN.  Avoid using NOT IN.

    yes you are right. NOT EXISTS is the perfect one I say.

    Tuesday, April 10, 2018 1:55 PM