A SQL query that will return only orphaned records

Posted on 2011-05-02
Last Modified: 2012-05-11
Hello, I'm looking for a query that will return me table records that are orphaned.  Here is an example of what I'm looking for:

Table 1:  Authors table
author_name, etc.

Table 2:  Book table
book_name, etc

There is a one-to-one relationship between them.  Every author has a book_id that they are linked to.  I want a query that I can run on the book table that returns me all the books that have not been assigned to an author.

Question by:parlays

    Author Comment

    I was trying to run a query similar to this, I think it works but am not sure:
    select book_id from book where (select book_id from author where author.book_id = book.book_id) IS NULL;

    LVL 40

    Expert Comment

    try this.
    select b.*
      from Book as b
      left join Authors as a on b.book_id = a.book_id
     where a.book_id is null

    Open in new window

    LVL 40

    Accepted Solution

    or this.
    select *
      from Book as b
     where not exists (select 1 from authors as a where b.book_id = a.book_id);

    Open in new window


    Author Closing Comment


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL ( several years ago, it seemed like now was a good time to updat…
    This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
    The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
    This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now