SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Finding un-matching Records – Day 5 of 35

Answer simple quiz at the end of the blog post and -
Every day one winner from India will get Joes 2 Pros Volume 1.
Every day one winner from United States will get Joes 2 Pros Volume 1.

Finding un-matching Records

Often time we want to find records in one table that have no matching key in another table. This is common for things like finding products that have never sold, or students who did not re-enroll. Something we were expecting is missing. Records in one table were expecting some related activity in another table and did not find them. There are many ways to find these records.

Basic Subquery

We have probably all heard that subqueries should be avoided if there is a better solution. Often times basic subqueries are used where a simple “Unmatched Records Query” could have been used.

Let’s start off with the subquery example. Looking at the Location table in the figure below we see all the data. In fact, this table does not allow nulls for the LocationID field. Looking further, there are no nulls anywhere in the Location table. So does this mean that all locations have at least one employee? It’s possible that some of these locations might be new and have not employees working there yet.

How can we find the location with no employees. We can use a subquery. For example we can use the following query to find all locations of the JProCo database that have now employee working there.

SELECT *
FROM Location
WHERE LocationID
NOT IN (SELECT DISTINCT LocationID
FROM Employee
WHERE LocationID IS NOT NULL)

Unmatched Records Queries

If you wanted to find all locations with no employees, you could run an “Unmatched Records Query”. Let’s build this piece by piece. In this case, we have to join the Location table with the Employee table to determine the location that has no employees. What type of join will tell us this? Since nulls don’t map through a join, the INNER JOIN drops the record from the result set and we won’t see Chicago. The outer join will show both the matches and the unmatched records, so we see every location. In the figure below we get all location even if there are no employees.

Notice Seattle is listed many times but Chicago is listed once with no employees found. A NULL appears in the fields from the Employee table for Chicago. With the Location table on the left and the NULL on the right, we have part of an unmatched records query. To find just the records that don’t match, we look for null records on the table that the outer join does not favor. In this case, it’s the Employee table.

The outer join will show us the unmatched records with null location details if you set the WHERE clause to look for nulls on a field in the non-dominant table. Unmatched record queries use SQL to return a result set displaying only the unmatched records between the two tables.

When our query criterion specifies NULL, only Chicago shows up in our result set. By doing a LEFT OUTER JOIN and using a NULL value from the Employee table (or “RIGHT” table) as our search condition criteria, our unmatched records query shows us one record.

Note: If you want to setup the sample JProCo database on your system you can watch this video.

Question 5

Q 5) Which of the following queries will show all location that have no employees?

  1. SELECT P.EmployeeID, T.[Name]
    FROM Employee P LEFT OUTER JOIN Location T
    ON T.TerritoryID = P.TerritoryID
    WHERE T.TerritoryID IS NULL
  2. SELECT P.EmployeeID, T.[Name]
    FROM Employee P LEFT OUTER JOIN Location T
    ON T.TerritoryID = P.TerritoryID
    WHERE P.TerritoryID IS NULL
  3. SELECT P.EmployeeID, T.[Name]
    FROM Employee P RIGHT OUTER JOIN Location T
    ON T.TerritoryID = P.TerritoryID
    WHERE T.TerritoryID IS NULL
  4. SELECT P.EmployeeID, T.[Name]
    FROM Employee P RIGHT OUTER JOIN Location T
    ON T.TerritoryID = P.TerritoryID
    WHERE P.TerritoryID IS NULL

Please post your answer in comment section to win Joes 2 Pros books.

Rules:

Please leave your answer in comment section below with correct option, explanation and your country of resident.
Every day one winner will be announced from United States.
Every day one winner will be announced from India.
A valid answer must contain country of residence of answerer.
Please check my facebook page for winners name and correct answer.
Winner from United States will get Joes 2 Pros Volume 1.
Winner from India will get Joes 2 Pros Volume 1.
The contest is open till next blog post shows up at http://blog.sqlauthority.com which is next day GTM+2.5.

Reference:  Pinal Dave (http://blog.SQLAuthority.com)

About these ads

139 thoughts on “SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Finding un-matching Records – Day 5 of 35

  1. Q 5) Which of the following queries will show all location that have no employees?

    4. SELECT P.EmployeeID, T.[Name]
    FROM Employee P RIGHT OUTER JOIN Location T
    ON T.TerritoryID = P.TerritoryID
    WHERE P.TerritoryID IS NULL

    Leo Pius
    USA

  2. The correct answer is No. 4, as the RIGHT OUTER JOIN using a NULL value from the LEFT table as the search condition criteria will produce just the locations with no employees.

    Rene Castro
    El Salvador

  3. Correct Answer is Option 4:
    – Gets all the employees that have no location assigned to
    1.SELECT P.EmployeeID, T.[Name]
    FROM Employee P LEFT OUTER JOIN Location T
    ON T.TerritoryID = P.TerritoryID
    WHERE T.TerritoryID IS NULL

    – Gets all the employees that have no location assigned to
    2.SELECT P.EmployeeID, T.[Name]
    FROM Employee P LEFT OUTER JOIN Location T
    ON T.TerritoryID = P.TerritoryID
    WHERE P.TerritoryID IS NULL

    – This query doesn’t return any data becuase TerritoryID won’t be NULL (if it is a Primary Key)
    3.SELECT P.EmployeeID, T.[Name]
    FROM Employee P RIGHT OUTER JOIN Location T
    ON T.TerritoryID = P.TerritoryID
    WHERE T.TerritoryID IS NULL

    – Gets all locations that have no employees
    4.SELECT P.EmployeeID, T.[Name]
    FROM Employee P RIGHT OUTER JOIN Location T
    ON T.TerritoryID = P.TerritoryID
    WHERE P.TerritoryID IS NULL

    Country – INDIA

  4. Answer 4 is right. As Employee is a child Table and Location is Main Table, so Location must have data with Employee. With Employee on Left Side will not give correct data so have to use opposite of Left Join that is Right Join.

    I am from New Delhi, India

  5. Correct Answer is option : 3

    SELECT P.EmployeeID, T.[Name]
    FROM Employee P RIGHT OUTER JOIN Location T
    ON T.TerritoryID = P.TerritoryID
    WHERE T.TerritoryID IS NULL

    Thanks,
    Dhruval Shah, Ahmedabad, India

  6. Correct Answer is option : 4

    SELECT P.EmployeeID, T.[Name]
    FROM Employee P RIGHT OUTER JOIN Location T
    ON T.TerritoryID = P.TerritoryID
    WHERE P.TerritoryID IS NULL

    Thanks,
    Dhruval Shah, Ahmedabad, India

  7. Correct Answer is # 4.

    4. SELECT P.EmployeeID, T.[Name]
    FROM Employee P RIGHT OUTER JOIN Location T
    ON T.TerritoryID = P.TerritoryID
    WHERE P.TerritoryID IS NULL;

    Here, RIGHT OUTER JOIN using a NULL value from the LEFT (Employee) table as the search condition criteria will produce just the locations with no employees.

    Gopalakrishnan Arthanarisamy
    Bangalore, India

  8. Right Answer is Option 4

    4. SELECT P.EmployeeID, T.[Name]
    FROM Employee P RIGHT OUTER JOIN Location T
    ON T.TerritoryID = P.TerritoryID
    WHERE P.TerritoryID IS NULL

    Neelesh Jain
    India

  9. whoever has read following link, wouldn’t have problem in answering this question.

    http://blog.sqlauthority.com/2009/04/13/sql-server-introduction-to-joins-basic-of-joins/

    Actually we are focused on Location table as we need location so we are going to use RIGHT OUTER JOIN in this case, if we will use RIGHT OUTER JOIN, we will get all records from LOCATION table and It will return all employee with respected location. if any records of LOCATION table won’t have employee, it will come with NULL in EMPLOYEEID and of course in TerritoryID too. so we are going to get all LOCATION which has no matching record in EMPLOYEE table with condition “P.TerritoryID IS NULL”. so our choice should on Option # 4.

    SELECT P.EmployeeID, T.[Name]
    FROM Employee P RIGHT OUTER JOIN Location T
    ON T.TerritoryID = P.TerritoryID
    WHERE P.TerritoryID IS NULL

    Ritesh Shah(India)

  10. Question 5
    Ans : 4- SELECT P.EmployeeID, T.[Name]
    FROM Employee P RIGHT OUTER JOIN Location T
    ON T.TerritoryID = P.TerritoryID
    WHERE P.TerritoryID IS NULL

    Aditya
    Chennai, INDIA

  11. The Correct answer is 4.

    4.SELECT P.EmployeeID, T.[Name]
    FROM Employee P RIGHT OUTER JOIN Location T
    ON T.TerritoryID = P.TerritoryID
    WHERE P.TerritoryID IS NULL

    Ravi
    Bangalore – India

  12. The answer is
    4. SELECT P.EmployeeID, T.[Name]
    FROM Employee P RIGHT OUTER JOIN Location T
    ON T.TerritoryID = P.TerritoryID
    WHERE P.TerritoryID IS NULL

    Country :India[Ahmedabad]

  13. Corrent Ans is Option 4:

    SELECT P.EmployeeID, T.[Name]
    FROM Employee P RIGHT OUTER JOIN Location T
    ON T.TerritoryID = P.TerritoryID
    WHERE P.TerritoryID IS NULL

    Pratik Raval
    India

  14. Answer:

    4. SELECT P.EmployeeID, T.[Name]
    FROM Employee P RIGHT OUTER JOIN Location T
    ON T.TerritoryID = P.TerritoryID
    WHERE P.TerritoryID IS NULL

    Lalit

    India

  15. Correct Answer is Option 4:

    SELECT P.EmployeeID, T.[Name]
    FROM Employee P RIGHT OUTER JOIN Location T
    ON T.TerritoryID = P.TerritoryID
    WHERE P.TerritoryID IS NULL

    India

  16. The correct answer is 4.
    Right outer join gets all matching records from both Employee and Location table based on territory id and non matching records from Location table and again there is filter based on territory id = null which will be able to fetch all locations that have no employees.

  17. Question 5

    Q 5) Which of the following queries will show all location that have no employees?

    Answer is 2 and 4
    because we are looking for locations that have no employees below query uses right outer join and Look at the where condition we have specified and looking for TerritoryId in Employee table i.e P.TerritoryId as null.

    4. SELECT P.EmployeeID, T.[Name]
    FROM Employee P RIGHT OUTER JOIN Location T
    ON T.TerritoryID = P.TerritoryID
    WHERE P.TerritoryID IS NULL

    below query uses left outer join and mentioned P.TerritoryID IS NULL
    in where clause.gives correct result.

    2. SELECT P.EmployeeID, T.[Name]
    FROM Employee P LEFT OUTER JOIN Location T
    ON T.TerritoryID = P.TerritoryID
    WHERE P.TerritoryID IS NULL

    Following are wrong :
    below query uses T.TerritoryID IS NULL T—>Location in where clause,
    Looks for the TerritoryID as null in location table, If that table don’t have null for the TerritoryID gives zero records.

    1. SELECT P.EmployeeID, T.[Name]
    FROM Employee P LEFT OUTER JOIN Location T
    ON T.TerritoryID = P.TerritoryID
    WHERE T.TerritoryID IS NULL

    Blow query uses RIGHT OUTER JOIN with Location and looking for TerritoryID IS NULL in Location table which gives wrong result.

    3. SELECT P.EmployeeID, T.[Name]
    FROM Employee P RIGHT OUTER JOIN Location T
    ON T.TerritoryID = P.TerritoryID
    WHERE T.TerritoryID IS NULL

    Country:India

  18. Answer for question 5:
    Above all option will give error, since the above all option join with common column TerritoryID which is not there in employee table nor in location table

    INDIA
    Brijesh

  19. The correct answer is option 4. that is

    SELECT P.EmployeeID, T.[Name]
    FROM Employee P RIGHT OUTER JOIN Location T
    ON T.TerritoryID = P.TerritoryID
    WHERE P.TerritoryID IS NULL

    Here we have used right outer join it means it will bring all records from Location Table and will fatch NULL wherever TerritoryID is NULL for Employee table. So we can match records from Employee table which have TerritoryID as null.

    From
    INDIA
    AHMEDABAD

  20. The correct option is 4.

    SELECT P.EmployeeID, T.[Name]
    FROM Employee P RIGHT OUTER JOIN Location T
    ON T.TerritoryID = P.TerritoryID
    WHERE P.TerritoryID IS NULL

    Gets all locations that have no employees. The where condition will list all the territoryID from right table(i.e. Employee Table) which contains null values

    By
    Yasodha.N (India)

  21. Option No. 4 is correct:
    SELECT P.EmployeeID, T.[Name]
    FROM Employee P RIGHT OUTER JOIN Location T
    ON T.TerritoryID = P.TerritoryID
    WHERE P.TerritoryID IS NULL

    Country- India

  22. OPTION 2 & 4 is Right

    2. SELECT P.EmployeeID, T.[Name]
    FROM Employee P LEFT OUTER JOIN Location T
    ON T.TerritoryID = P.TerritoryID
    WHERE P.TerritoryID IS NULL

    4..SELECT P.EmployeeID, T.[Name]
    FROM Employee P RIGHT OUTER JOIN Location T
    ON T.TerritoryID = P.TerritoryID
    WHERE P.TerritoryID IS NULL

  23. Ans:
    4. SELECT P.EmployeeID, T.[Name]
    FROM Employee P RIGHT OUTER JOIN Location T
    ON T.TerritoryID = P.TerritoryID
    WHERE P.TerritoryID IS NULL

    This queries will show all location that have no employees.

    Partha
    India

  24. I think option 4 is the correct answer

    SELECT P.EmployeeID, T.[Name]
    FROM Employee P RIGHT OUTER JOIN Location T
    ON T.TerritoryID = P.TerritoryID
    WHERE P.TerritoryID IS NULL

    Country- India

  25. Correct answer is option 4.

    4. SELECT P.EmployeeID, T.[Name]
    FROM Employee P RIGHT OUTER JOIN Location T
    ON T.TerritoryID = P.TerritoryID
    WHERE P.TerritoryID IS NULL

    Country – India

  26. Correct option is 4.

    SELECT P.EmployeeID, T.[Name]
    FROM Employee P RIGHT OUTER JOIN Location T
    ON T.TerritoryID = P.TerritoryID
    WHERE P.TerritoryID IS NULL

    India
    Shilpa Sharma

  27. 4. SELECT P.EmployeeID, T.[Name]
    FROM Employee P RIGHT OUTER JOIN Location T
    ON T.TerritoryID = P.TerritoryID
    WHERE P.TerritoryID IS NULL;

    Here right outer join with null give the value of Location which is not having employee

    Ghanshyam
    Bangalore, India

  28. The 1, 2 3 are wrong
    Bocs
    1. will give the employees list whose Territory ID not in Location table
    and employees whose territory ID is null in employee table

    2. will give the employees list whose territory ID is null in employee table

    3. will give the records which having territoryID is null in location table
    (If the location table permits null values to the TerritoryID column)

    So option 4 is the correct answer

    4. which will Give list of location which have no employees
    and If the location table permits null values to the TerritoryID column
    those rows also will include.

    Govindaraj P bangalore India.

  29. The correct option is 4.

    SELECT P.EmployeeID, T.[Name]
    FROM Employee P RIGHT OUTER JOIN Location T
    ON T.TerritoryID = P.TerritoryID
    WHERE P.TerritoryID IS NULL

    Gets all locations that have no employees. The where condition will list all the territoryID from right table(i.e. Employee Table) which contains null values

    From
    Malay Shah
    Ahmedabad (India)

  30. The correct option is 4.

    When we apply right join then the result is all row from right table which is location in our case and respective match records from the left table and null in case of no match.
    And applying the where cause of null (P.TerritoryID IS NULL) will only select result having employees with location null

    Karan
    India

  31. Option 4 is correct
    SELECT P.EmployeeID, T.[Name]
    FROM Employee P RIGHT OUTER JOIN Location T
    ON T.TerritoryID = P.TerritoryID
    WHERE P.TerritoryID IS NULL

    Country INDIA

  32. The answer is:
    SELECT P.EmployeeID, T.[Name]
    FROM Employee P RIGHT OUTER JOIN Location T
    ON T.TerritoryID = P.TerritoryID
    WHERE P.TerritoryID IS NULL

    First two options does not work because, it is LEFT OUTER JOIN. If we do this, in the first step itself it does not show the location which does not have employee.

    Thirst option does not work because, it is checking for ‘T.TerritoryID IS NULL’ which is can not be null.

    So the fourth option will meet the requirement.

    Country: India

  33. Hi Pinal,

    Correct Answer is Option 4

    SELECT P.EmployeeID, T.[Name]
    FROM Employee P RIGHT OUTER JOIN Location T
    ON T.TerritoryID = P.TerritoryID
    WHERE P.TerritoryID IS NULL

    Thanks,
    Fazal(India)

  34. Correct answer is option 4

    Correct Answer is Option 4

    SELECT P.EmployeeID, T.[Name]
    FROM Employee P RIGHT OUTER JOIN Location T
    ON T.TerritoryID = P.TerritoryID
    WHERE P.TerritoryID IS NULL

    Jankhana
    India

  35. Answer :

    4.SELECT P.EmployeeID, T.[Name]
    FROM Employee P RIGHT OUTER JOIN Location T
    ON T.TerritoryID = P.TerritoryID
    WHERE P.TerritoryID IS NULL

    Thanks
    Pawan

    Country: India

  36. Correct answer:(4)
    SELECT P.EmployeeID, T.[Name]
    FROM Employee P RIGHT OUTER JOIN Location T
    ON T.TerritoryID = P.TerritoryID
    WHERE P.TerritoryID IS NULL
    by kkmishra

  37. Answer is (4)
    SELECT P.EmployeeID, T.[Name]
    FROM Employee P RIGHT OUTER JOIN Location T
    ON T.TerritoryID = P.TerritoryID
    WHERE P.TerritoryID IS NULL
    by kkmjssate
    unnao(India)

  38. The Correct Answer is
    4.SELECT P.EmployeeID, T.[Name]
    FROM Employee P RIGHT OUTER JOIN Location T
    ON T.TerritoryID = P.TerritoryID
    WHERE P.TerritoryID IS NULL

    As Right Outer Join considers all the records from Right table which is Location and Matching Records from Left Table which is employees.
    Where condition will filter out data of employee table and
    Query retireves Locations which is not associated with Employee Table.

    Location: India

  39. The Correct answer is Option 4

    4.SELECT P.EmployeeID, T.[Name]
    FROM Employee P RIGHT OUTER JOIN Location T
    ON T.TerritoryID = P.TerritoryID
    WHERE P.TerritoryID IS NULL

    cheers,
    Prasad Yangamuni
    INDIA (Pune)

  40. Pingback: SQL SERVER – Win a Book a Day – Contest Rules – Day 0 of 35 Journey to SQLAuthority

  41. he correct option is 4.

    SELECT P.EmployeeID, T.[Name]
    FROM Employee P RIGHT OUTER JOIN Location T
    ON T.TerritoryID = P.TerritoryID
    WHERE P.TerritoryID IS NULL

    From
    Vishal India

  42. The Correct answer is 4.

    4.SELECT P.EmployeeID, T.[Name]
    FROM Employee P RIGHT OUTER JOIN Location T
    ON T.TerritoryID = P.TerritoryID
    WHERE P.TerritoryID IS NULL

    Somnath Desai

    India

  43. Hi ,

    I think this 4th option willl work fine only when there TerritoryId column is found in both tables we are joining,

    SELECT P.EmployeeID, T.[Name]
    FROM Employee P RIGHT OUTER JOIN Location T
    ON T.TerritoryID = P.TerritoryID
    WHERE P.TerritoryID IS NULL

  44. Correct Answer is Option 4 as we are looking for records where there are no employee in territory hence p.TerritoryID should be NULL and all the records are brought from Location table.

    Krunal K. Doshi
    INDIA

  45. Correct Answer is 4 i.e

    4. SELECT P.EmployeeID, T.[Name]
    FROM Employee P RIGHT OUTER JOIN Location T
    ON T.TerritoryID = P.TerritoryID
    WHERE P.TerritoryID IS NULL

    I’m from india

  46. The answer is No.4

    SELECT P.EmployeeID, T.[Name]
    FROM Employee P RIGHT OUTER JOIN Location T
    ON T.TerritoryID = P.TerritoryID
    WHERE P.TerritoryID IS NULL

    we are using right join here, that means all the records from Location table will come into output (whether they have matching record in employee or not), finally we check the employee table’s territory id for null to retrieve only those record which are not having the employee in that location.

    India

  47. Correct answer – 4

    SELECT P.EmployeeID, T.[Name]
    FROM Employee P RIGHT OUTER JOIN Location T
    ON T.TerritoryID = P.TerritoryID
    WHERE P.TerritoryID IS NULL

    Shekhar Gurav.
    Country – INDIA

  48. Correct Answer is Option 4

    SELECT P.EmployeeID, T.[Name]
    FROM Employee P RIGHT OUTER JOIN Location T
    ON T.TerritoryID = P.TerritoryID
    WHERE P.TerritoryID IS NULL

    Explanation : As in this query we have right outer join between Employee and Location Tables which will take all rows from location table irrespective of matching ‘TerritoryID’ rows from Employee Table. When the rows from Employee table doesn’t match with the ‘TerritoryID’ of Location Table then that row will be shown as NULL. And such rows are filtered by condition ‘P.TerritoryID IS NULL’. So this query will list all locations with no employees.

    Country – INDIA (Gujarat)

  49. Good One …

    Correct Answer:
    4. SELECT P.EmployeeID, T.[Name]
    FROM Employee P RIGHT OUTER JOIN Location T
    ON T.TerritoryID = P.TerritoryID
    WHERE P.TerritoryID IS NULL

    India (Bangalore).

  50. Correct Answer is 4 i.e

    4. SELECT P.EmployeeID, T.[Name]
    FROM Employee P RIGHT OUTER JOIN Location T
    ON T.TerritoryID = P.TerritoryID
    WHERE P.TerritoryID IS NULL

    Right outer join forces query statement to display records of right table with records of another table having some matching criteria.
    So we want to find all locations that’s why
    “Employee P RIGHT OUTER JOIN Location T” this statement is correct & next statement WHERE P.TerritoryID IS NULL gives result with all locations having no any Employee.

    I’m from india (Pune)
    Sunny Jagtap.

  51. Answer is: 4

    4. SELECT P.EmployeeID, T.[Name]
    FROM Employee P RIGHT OUTER JOIN Location T
    ON T.TerritoryID = P.TerritoryID
    WHERE P.TerritoryID IS NULL

    Country : india

  52. The correct option is 4.

    SELECT P.EmployeeID, T.[Name]
    FROM Employee P RIGHT OUTER JOIN Location T
    ON T.TerritoryID = P.TerritoryID
    WHERE P.TerritoryID IS NULL

    Country: India

    Varun

  53. Answer: Option 4
    Because Employee table is Right Outer Join with Location Table and having a condition on territory Id from Employee table which is nothing but the Location table is left outer join with Employee table and territory is null in employee table.

    India

  54. Answer: Option 4

    SELECT P.EmployeeID, T.[Name]
    FROM Employee P RIGHT OUTER JOIN Location T
    ON T.TerritoryID = P.TerritoryID
    WHERE P.TerritoryID IS NULL
    Country: India(sikha)

  55. Correct answer :
    Option 4:
    SELECT P.EmployeeID, T.[Name]
    FROM Employee P RIGHT OUTER JOIN Location T
    ON T.TerritoryID = P.TerritoryID
    WHERE P.TerritoryID IS NULL

    Thanks,
    Mandar
    Mumbai,INDIA

  56. Option 4 is the correct Answer

    4.SELECT P.EmployeeID, T.[Name]
    FROM Employee P RIGHT OUTER JOIN Location T
    ON T.TerritoryID = P.TerritoryID
    WHERE P.TerritoryID IS NULL

    Country: INDIA

  57. Question 5

    Q 5) Which of the following queries will show all location that have no employees?

    Answer:

    4.SELECT P.EmployeeID, T.[Name]
    FROM Employee P RIGHT OUTER JOIN Location T
    ON T.TerritoryID = P.TerritoryID
    WHERE P.TerritoryID IS NULL

    Answerer: Sivaprasad S

    Country: India

  58. The correct answer is:

    4.SELECT P.EmployeeID, T.[Name]
    FROM Employee P RIGHT OUTER JOIN Location T
    ON T.TerritoryID = P.TerritoryID
    WHERE P.TerritoryID IS NULL

    Country: United States

  59. Hi Pinal Dev,

    The correct answer is Option 4,

    SELECT P.EmployeeID, T.[Name]
    FROM Employee P RIGHT OUTER JOIN Location T
    ON T.TerritoryID = P.TerritoryID
    WHERE P.TerritoryID IS NULL

    The First two option is Left Outer join which is an invalid answer for the question as the Territory Id will never be shown up when we do a left outer join.

    And the 3 Option is trying to find where TerritoryId is null in the location table which is not possible as Location is the master table.

    P.Anish Shenoy
    INDIA, Bangalore Karnataka

  60. Correct answer is option 4

    SELECT P.EmployeeID, T.[Name]
    FROM Employee P RIGHT OUTER JOIN Location T
    ON T.TerritoryID = P.TerritoryID
    WHERE P.TerritoryID IS NULL

    RIGHT OUTER JOIN will give all the records from location table and matching records from employee table. Where condition will filter data from employee table and result will be locations which are not in employee table.

    Hiren Bavishi
    India

  61. In case of LEFT OUTER JOIN : It will result all the records from the left table irrespective of the records in the right table

    Simillarly, in RIGHT OUTER JOIN : it will result all the records from the right table irrespective of the records in the left table

    1. We have to take all the records from the Location, hence we can take left outer join having Location in the left or right outer join having Location in the right

    2. We have to show the locations having no employees, hence we have to put
    in where condition Employee.TerritoryID is null

    Above two conditions are satisfied only in Option – 4

    So, Correct Answer is Option 4

    SELECT P.EmployeeID, T.[Name]
    FROM Employee P RIGHT OUTER JOIN Location T
    ON T.TerritoryID = P.TerritoryID
    WHERE P.TerritoryID IS NULL

    Ishan Shah,
    Gandhinagar,
    India

  62. SELECT P.EmployeeID, T.[Name]
    FROM Employee P RIGHT OUTER JOIN Location T
    ON T.TerritoryID = P.TerritoryID
    WHERE P.TerritoryID IS NULL

    We have to use Right Outer Join to get data from Location table and where clause to check NULL value must be on Employee table.

    Chetan – USA

  63. Hi Pinal,

    Challenge:

    Which of the following queries will show all location that have no employees?

    Correct Answer:

    4.SELECT P.EmployeeID, T.[Name]
    FROM Employee P RIGHT OUTER JOIN Location T
    ON T.TerritoryID = P.TerritoryID
    WHERE P.TerritoryID IS NULL

    The RIGHT OUTER JOIN will get all of the Location Records. The Where clause will filter the results to show only the Location Records that don’t have employees (WHERE P.TerritoryID IS NULL).

    Country:
    United States

    Thanks for the knowledge!

    Regards,

    Bill Pepping

  64. Answer #4 –
    SELECT P.EmployeeID, T.[Name]
    FROM Employee P RIGHT OUTER JOIN Location T
    ON T.TerritoryID = P.TerritoryID
    WHERE P.TerritoryID IS NULL

    you need the RIGHT OUTER JOIN so you can get locations without employees, but you also need to be checking the employee table for the null TerritoryID. Checking the location table for the null TerritoryID won’t return anything since all locations will have an ID

  65. I believe the correct answer is 4.

    The first two are doing a left outer join which would get employees without location rather than locations without employees.

    The third one is close, but it is checking for nulls on the wrong table. The table is P for employee, not t for Territory. With number 4 you would see the rows that have nulls in the Employee table.

    I am from the United States.

  66. Correct answer is option # 4
    SELECT P.EmployeeID, T.[Name]
    FROM Employee P RIGHT OUTER JOIN Location T
    ON T.TerritoryID = P.TerritoryID
    WHERE P.TerritoryID IS NULL

    Nikhil Bhatt, Tampa, FL, USA

  67. The correct answer is 4.

    left out join list all rows from left hand table plus null.
    right out join list all rows from right hand table plus null.

    So in this case, we want to know null employees, right out join is used here.

    Country – Canada

  68. Option 4 is correct.

    4.SELECT P.EmployeeID, T.[Name]
    FROM Employee P RIGHT OUTER JOIN Location T
    ON T.TerritoryID = P.TerritoryID
    WHERE P.TerritoryID IS NULL

    Right outer join returns the records only from the right table excluding the subset of primary and secondary table records and NULL on employee table returns rows that have no employees.

    Country: USA

  69. I think the answer for this case is option 4

    SELECT P.EmployeeID, T.[Name]
    FROM Employee P RIGHT OUTER JOIN Location T
    ON T.TerritoryID = P.TerritoryID
    WHERE P.TerritoryID IS NULL

    Country : Chile

  70. Answer No 4 is correct:
    “SELECT P.EmployeeID, T.[Name]
    FROM Employee P RIGHT OUTER JOIN Location T
    ON T.TerritoryID = P.TerritoryID
    WHERE P.TerritoryID IS NULL”

    —————–
    Rajneesh Verma
    INDIA

  71. 4.SELECT P.EmployeeID, T.[Name]
    FROM Employee P RIGHT OUTER JOIN Location T
    ON T.TerritoryID = P.TerritoryID
    WHERE P.TerritoryID IS NULL

    Gordon Kane
    USA

  72. Option No 4 is correct:

    SELECT P.EmployeeID, T.[Name]
    FROM Employee P RIGHT OUTER JOIN Location T
    ON T.TerritoryID = P.TerritoryID
    WHERE P.TerritoryID IS NULL

    Country: India

  73. Answer is 4.

    SELECT P.EmployeeID, T.[Name]
    FROM Employee P RIGHT OUTER JOIN Location T
    ON T.TerritoryID = P.TerritoryID
    WHERE P.TerritoryID IS NULL

    David Brust
    USA

  74. 4. SELECT P.EmployeeID, T.[Name]
    FROM Employee P RIGHT OUTER JOIN Location T
    ON T.TerritoryID = P.TerritoryID
    WHERE P.TerritoryID IS NULL

    Ramdas
    Charlotte,USA

  75. Hello Pinal,

    The Correct option for the above question is : 4

    OPTION 4:
    SELECT P.EmployeeID, T.[Name]
    FROM Employee P RIGHT OUTER JOIN Location T
    ON T.TerritoryID = P.TerritoryID
    WHERE P.TerritoryID IS NULL

    Detailed Explanation :

    As in the above query we have right outer join between Employee and Location Tables which take’s all rows from Location table whether or not the matching ‘TerritoryID’ rows from the Employee Table.
    When the rows from Employee table doesn’t equate or matches with the ‘TerritoryID’ of Location Table then that row will be shown as NULL. And such rows are filtered by condition ‘P.TerritoryID IS NULL’. So this query will display all locations with no employees (i.e.) it selects only result having employees with location null.

    OPTION 1.This will return the employees record whose Territory ID not in Location table and employees whose territory ID is null in employee table

    OPTION 2.This will return the employees record whose territory ID is null in employee table.

    OPTION 3. This will return the records which are having the territoryID as null in location table (If the location table permits null values to the TerritoryID column).

    So option 4 is the correct option.

    DILIP KUMAR JENA
    COUNTRY : INDIA

  76. Answer is Option 4. SELECT P.EmployeeID, T.[Name]
    FROM Employee P RIGHT OUTER JOIN Location T
    ON T.TerritoryID = P.TerritoryID
    WHERE P.TerritoryID IS NULL

    (Sale, Nigeria)

  77. Option 4

    4.SELECT P.EmployeeID, T.[Name]
    FROM Employee P RIGHT OUTER JOIN Location T
    ON T.TerritoryID = P.TerritoryID
    WHERE P.TerritoryID IS NULL

    Explanation:- RIGHT OUTER JOIN will get all of the location records irrespective of Employee.

    COUNTRY:- INDIA

  78. Thank you, Pinal Dave, for this interesting article. I can use this other view on finding un-mathching records in a query I (re)wrote yesterday.

    I’ll continue reading your articles as they come.

    Many thanks all the way from the Netherlands (Europe).

    Ruud van de Beeten

  79. The correct option is #4
    SELECT P.EmployeeID, T.[Name]
    FROM Employee P RIGHT OUTER JOIN Location T
    ON T.TerritoryID = P.TerritoryID
    WHERE P.TerritoryID IS NULL

    It’s a bit of a tricky question as it’s not easy to de-cipher RIGHT JOIN as LEFT JOIN is usually simpler. I always would have written this query as NOT EXISTS.

    I’m from USA

  80. Hi Pinal sir,

    The correct answer for this question is option no. 4:

    4. SELECT P.EmployeeID, T.[Name]
    FROM Employee P RIGHT OUTER JOIN Location T
    ON T.TerritoryID = P.TerritoryID
    WHERE P.TerritoryID IS NULL

    Reason: We have to find out all the location that have no employee.
    Above query is fulfilling all the criteria to provide expected results.

    To have location with no employee we have to take a help of OUTER JOIN and from the result set of query we required Location id that is null (i.e. TerritoryID). We have RIGHT OUTER JOIN of Employee table with Location Table. Which will provide all the matching and mismatching rows from location table i.e all the location who have employee or not have a single employee.
    And in Where clause we are finding TerritoryID of Employee table which is null.
    And TerritoryID of Employee table is null only when there is no employee for respective location when used with right outer join.

    Following options are not correct because:

    Option 1:
    SELECT P.EmployeeID, T.[Name]
    FROM Employee P LEFT OUTER JOIN Location T
    ON T.TerritoryID = P.TerritoryID
    WHERE T.TerritoryID IS NULL

    TerritoryID will be not null for location table.

    Option 2.
    SELECT P.EmployeeID, T.[Name]
    FROM Employee P LEFT OUTER JOIN Location T
    ON T.TerritoryID = P.TerritoryID
    WHERE P.TerritoryID IS NULL

    Left outer join is used for employee table with location table and TerrirotyID will not be null for employee table.

    Option 3.
    SELECT P.EmployeeID, T.[Name]
    FROM Employee P RIGHT OUTER JOIN Location T
    ON T.TerritoryID = P.TerritoryID
    WHERE T.TerritoryID IS NULL

    TerritoryID will be not null for location table.

    Regard$
    Chirag Satasiya

  81. The right answer for this Question is:

    4. SELECT P.EmployeeID, T.[Name]
    FROM Employee P RIGHT OUTER JOIN Location T
    ON T.TerritoryID = P.TerritoryID
    WHERE P.TerritoryID IS NULL;

    The right outer join with null give the value of Location which is not having employee

    Paurav
    Surat, India

  82. ELECT P.EmployeeID, T.[Name]
    FROM Employee P RIGHT OUTER JOIN Location T
    ON T.TerritoryID = P.TerritoryID
    WHERE P.TerritoryID IS NULL

  83. 4. SELECT P.EmployeeID, T.[Name]
    FROM Employee P RIGHT OUTER JOIN Location T
    ON T.TerritoryID = P.TerritoryID
    WHERE P.TerritoryID IS NULL;

  84. Q 5) SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Finding un-matching Records – Day 5 of 35

    Since we want all Location (with no employees) we need to favor the Employee table so (1) and (4) are incorrect. We are not looking for null location making (3) incorrect too. The Location table on the right and a null employee on the left would find the unmatched record from the Location table so (4) is correct.

    Winner from USA: Naomi

    Winner from India: Ritesh Shah

    I thank you all for participating here. The permanent record of this update is posted on facebook page.

  85. The answer is 4:

    SELECT P.EmployeeID, T.[Name]
    FROM Employee P RIGHT OUTER JOIN Location T
    ON T.TerritoryID = P.TerritoryID
    WHERE P.TerritoryID IS NULL

    The right outer join gives us all of the matched and unmatched locations when joining to the employee table. Filtering for a null value in the employee table will give us all locations where no employees are at.

    Country of Residence: USA

  86. Pingback: SQL SERVER – The SQL Hands-On Guide for Beginners – Book Available for SQL Server Certification Journey to SQLAuthority

  87. 4.SELECT P.EmployeeID, T.[Name]
    FROM Employee P RIGHT OUTER JOIN Location T
    ON T.TerritoryID = P.TerritoryID
    WHERE P.TerritoryID IS NULL

  88. HELLO GUYS
    WHEN I WROTE THIS QUERY I WAS VERY EXITING

    REQIREMENT:-
    I WANT TO GET THE PATIENT DETAILS(;TABLE :- PATIENT)
    BUT THESE PATIENT SHOULD NOT IN ADMISSION(;TABLE :- ADT_ADMN)
    IN CASE THE ADMISSION PATIENTS ARE IN DISCHARGE(;TABLE :- ADT_DSCHRG) I WANT THE RESULT SET

    QUERY:-

    CREATE TABLE PATIENT(PATIENT_ID INT IDENTITY,
    FIRST_NAME VARCHAR(20),
    LAST_NAME VARCHAR(20),
    PATIENT_NAME AS(FIRST_NAME+’ ‘+LAST_NAME))
    GO

    CREATE TABLE ADT_ADMN(ADMN_ID INT IDENTITY,
    PATIENT_ID INT)

    GO

    CREATE TABLE ADT_DSCHRG(DSCHRG_ID INT IDENTITY,
    PATIENT_ID INT)

    GO

    SELECT *
    FROM PATIENT P
    LEFT JOIN ( SELECT AA.PATIENT_ID
    FROM DBO.ADT_ADMN AA
    LEFT JOIN DBO.ADT_DSCHRG AD ON AA.ADMN_ID = AD.ADMN_ID
    WHERE AD.DSCHRG_ID IS NULL
    )AS AAAD ON P.PATIENT_ID = AAAD.PATIENT_ID
    WHERE P.PATIENT_ID IS NULL

  89. Right is # 4

    SELECT P.EmployeeID, T.[Name]
    FROM Employee P RIGHT OUTER JOIN Location T
    ON T.TerritoryID = P.TerritoryID
    WHERE P.TerritoryID IS NULL

    Francisco,
    Miami

  90. Answer is #4:
    SELECT P.EmployeeID, T.[Name]
    FROM Employee P RIGHT OUTER JOIN Location T
    ON T.TerritoryID = P.TerritoryID
    WHERE P.TerritoryID IS NULL

    Thanks,
    Wayne

  91. answer is # 4:
    SELECT P.EmployeeID, T.[Name]
    FROM Employee P RIGHT OUTER JOIN Location T
    ON T.TerritoryID = P.TerritoryID
    WHERE P.TerritoryID IS NULL

    atul singh
    i’,m from india(mumbai)

  92. Hi Pinal,

    I really enjoy your blog and have found answers to many questions here.

    Regarding this post about finding unmatched records, why didn’t you use the (new to 2008) EXCEPT? It’s very useful and I have found it to be quicker than using a subquery.

    Kind Regards,
    Connie OI

  93. Option 4. SELECT P.EmployeeID, T.[Name]
    FROM Employee P RIGHT OUTER JOIN Location T
    ON T.TerritoryID = P.TerritoryID
    WHERE P.TerritoryID IS NULL

    INDIA

  94. Ans 4) SELECT P.EmployeeID, T.[Name]
    FROM Employee P RIGHT OUTER JOIN Location T
    ON T.TerritoryID = P.TerritoryID
    WHERE P.TerritoryID IS NULL

    Country : India

  95. Pingback: SQL SERVER – Query Hint – Contest Win Joes 2 Pros Combo (USD 198) – Day 1 of 5 « SQL Server Journey with SQL Authority

  96. Thanks Pinal – I just had to do this very thing yesterday and only knew to write a subquery. I just tried your above method and it worked very well. Thanks for the article!

  97. Please solve my query..
    write a query to display all the employees from emp table in which ‘i’ is not existing in their names.

  98. In database we are having table products with column prod_desc and prod_desc having below values
    10tea
    coffee20

    please write a query to disply the below output.

    prod_desc id name
    10tea 10 tea
    coffee20 20 coffee

  99. Pingback: SQL SERVER – Weekly Series – Memory Lane – #041 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s