SQL: Quick start

Скорее это памятка самому себе, ибо как только перестаёшь постоянно иметь дело с sql, так тут же начинаешь его забывать.
В качестве примера будем рассматривать некоторый офис, значения взяты с Wiki / SQL Join - Sample tables
Таблица «Отдел»:

CREATE TABLE departament (
id INTEGER NOT NULL,
name VARCHAR(32) NOT NULL,

CONSTRAINT departament_id_pk PRIMARY KEY (id),
CONSTRAINT departament_name_qk UNIQUE(name)
);

Данные о отделах:

INSERT INTO departament VALUES 
(31, 'Sales'), 
(33, 'Engineering'), 
(34, 'Clerical'), 
(35, 'Marketing');

Таблица «Сотрудник»:

CREATE TABLE employee (
id INTEGER NOT NULL,
lastname VARCHAR(32) NOT NULL, 
departament_id INTEGER NOT NULL,

CONSTRAINT employee_id_pk PRIMARY KEY (id),
CONSTRAINT employee_name_qk UNIQUE(lastname),
CONSTRAINT employee_departament_id FOREIGN KEY(departament_id)
REFERENCES departament(id)  ON DELETE CASCADE
);

Инфо о сотрудниках:

INSERT INTO employee VALUES 
(1, 'Rafferty', 31), 
(2, 'Jones', 33),
(3, 'Steinberg', 33), 
(4, 'Robinson', 34), 
(5, 'Smith', 34), 
(6, 'Jasper', NULL);

Таблица «Счета сотрудников»:

CREATE TABLE bill(
employee_id INTEGER NOT NULL, 
payments INTEGER, 
debet INTEGER, 

CONSTRAINT bill_employee_id FOREIGN KEY(employee_id)
REFERENCES employee(id) ON DELETE CASCADE
)

Собственно счета:

INSERT INTO bill VALUES 
(1, NULL, 50),  (1, 100, NULL),
(2, NULL, 5),  (2, NULL, 55),
(3, 50, NULL),  (3, 100, NULL),
(4, 50, NULL), (4, NULL,100);

Теперь собственно всякие запросы и результаты:

• Cross Join он же Cartesian join - Декартовое (прямое) произведение.
  

  SELECT employee.lastname, departament.name 
  FROM employee 
  CROSS JOIN departament;

  эквивалентно:

  SELECT employee.lastname, departament.name 
  FROM employee, departament;

• Inner Join - пересечение множеств основной (левой) таблицы и присоединяемой (права) таблицы, которые удовлетворяют условию.
  Замечание: Inner Join может быть заменён на WHERE условие при прямом произведении таблиц.
  
  Пример: получить список всех сотрудников и названиях их отделов, если сотрудник закреплён к отделу

  SELECT employee.lastname, departament.name FROM employee
  INNER JOIN departament 
  ON employee.departament_id = departament.id;

  Замечание: эквивалентно

  SELECT employee.lastname, departament.name FROM employee, departament 
  WHERE employee.departament_id = departament.id

  lastname  |    name     
  -----------+-------------
  Rafferty  | Sales
  Jones     | Engineering
  Steinberg | Engineering
  Robinson  | Clerical
  Smith     | Clerical

• Left (outer) Join - т.н. левое дополнение, когда из основной (слева) таблицы будут получены все значения, а из присоединяемой таблицы (справа), только те, которые удовлетворяют условию.
  Пример: получить список всех сотрудников и названиях их отделов, NULL - если сотрудник не закреплён к отделу.

  SELECT employee.lastname, departament.name FROM employee
  LEFT OUTER JOIN departament 
  ON employee.departament_id = departament.id;

  lastname  |    name     
  -----------+-------------
  Rafferty  | Sales
  Jones     | Engineering
  Steinberg | Engineering
  Robinson  | Clerical
  Smith     | Clerical
  Jasper    | 
  

• Right (outer) Join - т.н. правое дополнение, когда из присоединяемой (справа) таблицы будут получены все значения, а из основной таблицы (левой), только те, которые удовлетворяют условию.
  Пример: получить список всех отделов и сотрудников в этих отделах, NULL - если сотрудников в отделе нет, сотрудники, которые не закреплены к отделу не будут показаны.

  SELECT employee.lastname, departament.name FROM employee
  RIGHT OUTER JOIN departament 
  ON employee.departament_id = departament.id;

  Замечание: может быть представлен через Left (outer) Join

  SELECT employee.lastname, departament.name FROM departament
  LEFT OUTER JOIN employee 
  ON employee.departament_id = departament.id;

  lastname  |    name     
  -----------+-------------
  Rafferty  | Sales
  Steinberg | Engineering
  Jones     | Engineering
  Smith     | Clerical
  Robinson  | Clerical
  | Marketing

• Full Join - объединение двух предыдущих методик.
  
  Пример: получить список всех отделов и сотрудников в этих отделах, NULL - если сотрудников в отделе нет, NULL - если сотрудник не закреплён к отделу.

  SELECT employee.lastname, departament.name FROM employee
  FULL JOIN departament 
  ON employee.departament_id = departament.id;

  lastname  |    name     
  -----------+-------------
  Rafferty  | Sales
  Jones     | Engineering
  Steinberg | Engineering
  Robinson  | Clerical
  Smith     | Clerical
  Jasper    | 
  | Marketing

• Group by - объединение (агрегация) по колонке, часто используется в связке с функциями sum, min, max, average, count.
  Пример: какие сотрудники производили какие-либо операции

  SELECT employee.lastname FROM bill 
  INNER JOIN employee 
  ON employee_id = employee.id 
  GROUP BY employee.lastname;

  lastname  
  -----------
  Steinberg
  Jones
  Robinson
  Rafferty

• Coalesce - выбор первого не NULL аргумента из списка
  Пример:Вычислить баланс сотрудника, однако при вычислении с использованием SUM если одно из значений будет NULL, то и вся сумма будет NULL.

  SELECT employee.lastname,
  SUM(COALESCE(bill.debet, 0)) - SUM(COALESCE(bill.payments, 0)) 
  AS sum
  FROM bill 
  INNER JOIN employee 
  ON employee_id = employee.id
  GROUP BY employee.lastname;

  lastname  | sum  
  -----------+------
  Steinberg | -150
  Jones     |   60
  Robinson  |   50
  Rafferty  |  -50