Get all records in Table B that don't exist in Table A

Question

Got this question from a job interview via skype. I managed to take a screenshot of the question.

I imagine it's the reverse of a RIGHT JOIN, but I can't figure it out.

Possible duplicate of https://stackoverflow.com/q/2686254/3404097 — philipxy, Apr 18 '18 at 03:11
Hi. Read & act on [ask], the downvote arrow mouseover text & hits googling 'stackexchange homework'. Always google many clear, concise & specific versions/phrasings of your question/problem/goal & read many answers. Add relevant keywords you discover to your searches. If you don't find an answer then post, using use one variant search for your title & keywords for your tags. Please [use text, not images/links, for text (including code, tables & ERDs)](https://meta.stackoverflow.com/q/285551/3404097). Use an image only for convenience to supplement text and/or for what can't be given in text. — philipxy, Apr 18 '18 at 03:19

score 2 · Answer 1 · answered Apr 15 '18 at 07:47

2

One simple way of doing this via an anti-join:

SELECT d.*
FROM departments d
LEFT JOIN students s
    ON d.DEPARTMENT_ID = s.DEPARTMENT_ID
WHERE
    s.DEPARTMENT_ID IS NULL;

We can also use an EXISTS query:

SELECT d.*
FROM departments d
WHERE NOT EXISTS (SELECT 1 FROM students s WHERE s.DEPARTMENT_ID = d.DEPARTMENT_ID);

answered Apr 15 '18 at 07:47

Tim Biegeleisen

Congratulations. You're hired. – Strawberry Apr 15 '18 at 08:18
@Strawberry I can't get a good job. The coding challenges always defeat me. – Tim Biegeleisen Apr 15 '18 at 08:20

Md Shahadat Hossain · Answer 2 · 2018-04-15T07:57:08.560

0

You can do this by a using a subquery. Here are two example of MySQL query. You can try this.

SELECT * 
FROM DEPARTMENT 
WHERE 
DEPERTMENT_ID NOT IN (
   SELECT DEPERTMENT_ID 
   FROM STUDENTS 
   GROUP BY DEPERTMENT_ID)

Another way using where not exists:

   SELECT d.*
    FROM departments d
    WHERE NOT EXISTS (
         SELECT 1 FROM students s WHERE s.DEPARTMENT_ID = d.DEPARTMENT_ID
    );

edited Apr 15 '18 at 07:57

answered Apr 15 '18 at 07:50

score 0 · Answer 3 · answered Apr 15 '18 at 07:54

0

Try this:

SELECT *
FROM department_table D
WHERE 
NOT EXISTS (SELECT NULL
            FROM student_table B
            WHERE A.department_id=B.department_id);

answered Apr 15 '18 at 07:54

cdaiga

3 Answers3