Medical Center database
Non primary-foreign key JOINs
Non-equi JOINs
Non-equi self JOINs
19. NON-EQUI self JOINs
Summary

Instruction

Great! The non-equi JOIN comes in handy especially when you have to join a table with itself. For example you can select all the items from a table and pair them, either in unique or non-unique pairs.

Suppose you have the items table, and you want to find all possible combinations of all item pairs for a special promotion. You would like to show all possible pairs, excluding only the case when a given item matches itself (that is, the pair consists of two copies of the same item):

SELECT
  item1.name,
  item2.name
FROM items item1
JOIN items item2
  ON item1.id != item2.id

Great! Everything works... However, with this query, we don't get unique pairs - because the result also includes pairs in reverse order. What about unique pairs? Well, in that case, we have to change the inequality operator to the less-than operator (<):

SELECT
  item1.name,
  item2.name
FROM items item1
JOIN items item2
  ON item1.id < item2.id

Why? Suppose we have only three items in the items table. The item with the smallest ID (id = 1) can be listed as the first element of two pairs: 1 & 2 and 1 & 3. But because of the less-than operator, the item with id = 2 can only be listed together with item 3. And the item with id = 3 is never listed as the first member of a pair. With this little trick, we have unique pairs!

Exercise

Take a look at the employee table. It contains the following columns:

  • id – the ID of a given employee,
  • name – the name of a given employee,
  • position – the position of a given employee,
  • department – the department of a given employee, and
  • age – the age of a given employee.

Join the table with itself and connect all employees in pairs, showing their names. Display only unique pairs of employees.

Stuck? Here's a hint!

Type:

SELECT
  emp1.name,
  emp2.name
FROM employee emp1
JOIN employee emp2
ON emp1.id  < emp2.id

Console

Code editor

Result

TableConsole