Comprendre les jointures avec MySQL

inner-join.sql

SELECT *
FROM post AS p
INNER JOIN category AS c ON p.category_id = c.category_id;

install.sql

--
-- Structure de la table `post`
--

CREATE TABLE IF NOT EXISTS `post` (
  `post_id` int(11) NOT NULL auto_increment,
  `post_title` varchar(50) NOT NULL,
  `category_id` int(11) default NULL,
  PRIMARY KEY  (`post_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=10 ;

--
-- Contenu de la table `post`
--

INSERT INTO `post` (`post_id`, `post_title`, `category_id`) VALUES
(1, 'Bienvenue', 1),
(2, 'Settimeout Javascript', 2),
(3, 'Sprites CSS', 3),
(4, 'Firebug', 2),
(5, 'Google Apps', NULL),
(6, 'Colorisation Photoshop', 3),
(7, 'Base photographie', 5),
(8, 'Jointure SQL', 4),
(9, 'Article top secret', NULL);

--
-- Structure de la table `category`
--

CREATE TABLE IF NOT EXISTS `category` (
  `category_id` int(11) NOT NULL auto_increment,
  `category_name` varchar(50) NOT NULL,
  PRIMARY KEY  (`category_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=7 ;

--
-- Contenu de la table `category`
--

INSERT INTO `category` (`category_id`, `category_name`) VALUES
(1, 'News'),
(2, 'Javascript'),
(3, 'Graphisme'),
(4, 'SQL'),
(5, 'Photographie'),
(6, 'PHP');

left-join.sql

SELECT * 
FROM post AS p
LEFT JOIN category AS c ON p.category_id = c.category_id;

right-join.sql

SELECT * 
FROM post AS p
RIGHT JOIN category AS c ON p.category_id = c.category_id;

select.sql

SELECT * 
FROM post AS p, category AS c
WHERE p.category_id = c.category_id;

union.sql

(SELECT * 
FROM post AS p
LEFT JOIN category AS c ON p.category_id = c.category_id)
UNION
(SELECT * 
FROM post AS p
RIGHT JOIN category AS c ON p.category_id = c.category_id);