CODEWITHSUNDEEP
sqlsubquery
n4mi
n4mi

Reputation: 21

Need help in nested sql queries

Please help me with this query

SELECT * 
FROM documents 
WHERE doc_id <> (SELECT doc_id 
                 FROM doc_submitted 
                 WHERE student_IDNUM = 131009685)

What I want is to get all documents that are not in the doc_submitted.

Upvotes: 0

Views: 41

Answers (4)

onedaywhen
onedaywhen

Reputation: 57053

It's known as an anti-join and like many relational operators there is no direct equivalent in SQL. Instead we must choose from a variety of workarounds e.g. here's one:

SELECT doc_id 
  FROM documents 
EXCEPT
SELECT doc_id 
  FROM doc_submitted 
 WHERE student_IDNUM = 131009685

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270431

I generally recommend NOT EXISTS over NOT IN, because NOT IN returns zero rows if even one row in the subquery has a NULL value:

SELECT d.*
FROM documents d
WHERE NOT EXISTS (SELECT 1
                  FROM doc_submitted ds
                  WHERE ds.doc_id = d.docid AND ds.student_IDNUM = 131009685
                 )

Upvotes: 2

Unnikrishnan R
Unnikrishnan R

Reputation: 5031

You can use left join also for omitting the unwanted records.

SELECT a.* 
FROM documents a
  LEFT JOIN  doc_submitted b ON   a.doc_id=b.doc_id AND b.student_IDNUM = 131009685 
WHERE b. doc_id IS null

Upvotes: 1

Sandip - Frontend Developer
Sandip - Frontend Developer

Reputation: 14679

Try NOT IN:

NOT IN will Omit records from main query which are available in sub query.

SELECT * FROM documents WHERE doc_id NOT IN 
(
     SELECT doc_id FROM doc_submitted WHERE student_IDNUM = 131009685
)

Upvotes: 0

Related Questions