CODEWITHSUNDEEP
sqlsql-server
Tracer
Tracer

Reputation: 2736

SQL - How to select master records that have no detail records?

I have two tables (Table1 - master table, Table2 - detail table). How can I select (view) only those records from master table that have no records in detail table? What SQL statement can I use for this? I'm using MS SQL Server 2012.

Edit: Table definition

Table1 - ID (PK) Table2 - ID (PK), Table1ID (FK)

Upvotes: 2

Views: 3550

Answers (5)

Pரதீப்
Pரதீப்

Reputation: 93764

Use NOT IN Operator

SELECT *
    FROM   Table1
    WHERE  ID NOT IN(SELECT Table1ID
                           FROM   Table2 )

Upvotes: 6

Mithrandir
Mithrandir

Reputation: 25397

How about using an exclusive (left) outer join?

 SELECT 
       master.* 
 FROM 
       master LEFT OUTER JOIN details 
 ON    master.ID = details.masterID         
 WHERE details.ID IS NULL;

Have a look here for a more detailed description why this query is a solution to this kind of problem.

Upvotes: 1

Dmitry Grekov
Dmitry Grekov

Reputation: 688

I prefer using left joins in such cases:

select tp.*
from   table_parent tp
       left join table_child tc on tc.parent_id = tp.id
where  tc.parent_id is null

Upvotes: 0

userxxxso
userxxxso

Reputation: 165

this will help you greatly in answering your question:

http://dev.mysql.com/doc/refman/5.0/en/exists-and-not-exists-subqueries.html

SELECT column1 FROM t1 WHERE NOT EXISTS (SELECT * FROM t2);

Upvotes: -1

Tim Schmelter
Tim Schmelter

Reputation: 460238

I would use NOT EXISTS since it's clear, efficient and has no issues with nullable columns.

For example (MasterID is the PK/FK):

SELECT master.*
FROM dbo.Table1 master
WHERE NOT EXISTS
(
    SELECT 1 FROM Table2 detail
    WHERE detail.MasterID = master.MasterID
)

But you have other options: http://sqlperformance.com/2012/12/t-sql-queries/left-anti-semi-join

Upvotes: 5

Related Questions