How can I achieve a Cartesian Product for all distinct entries of two fields within the provided sample data without using a Cross Join (MySQL 5.6)? [closed]

Here’s a sample data set:

CREATE TABLE IF NOT EXISTS `A` (
  `Product_id` int(6) unsigned NOT NULL,
  `Material` varchar(200) NOT NULL,
  `Type` varchar(200) NOT NULL,
  PRIMARY KEY (`Product_id`,`Material`)
) DEFAULT CHARSET=utf8;
INSERT INTO `A` (`Product_id`, `Material`, `Type`) VALUES
  ('123', 'Mahogany', 'Chair'),
  ('123', 'Oak', 'Chair'),
  ('123', 'Birch', 'Chair'),
  ('456', 'Mahogany', 'Table'),
  ('456', 'Oak', 'Table'),
  ('789', 'Steel', 'Fridge'),
  ('789', 'Iron', 'Fridge'),
  ('111', 'Spruce', 'Chair'),
  ('111', 'Mahogany', 'Chair'),
  ('222', 'Mahogany', 'Table');

Specifically, I want to get the Cartesian Product of the ‘Material’ and ‘Type’ fields.

Answer

You can use a regular join:

select *
from (select distinct material from a) m join
     (select distinct type from a) t
     on 1=1;

Seems like a strange request. cross join is the built-in functionality for this purpose. I should note that the on clause is optional in MySQL, but I strongly recommend using it with join.