1

Suppose you have 2 tables. Book(TITLE) and Subject(IDSUBJECT, title) where title in subject references to title in book. What's the query to find all the books that are not liked to a subject?

GMB
  • 216,147
  • 25
  • 84
  • 135
Tomoko
  • 21
  • 1
  • Have a look here: https://stackoverflow.com/questions/14253994/selecting-all-fields-except-only-one-field-in-mysql/14254068 – Tiago Mateus Jun 22 '20 at 22:36
  • 1
    Note that it would be unlikely to relate tables in this way; it's possible for two books to have the same title. – Strawberry Jun 23 '20 at 06:54

2 Answers2

0

Use not exists:

select b.*
from books b
where not exists (select 1 from subject s where s.title = b.title)
GMB
  • 216,147
  • 25
  • 84
  • 135
0

Try This:

SELECT 
     book.TITLE
FROM 
     Book AS book

LEFT JOIN Subject AS subj 

ON book.TITLE = subj.TITLE

WHERE 
subj.IDSUBJECT IS NULL;
Alekhya Reddy
  • 1
  • Why do you add this answer two years late? And this anti join syntax is considered less readable than a mere `NOT EXISTS` or `NOT IN`. clause. Why do you suggest using this over the already given answer? And then, please don't write code-only answers. Explain what the query does and how it solves the problem. – Thorsten Kettner Apr 28 '23 at 05:57