0

I have two tables student table and fee table:

First Table (Student):

std_id std_name
1 A
2 B
3 C
4 D
5 E
6 F

Second Table(Fee):

fid std_id amount
1 1 500
2 2 500
3 4 500

I want to fetch rows of those students from fee table who are not paid amount. In above example want to fetch the names of std_id 3,5 and 6.

1 Answer 1

1

Common anti-semijoin.

Either

SELECT *
FROM table1
LEFT JOIN table2 USING (std_id)
WHERE t2.std_id IS NULL

or

SELECT *
FROM table1
WHERE NOT EXISTS ( SELECT NULL
                   FROM table2
                   WHERE table1.std_id = table2.std_id )

or

SELECT *
FROM table1
WHERE std_id NOT IN ( SELECT std_id 
                      FROM table2 )

First 2 variants have approximately the same effectiveness (depends on data statistic and indices presence, sometimes these queries may even produce the same execution plan). Last variant has least effectiveness approximately always.

3
  • Sir Akina! i have changed question, please guide now Commented Jul 26, 2022 at 16:11
  • No, once your question has been answered, it's not good manners to change it. Commented Jul 26, 2022 at 16:31
  • You can always post another question. Commented Jul 26, 2022 at 16:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.