title: MySQL: UNION plus JOIN
body: <p>I'm trying to figure out how to use UNION with the same JOIN and not to fall into #2014 - Commands out of sync.</p>
  
  <p>I have created four simple tables and wrote simplified code for them. The main idea is to get each sword, gun and hat which have 'best' in name + if the owner of these is cool.</p>
  
  <pre><code>SELECT pirate_id, sword, null AS gun, null AS hat, legs = 1 AS is_cool FROM swords as t1
  LEFT JOIN
  (SELECT legs, pirate_id as pid FROM body_parts) AS t2
  ON t1.pirate_id = t2.pid
  WHERE sword LIKE '%best%'
  
  UNION
  SELECT pirate_id, null AS sword, gun, null AS hat, legs = 1 AS is_cool FROM guns as t3
  LEFT JOIN
  (SELECT legs, pirate_id as pid FROM body_parts) AS t4
  ON t3.pirate_id = t4.pid
  WHERE gun LIKE '%best%' 
  
  UNION
  SELECT pirate_id, null AS sword, null AS gun, hat, legs = 1 AS is_cool FROM hats as t5
  LEFT JOIN
  (SELECT legs, pirate_id as pid FROM body_parts) AS t6
  ON t5.pirate_id = t6.pid
  WHERE hat LIKE '%best%' 
  
  ORDER BY is_cool DESC, pirate_id ASC
  </code></pre>
  
  <p>Currently I have to repeat JOIN each time I have to UNION. I hope there is a better way...</p>
  
  <p>TABLES CREATION:</p>
  
  <pre><code>CREATE TABLE IF NOT EXISTS `body_parts` (
    `pirate_id` int(11) NOT NULL,
    `legs` int(11) NOT NULL
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  INSERT INTO `body_parts` (`pirate_id`, `legs`) VALUES
  (1, 0),
  (2, 1),
  (3, 2);
  
  CREATE TABLE IF NOT EXISTS `guns` (
    `pirate_id` int(11) NOT NULL,
    `gun` varchar(100) NOT NULL
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  INSERT INTO `guns` (`pirate_id`, `gun`) VALUES
  (1, 'best 1');
  
  CREATE TABLE IF NOT EXISTS `hats` (
    `pirate_id` int(11) NOT NULL,
    `hat` varchar(100) NOT NULL
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  INSERT INTO `hats` (`pirate_id`, `hat`) VALUES
  (2, 'best 2');
  
  CREATE TABLE IF NOT EXISTS `swords` (
    `pirate_id` int(11) NOT NULL,
    `sword` varchar(100) NOT NULL
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  INSERT INTO `swords` (`pirate_id`, `sword`) VALUES
  (3, 'best 3');
  
  </code></pre>