Непонятная структура

таблица product (продукт), поля: id, name (название), product_id (айди продукта)
таблица order (порядок), поля: id, product_id, category_id (айди категории)
таблица category (категория), поля: id, name (название), 

SELECT * FROM product
LEFT JOIN order ON prdouct.category.id = order.id
LEFT JOIN category ON order.category_id = category.id

prdouct.category.id

Зачем у продукта, создавать поле айди продукта, если у него и так будет id?

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

Что означает таблица order? Почему она так названа?

Таблица организует связь много-ко-много. А название, вероятно, взято с потолка - всё одно название ни на что не влияет.

Вот эту цепочку

prdouct.category.id 

я вообще не понимаю

Вероятно, prdouct - это наименование базы данных.

Зачем у продукта, создавать поле айди продукта, если у него и так будет id?

Вы правы, смысла нет. Скорее всего ошибка копипасты

Что означает таблица order?

Обычная классическая кросс-таблица

Почему она так названа?

Этот вопрос нужно задавать разработчику этой БД

Вот эту цепочку

prdouct.category.id

я вообще не понимаю

Это бред. Спросите у того, кто писал этот запрос. Возможно prdouct это название БД, но тогда все равно запрос бессмысленный

А вообще для задачи

достать все товары вместе с их категориями:

Решение такое

SELECT product.*, category.* FROM product
LEFT JOIN order ON order.product_id = product.id
LEFT JOIN category ON order.category_id = category.id

prdouct.category.id

Обычная опечатка. Похоже на product.category_id, но должно быть

LEFT JOIN order ON product.id = order.product_id