Конструкция join

Для большей совместимости со стандартом SQL в VIP включена возможность описывать соединения таблиц в секции from с помощью ключевого слова join.

Пример:

select
  x$files.xf$name, x$fields.xe$name
from
  x$files join x$fields
	on (x$files.xf$code = x$fields.xe$filecode)
;

Данный запрос эквивалентен запросу:

select
  x$files.xf$name, x$fields.xe$name
from
  x$files, x$fields
where
((
	root /== x$fields.xe$filecode
  and (x$files.xf$code = x$fields.xe$filecode)
))
;

<join-соединение-таблиц> = <соединение-по-условию>
  | <соединение-по-именам-столбцов> | <естественное-соединение>

<соединение-по-условию> - соединение таблиц определяется подцепками и фильтрами в секции <условие-соединения>. Данный вариант во многом похож на соединение таблиц посредством конструкции where оператора select.

<соединение-по-условию> = <таблица-1> [left] join
  <таблица-2> on <условие-соединения>

<соединение-по-именам-столбцов> - таблицы связываются по условию равенства перечисленных столбцов. Полученные связи воспринимаются как фильтры. Столбцы, по которым связываются таблицы, должны иметь одинаковые имена в обеих таблицах.

<соединение-по-именам-столбцов> = <таблица-1> [left] join
  <таблица-2> using (<список-столбцов>)

<естественное-соединение> - две таблицы объединяются по всем одноименным столбцам. Одноименные столбцы связываются по условию равенства. Полученные связи воспринимаются как фильтры.

<естественное-соединение> = <таблица-1> [left] natural join
  <таблица-2>

<таблица-1> и <таблица-2> - соединяемые таблицы.

left - указывает на необходимость включить в результат запроса несвязные записи (мягкая подцепка).

Так, запрос:

select
  x$files.xf$name, x$fields.xe$name
from
  x$files left join x$fields
	on (x$files.xf$code = x$fields.xe$filecode)
;

Аналогичен запросу

select
  x$files.xf$name, x$fields.xe$name
from
  x$files, x$fields
where
((
	root == x$fields.xe$filecode
  and (x$files.xf$code = x$fields.xe$filecode)
));

В результат запроса попадают несвязные записи из левой таблицы.

join - ключевое слово, указывающее на операцию соединения таблиц (оператор соединения таблиц).

on - ключевое слово, указывающее на связь таблиц по заданным подцепкам и фильтрам.

<условие-соединения> - определяет условия соединения таблиц. Если в условиях соединения присутствуют подцепки, они идут непосредственно после ключевого слова on. Синтаксис описания подцепок похож на синтаксис конструкции where оператора select, но фильтры внутри двойных скобок не допускаются.

<условие-соединения> = (( <подцепки> )) [ and <фильтры> ]
  | <фильтры>

<подцепки> - полный для данной логической таблицы набор соединений таблиц (подцепок), разделенных ключевым словом and. Порядок перечисления сегментов не имеет значения:

<подцепки> = <подцепка> | <подцепки> and <подцепка>

<подцепка> - устанавливает соответствие между отдельным полем подчиненной таблицы и соответствующим ему полем родительской таблицы:

<подцепка> = <левое-выражение> <условие-связи>
  <поле-подчиненной-таблицы>

<левое-выражение> - выражение (в т.ч. поле родительской таблицы), задающее условие связи.

<условие-связи> - аналогично условиям связи конструкции where оператора select. Условие "/==" не поддерживатся. Жеская-мягкая подцепка устанавливается на основании ключевого слова left: при его наличии подцепка мягкая, при отсутствии - жесткая.

<условие-связи> = == | >>= | <<= | >> | <<

<поле-подчиненной-таблицы> - связываемое поле подчиненной таблицы.

<фильтры> - набор условий отбора записей.

<фильтры> = (<предикат>) | <фильтры> and (<предикат>)

<предикат> - выражение, сравнивающее поля родительской и подчиненной таблиц.

<предикат> = <левое-выражение> <операция-сравнения>
  <правое-выражение>

<левое-выражение> и <правое-выражение> - выражения, задающие условие фильтрации. Одно из выражений должно быть полем подчиненной (фильтруемой) таблицы.

<операция-сравнения> - одна из операций сравнения:

<операция-сравнения> = = | >= | <= | > | < | <>

using - ключевое слово, указывающее на связь таблиц по условию равенства перечисленных столбцов.

<список-столбцов> - перечень имен столбцов, по которым связываются таблицы. Они должны иметь одинаковые имена в обеих таблицах.

<список-столбцов> = <столбец> | <столбец>, <список-столбцов>

natural - ключевое слово, указывающее на естественное соединение таблиц. Соединение аналогично join using. В качестве списка столбцов используются все одноименные столбцы связываемых таблиц.

При совместном использовании конструкций join и where связи, образованные конструкцией join, добавляются к связям, образованным конструкцией where (см. Пример 4).

Примеры

Пример 1. Использование разных вариантов оператора join.

Пусть имеются таблицы следующей структуры:

create table Table1
  (
	...
	Code	 : word   "Код ресурса",
	MasterCode : word   "Код владельца ресурса",
	...
  )
create table Table2
  (
	...
	Code	 : word   "Код ресурса",
	MasterCode : word   "Код владельца ресурса",
	...
  )

Тогда соединение таблиц по совпадению двух указанных полей можно записать:

select * from Table1 join Table2 using (Code, MasterCode);

Если Code и MasterCode являются единственными полями с одинаковым названием в обеих таблицах, то можно упростить:

select * from Table1 natural join Table2;

В общем случае соединение двух таблиц оформляется через фильтры:

select * from Table1 join Table2
on
	(Table1.Code = Table2.Code)
  and (Table1.MasterCode = Table2.MasterCode)
;

При наличии необходимых индексов соединение двух таблиц лучше оформить через подцепки:

select * from Table1 join Table2
on
((
	Table1.Code == Table2.Code
  and Table1.MasterCode == Table2.MasterCode
))
;

Можно также воспользоваться комбинацией подцепки и фильтра:

select * from Table1 join Table2
on
((
  Table1.Code == Table2.Code
))
  and (Table1.MasterCode = Table2.MasterCode)
;

Пример 2. Связывание таблиц по фильтрам.

interface JoinDemo2 'Связывание таблиц по фильтрам' EscClose, Cyan;
  show at (0,0,79,25);

create view
as select
  x$fields.xe$name, x$indexes.xi$name
from
  x$indexes left join x$fields on
	(x$indexes.xi$filecode = x$fields.xe$filecode)
	and (x$indexes.xi$fieldcode = x$fields.xe$code)
;

browse brw1;
  show at (1,1,78,10);
  fields
	x$indexes.xi$name 'Индекс' : [15], protect;
	x$fields.xe$name  'Поле' : [15], protect;
end;

end.

Пример 3. Связывание таблиц по подцепке и фильтрам.

interface JoinDemo3 'Связывание таблиц по подцепке и фильтрам' EscClose, Cyan;
  show at (0,0,79,25);

create view
as select
  x$fields.xe$name, x$indexes.xi$name
from
  x$indexes join x$fields on
	((x$indexes.xi$filecode == x$fields.xe$filecode))
	and (x$indexes.xi$fieldcode = x$fields.xe$code)
	and ('PRJEXECUTORS01' = x$indexes.xi$name)
;

browse brw1;
  show at (1,1,78,10);
  fields
	x$indexes.xi$name 'Индекс' : [15], protect;
	x$fields.xe$name  'Поле' : [15], protect;
end;

end.

Пример 4. Связывание таблиц с совместным использованием конструкций join и where.

interface JoinDemo3 'Join совместно с Where' EscClose, Cyan;
  show at (0,0,79,25);

create view
as select
  x$files.xf$name, x$fields.xe$name, x$indexes.xi$name
from
  x$fields,
  x$files left join x$indexes on
	((x$files.xf$code == x$indexes.xi$filecode))
where
  ((
		x$indexes.xi$filecode == x$fields.xe$filecode
	and x$indexes.xi$fieldcode == x$fields.xe$code
  ))
;

browse brw1;
  show at (1,1,78,9);
  table x$files;
  fields
	x$files.xf$name   'Файл' : [15], protect;
end;

browse brw2;
  show at (1,10,78,17);
  table x$indexes;
  fields
	x$indexes.xi$name 'Индекс' : [15], protect;
end;

browse brw3;
  show at (1,18,78);
  table x$fields;
  fields
	x$fields.xe$name  'Поле' : [15], protect;
end;

end.

Версия

Атлантис 5.1.34.