Троичная логика в Transact-SQL

Троичная логика (или трехзначная логика) — это логическая система, которая оперирует тремя значениями:

• TRUE (истина)
• FALSE (ложь)
• UNKNOWN (неизвестно)

В реляционных базах данных, таких как Microsoft SQL Server, PostgreSQL и SQLite, троичная логика используется для обработки ситуаций, когда данные могут быть неопределны или отсутствовать в принципе. Основой троичной логики является значение NULL, которое обозначает отсутствие данных. Операции с участием NULL могут приводить к результату UNKNOWN, что означает, что результат выражения не может быть определен как истинный или ложный.

Описание базы данных для тестовых операций

Для демонстрации работы троичной логики в SQL мы создадим базу данных, которая будет представлять собой систему управления заказами для интернет-магазина. В примере будут представлены следующие сущности:

1. Клиенты — клиенты могут регистрироваться в системе и делать заказы. Однако не все клиенты подтверждают свои учетные записи. Поле verified в таблице клиентов может быть TRUE, FALSE или NULL, если клиент еще не завершил регистрацию.

2. Сотрудники — сотрудники работают в отделе продаж или на складе и обрабатывают заказы. Поле access_confirmed может быть TRUE, FALSE или NULL, если у сотрудника еще не подтвержден доступ к системе.

3. Товары — на складе могут находиться разные товары, которые могут быть доступны, зарезервированы или отсутствовать. Статус товара может быть представлен как 'available', 'reserved' или 'out_of_stock'.

4. Заказы — клиенты могут делать заказы на товары. Заказ может быть выполнен, отменен или находиться в процессе обработки. Статус заказа может быть представлен как 'completed', 'pending' или 'cancelled'.

5. Счета — каждый заказ сопровождается счетом. Статус оплаты может быть 'paid', 'unpaid' или 'partially_paid', что позволяет отслеживать состояние оплаты заказа.

Структура базы данных:

-- Таблица клиентов
CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE,
    registration_date DATE NOT NULL,
    verified BOOLEAN  -- Подтверждена ли учетная запись клиента (TRUE, FALSE, NULL)
);

-- Таблица сотрудников
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE,
    phone_number TEXT UNIQUE,
    access_confirmed BOOLEAN,  -- Подтвержден ли доступ сотрудника к системе
    hire_date DATE NOT NULL
);

-- Таблица товаров
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    sku TEXT UNIQUE,  -- Уникальный код товара
    quantity INT NOT NULL,  -- Количество на складе
    status TEXT CHECK (status IN ('available', 'reserved', 'out_of_stock'))  -- Статус товара
);

-- Таблица заказов
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers(id),
    employee_id INT REFERENCES employees(id),  -- Сотрудник, который обрабатывает заказ
    product_id INT REFERENCES products(id),
    order_date DATE NOT NULL,
    quantity INT NOT NULL,  -- Количество заказанных товаров
    status TEXT CHECK (status IN ('completed', 'cancelled', 'pending'))  -- Статус заказа
);

-- Таблица счетов
CREATE TABLE invoices (
    id SERIAL PRIMARY KEY,
    order_id INT REFERENCES orders(id),
    payment_date DATE,
    total_amount NUMERIC(10, 2) NOT NULL,  -- Общая сумма счета
    paid_amount NUMERIC(10, 2),  -- Оплаченная сумма
    payment_status TEXT CHECK (payment_status IN ('paid', 'unpaid', 'partially_paid'))  -- Статус оплаты
);

Пример тестовых данных:

-- Вставляем данные клиентов
INSERT INTO customers (name, email, registration_date, verified) VALUES
('John Doe', 'john.doe@example.com', '2021-01-20', TRUE),
('Jane Smith', 'jane.smith@example.com', '2022-04-05', FALSE),  -- Клиент не подтвердил учетную запись
('Alex Johnson', 'alex.johnson@example.com', '2023-07-12', NULL);  -- Неизвестно, подтвердил ли клиент учетную запись

-- Вставляем данные сотрудников
INSERT INTO employees (name, email, phone_number, access_confirmed, hire_date) VALUES
('Ivan Petrov', 'ivan.petrov@example.com', '1234567890', TRUE, '2020-03-15'),
('Anna Sidorova', 'anna.sidorova@example.com', '0987654321', FALSE, '2021-06-01'),
('Oleg Ivanov', 'oleg.ivanov@example.com', NULL, NULL, '2022-01-20');  -- У сотрудника нет номера телефона и доступа

-- Вставляем данные товаров
INSERT INTO products (name, sku, quantity, status) VALUES
('Laptop HP', 'LAP123', 50, 'available'),
('Smartphone Samsung', 'PHN456', 20, 'reserved'),
('Tablet Lenovo', 'TBL789', 0, 'out_of_stock');  -- Товара нет на складе

-- Вставляем данные заказов
INSERT INTO orders (customer_id, employee_id, product_id, order_date, quantity, status) VALUES
(1, 1, 1, '2023-05-15', 2, 'completed'),  -- Заказ Laptop HP для клиента John Doe
(2, 2, 2, '2023-05-20', 1, 'pending'),  -- Заказ для клиента Jane Smith в процессе
(3, 3, 3, '2023-06-01', 1, 'cancelled');  -- Отмененный заказ по товару Tablet Lenovo

-- Вставляем данные счетов
INSERT INTO invoices (order_id, payment_date, total_amount, paid_amount, payment_status) VALUES
(1, '2023-05-16', 2000.00, 2000.00, 'paid'),  -- Заказ полностью оплачен
(2, NULL, 1500.00, NULL, 'unpaid'),  -- Заказ не оплачен
(3, '2023-06-02', 500.00, 250.00, 'partially_paid');  -- Заказ частично оплачен

1. Операции с NULL и UNKNOWN

Операции с NULL — это основа троичной логики в SQL. NULL используется для обозначения отсутствующего или неопределенного значения. В троичной логике результат выражения с участием NULL может быть:

• TRUE
• FALSE
• UNKNOWN (неопределенно)

UNKNOWN возникает, когда в логических выражениях участвует NULL, и система не может определить результат выражения. Это ключевая часть троичной логики, которая активно используется в Microsoft SQL Server. В таких случаях результатом выражения является не TRUE и не FALSE, а UNKNOWN.

Пример: Сравнение с NULL

В SQL нельзя просто сравнить значение с NULL с помощью оператора = или !=. Например, следующий запрос не вернет результатов, даже если есть строки с NULL:

SELECT * FROM customers WHERE verified = NULL;

Для работы с NULL нужно использовать специальные операторы:

• IS NULL — проверяет, является ли значение NULL.
• IS NOT NULL — проверяет, что значение не является NULL.

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

SELECT * FROM customers WHERE verified IS NULL;

Этот запрос вернет всех клиентов, для которых статус верификации учетной записи не определен.

2. Логические операторы (AND, OR, NOT)

Логические операторы в SQL также подчиняются троичной логике. Результат выражения зависит от того, участвует ли в нем NULL. Если одно из условий в логическом выражении имеет значение NULL, результатом будет UNKNOWN.

Пример: Использование AND

SELECT * FROM employees WHERE access_confirmed = TRUE AND phone_number IS NOT NULL;

Этот запрос вернет всех сотрудников, которые подтвердили доступ и у которых указан номер телефона. Если access_confirmed или phone_number имеют значение NULL, результат будет UNKNOWN, и такие записи не попадут в результат.

Пример: Использование OR

SELECT * FROM customers WHERE verified = TRUE OR verified IS NULL;

Этот запрос вернет всех клиентов, чья учетная запись подтверждена (TRUE), а также тех, чей статус верификации не определен (NULL).

Таблица результатов логических операторов в троичной логике

3. Операторы сравнения

Операторы сравнения (=, !=, >, <, и т.д.) также подчиняются троичной логике. Если одно из сравниваемых значений — это NULL, результат всегда будет UNKNOWN. Это ключевая особенность троичной логики: NULL не равно ни чему, даже самому себе.

Пример: Сравнение с NULL

SELECT * FROM products WHERE status = 'available' OR status = 'reserved';

Этот запрос вернет только те продукты, у которых статус — available или reserved, но не вернет продукты со статусом NULL, потому что сравнение с NULL всегда возвращает UNKNOWN.

Пример: Использование оператора IS DISTINCT FROM

Этот оператор — полезная альтернатива для сравнения, которая учитывает NULL. Например, следующий запрос вернет всех клиентов, у которых учетная запись не подтверждена или статус неизвестен:

SELECT * FROM customers WHERE verified IS DISTINCT FROM TRUE;

IS DISTINCT FROM сравнивает значения с учетом NULL, в отличие от обычного оператора !=, который вернет UNKNOWN, если одно из значений является NULL.

4. JOIN'ы

При работе с JOIN'ами в SQL NULL может привести к неожиданным результатам. Например, если в одной из таблиц поле, участвующее в соединении, имеет значение NULL, оно не будет участвовать в соединении при использовании INNER JOIN.

Пример: Использование LEFT JOIN для учета NULL

SELECT s.id, c.name AS customer_name, e.name AS employee_name, p.name AS product_name, s.status
FROM sales s
LEFT JOIN customers c ON s.customer_id = c.id
LEFT JOIN employees e ON s.employee_id = e.id
JOIN products p ON s.product_id = p.id;

В этом запросе используется LEFT JOIN для получения всех записей из таблицы sales, даже если у некоторых сделок нет информации о клиенте или сотруднике (например, если одно из этих полей равно NULL). INNER JOIN в данном случае исключил бы такие записи.

Пример: INNER JOIN и исключение строк с NULL

SELECT s.id, c.name, e.name, p.name
FROM sales s
JOIN customers c ON s.customer_id = c.id
JOIN employees e ON s.employee_id = e.id
JOIN products p ON s.product_id = p.id;

Здесь INNER JOIN приведет к тому, что сделки, у которых customer_id или employee_id равно NULL, не будут включены в результаты.

Заключение

Понимание принципов троичной логики в реляционных базах данных, поддерживающих Transact-SQL, является критически важным аспектом для обеспечения корректной и предсказуемой обработки данных. Способность правильно интерпретировать результаты операций с NULL и учитывать поведение логических выражений с использованием значений TRUE, FALSE и UNKNOWN позволяет избежать ошибок при выполнении запросов, особенно при работе с отсутствующими или неопределенными данными. Внимательное отношение к этим аспектам помогает проектировать более надежные и точные SQL-запросы, улучшая качество и точность обработки информации в базе данных.