Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close
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. Name the first column name1 and the second one name2. Display only unique pairs of employees.

Stuck? Here's a hint!

Type:

SELECT
  emp1.name AS name1,
  emp2.name AS name2
FROM employee emp1
JOIN employee emp2
  ON emp1.id < emp2.id;