CODEWITHSUNDEEP
sqlmysql
user23167
user23167

Reputation: 487

SQL query to select unreferenced rows

I'm having a brain-dead moment... I have two tables described by:

CREATE TABLE table_a (
    id             INTEGER          PRIMARY KEY AUTO_INCREMENT,
    name           VARCHAR(255)     NOT NULL
    UNIQUE (name))

CREATE TABLE table_b (
    id             INTEGER          PRIMARY KEY AUTO_INCREMENT,
    a_key          INTEGER          NOT NULL,
    other_stuff    VARCHAR(255)     NOT NULL,
    FOREIGN KEY(a_key)  REFERENCES table_a(id)
         ON DELETE CASCADE)

How can I select all rows from table_a that do not have an entry in table_b.a_key?

Upvotes: 5

Views: 2901

Answers (5)

Bigballs
Bigballs

Reputation: 3809

select a.*
from table_a a
where a.id not in (select b.a_key from table_b b where b.a_key = a.id)

Upvotes: 0

LukeH
LukeH

Reputation: 269408

SELECT table_a.*
FROM table_a
    LEFT JOIN table_b
        ON table_a.id = table_b.a_key
WHERE table_b.id IS NULL

Upvotes: 12

edosoft
edosoft

Reputation: 17271

If it is SQL server 2005+ you could try EXCEPT

SELECT id
FROM table_a
EXCEPT
SELECT a_key
FROM table_b;

Upvotes: 1

Philip Reynolds
Philip Reynolds

Reputation: 9392

SELECT 
table_a.* 
FROM table_a 
LEFT JOIN table_b 
ON table_a.id = table_b.a_key 
WHERE b.id IS NULL;

This does a JOIN on table_a and table_b and where the JOIN finds no join result for table_b, it prints the corresponding row entry for table_a.

Upvotes: 4

Welbog
Welbog

Reputation: 60418

Naively, you can use a NOT EXISTS subquery:

SELECT A.*
FROM table_a A
WHERE NOT EXISTS (
  SELECT 1
  FROM table_b B
  WHERE B.a_key = A.id
)

You can also try an outer join. But they'll usually optimize to the same query internally.

Upvotes: 3

Related Questions