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?
- SELECT P.EmployeeID, T.[Name]
FROM Employee P LEFT OUTER JOIN Location T
ON T.TerritoryID = P.TerritoryID
WHERE T.TerritoryID IS NULL - SELECT P.EmployeeID, T.[Name]
FROM Employee P LEFT OUTER JOIN Location T
ON T.TerritoryID = P.TerritoryID
WHERE P.TerritoryID IS NULL - SELECT P.EmployeeID, T.[Name]
FROM Employee P RIGHT OUTER JOIN Location T
ON T.TerritoryID = P.TerritoryID
WHERE T.TerritoryID IS NULL - 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 which is next day GTM+2.5.
Reference: Pinal Dave (https://blog.sqlauthority.com)
135 Comments. Leave new
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
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
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
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
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
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
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
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
Varinder Sandhu (India)
The answer is No.4
David Hunter
USA
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
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
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
Kulwant kumar
Delhi
The option 4 is the answer.
Country – USA.
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
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]
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
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
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
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.
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