ПОЛНОЕ НАРУЖНОЕ СОЕДИНЕНИЕ с SQLite

Question

SQLite имеет только INNER и LEFT JOIN.

Есть ли способ сделать FULL OUTER JOIN с SQLite?

Answer 1

Да, смотрите пример в Википедии.

SELECT employee.*, department.*
FROM   employee 
       LEFT JOIN department 
          ON employee.DepartmentID = department.DepartmentID
UNION ALL
SELECT employee.*, department.*
FROM   department
       LEFT JOIN employee
          ON employee.DepartmentID = department.DepartmentID
WHERE  employee.DepartmentID IS NULL

Answer 2

FULL OUTER JOIN изначально поддерживается, начиная с SQLite 3.39.0:

2.1. Определение входных данных (обработка предложения FROM)

"FULL JOIN" или "FULL OUTER JOIN" представляет собой комбинацию "LEFT JOIN" и "RIGHT JOIN". Дополнительные строки вывода добавляются для каждой строки в левом наборе данных, которая не соответствует ни одной строке в правом, и для каждой строки в правом наборе данных, которая не соответствует ни одной строке в левом. Несовпадающие столбцы заполняются NULL.

---

Демо:

      CREATE TABLE t1 AS
SELECT 1 AS id, 'A' AS col UNION
SELECT 2 AS id, 'B' AS col;

CREATE TABLE t2 AS
SELECT 1 AS id, 999 AS val UNION
SELECT 3 AS id, 100 AS val;

Запрос:

      SELECT *
FROM t1
FULL JOIN t2
  ON t1.id = t2.id;

db<>демонстрация скрипки

Answer 3

После комментария Джонатана Леффлера вот альтернативный ответ на вопрос Марка Байерса:

SELECT * FROM table_name_1 LEFT OUTER JOIN table_name_2 ON id_1 = id_2
UNION
SELECT * FROM table_name_2 LEFT OUTER JOIN table_name_1 ON id_1 = id_2

Смотрите здесь исходный источник и дополнительные примеры SQLite.

Answer 4

Для людей, в поисках ответа эмулировать Distinct полное внешнее соединение : В связи с тем, что SQLite не имеет ни поддержки полное внешнее объединение, ни правой Join, я должен был подражать отчетливый полное внешнее соединение / Инвертированный внутреннее соединение ( как бы вы это ни называли). На следующей диаграмме Венна показан ожидаемый результат:

---

Чтобы получить этот ожидаемый результат, я объединил два предложения Left Join (пример относится к двум идентичным построенным таблицам с частично разными данными. Я хотел вывести только те данные, которые присутствуют в таблице A ИЛИ в таблице B).

      SELECT A.flightNumber, A.offblockTime, A.airspaceCount, A.departure, A.arrival FROM D2flights A
    LEFT JOIN D1flights B
        ON A.flightNumber = B.flightNumber
        WHERE B.flightNumber IS NULL
UNION 
SELECT A.flightNumber, A.offblockTime, A.airspaceCount,  A.departure, A.arrival FROM D1flights A
    LEFT JOIN D2flights B
        ON A.flightNumber = B.flightNumber
        WHERE B.flightNumber IS NULL

Приведенный выше оператор SQLite возвращает ожидаемый результат в одном запросе. Похоже, что предложение UNION также упорядочивает вывод через столбец flightNumber .

Код был протестирован с SQLite версии 3.32.2.

Answer 5

Я запоздало выложу свои 2 цента. Рассмотрим две простые таблицы people1 и people2 ниже:

         id   name age
0   1    teo  59
1   2   niko  57
2   3  maria  54 


    id   name weight
0   1    teo    186
1   2  maria    125
2   3    evi    108

Сначала мы создаем временное представление v_all, в котором соединяем с помощью UNION два противоположных LEFT JOINS, как показано ниже:

      CREATE TEMP VIEW v_all AS
              SELECT p1.name AS name1, p1.age,
                    p2.name AS name2, p2.weight
              FROM people1 p1
              LEFT JOIN people2 AS p2 
              USING (name)
              UNION
              SELECT p1.name AS name1, p1.age,
                  p2.name AS name2, p2.weight
              FROM people2 AS p2
              LEFT JOIN people1 AS p1
              USING (name);

Однако мы получаем 2 столбца имен, name1 и name2, которые могут иметь нулевое значение или равные значения. Мы хотим объединить name1 и name2 в одном имени столбца. Мы можем сделать это с помощью CASE-запроса, как показано ниже:

      SELECT age,weight,
                CASE
                  WHEN name1 IS NULL
                    THEN name2
                  WHEN name2 IS NULL
                    THEN name1
                  WHEN name1=name2
                    THEN name1
                END name
              FROM v_all

И в итоге мы получаем:

            name weight   age
0    evi    108  None
1  maria    125    54
2   niko   None    57
3    teo    186    59

Конечно, вы можете объединить их в один запрос, не создавая временное представление. Я избегал этого, чтобы подчеркнуть недостаточность всего 2 левых соединений и объединения, что я рекомендовал до сих пор.