Qualified Joins

CommentsProcedural SQL (PSQL) Statements

Qualified joins

Qualified joins specify conditions for the combining of rows.This happens either explicitly in an ON clause or implicitly in a USING clause.

Syntax

<qualified-join> ::=
  <table-reference> [<join-type>] JOIN <table-primary>
  { ON <search-condition>
  | USING (<column-name-list>) }

<join-type> ::= INNER | {LEFT | RIGHT | FULL} [OUTER]

Explicit-condition joins

Most qualified joins have an ON clause, with an explicit condition that can be any valid Boolean expression, but usually involves a comparison between the two sources involved.

Often, the condition is an equality test (or a number of ANDed equality tests) using the “=” operator.Joins like these are called equi-joins.(The examples in the section on inner and outer joins were all equi-joins.)

Examples of joins with an explicit condition:

/* Select all Detroit customers who made a purchase
   in 2013, along with the purchase details: */
select * from customers c
  join sales s on s.cust_id = c.id
  where c.city = 'Detroit' and s.year = 2013;

/* Same as above, but include non-buying customers: */
select * from customers c
  left join sales s on s.cust_id = c.id
  where c.city = 'Detroit' and s.year = 2013;

/* For each man, select the women who are taller than he.
   Men for whom no such woman exists are not included. */
select m.fullname as man, f.fullname as woman
  from males m
  join females f on f.height > m.height;

/* Select all pupils with their class and mentor.
   Pupils without a mentor are also included.
   Pupils without a class are not included. */
select p.firstname, p.middlename, p.lastname,
       c.name, m.name
  from pupils p
  join classes c on c.id = p.class
  left join mentors m on m.id = p.mentor;