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.

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

Solarwinds

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.

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

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.

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

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 which is next day GTM+2.5.

Reference:  Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
,
Previous Post
SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Efficient Query Writing Strategy – Day 4 of 35
Next Post
SQL SERVER – Row Constructors – Day 6 of 35

Related Posts

135 Comments. Leave new

  • Srikanth Nallamothu
    August 9, 2011 12:20 pm

    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

    Reply
  • 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

    Reply
  • Vinit Prajapati
    August 16, 2011 4:31 pm

    Option 4

    I am from India

    Reply
  • 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

    Reply
  • 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)

    Reply
  • answer Option: 4

    Raghavendra (USA)

    Reply
  • Namburi Zithendra
    September 6, 2011 12:32 pm

    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

    India

    Reply
  • 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

    Reply
  • Krishnat Patil
    July 18, 2012 6:58 pm

    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

    Reply
  • 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

    Reply
  • 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!

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

    Reply
  • write a query to list all the emps whose names starts with S or K without using or operator.

    Reply
  • 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

    Reply

Leave a Reply

Menu