I am unsure: Is this an anti-join?

Question

I am working on the first problem of the famous SQLzoos and am working on the using Null section: http://sqlzoo.net/wiki/Using_Null

The question is:

List the teachers who have NULL for their department.

The corresponding SQL query would be:

SELECT t.name
  FROM teacher t
WHERE t.dept IS NULL

Is this a type of anti-join? Specifically, is this a left-anti-join?

Answer 1

This isn't a join at all.

The statement is filtering only records for teachers who don't have an assigned department.

Set Difference

The set difference of teachers and departments, teacher \ department would be a kind of "anti-join"

SELECT
t.name
FROM teacher t
LEFT JOIN department d ON d.id = t.dept_id
WHERE d.id IS NULL

At first glance, this statement does what your statement does, if the foreign key reference was enforced, it would guarantee to do exactly that. However, one use for this statement would be to retrieve teachers who are assigned to departments that have since been deleted (e.g. if the English Lit Dept. & English as 2nd Lang Dept. were reorganized as the English Dept.)

Symmetric Difference

Another "anti-join" would be the symmetric difference, which selects elements from both sets ONLY if they cannot be joined, i.e

(teacher \ department) U (department \ teacher)

I can't think of a motivating example using teachers and departments, but one way to write the symmetric difference on databases that support the FULL OUTER JOIN would be:

SELECT
t.name
FROM teacher t
FULL OUTER JOIN department d ON d.id = t.dept_id
WHERE d.id IS NULL OR t.id IS NULL

For MySQL, this statement would have to be written as the union of two statements.

SELECT
t.name teacher_name, d.name department_name
FROM teacher t
LEFT JOIN department d ON d.id = t.dept_id
WHERE d.id IS NULL

UNION ALL

SELECT
t.name teacher_name, d.name department_name
FROM teacher t
LEFT JOIN department d ON d.id = t.dept_id
WHERE t.id IS NULL

Looking through one of my projects, I found this one use of symmetric difference:

Context:

I have three tables: users, users_gameplay_summary, users_transactions_summary. I needed to email those users who created their accounts in the past 7 days AND one of the following have transacted but have not played or played but have not transacted.

To get the list, I have this query (note, this was written for Postgresql, and won't work on MySQL, but it illustrates the symmetric difference use case):

SELECT 
COALESCE(g.user_id, t.user_id) user_id
FROM users_gameplay_summary g
FULL OUTER JOIN users_transactions_summary t ON t.user_id = g.user_id

WHERE COALESCE(g.user_id, t.user_id) IN (
          SELECT user_id 
          FROM users 
          WHERE created_at > CURRENT_DATE - '7 day'::interval)
  AND (g.user_id IS NULL OR t.user_id IS NULL)

Answer 2

Not exactly, your not actually joining anything now, in the case of a left anti join you would have access to the department name as well. (although it would be NULL)

Your sql code would be a correct answer for the question you gave though.

A left anti join would be:

SELECT t.name
FROM teacher t
LEFT JOIN dept d ON d.id = t.dept
WHERE d.id IS NULL

Answer 3

To solve this problem of listing teachers without assigned departments, you don't need a JOIN between teacher and dept tables.

dept table is basically a dictionary table that you join to, to translate ids to corresponding names.

teacher table has a dept column which normally could have a FOREIGN KEY constraint to id column in dept table.

---

Your query is not an ANTI-JOIN. This is a simple projection and selection query using one table.

SELECT t.name
  FROM teacher t
WHERE t.dept IS NULL

For an ANTI-JOIN you would at least need a JOIN operation between more than one table at first.

Normally an ANTI-JOIN could look like:

Using LEFT JOIN

SELECT *
FROM table1 t1
LEFT JOIN table2 t2
  ON t1.join_column = t2.join_column
WHERE t2.join_column IS NULL

Using NOT EXISTS

SELECT *
FROM table1 t1
WHERE NOT EXISTS ( 
  SELECT 1
  FROM table2 t2
  WHERE t1.join_column = t2.join_column
  )