{"id":364,"date":"2011-03-07T14:47:21","date_gmt":"2011-03-07T12:47:21","guid":{"rendered":"http:\/\/grummfy.be\/blog\/?p=364"},"modified":"2011-03-07T14:47:21","modified_gmt":"2011-03-07T12:47:21","slug":"sql-differences-entre-left-join-right-join-etc","status":"publish","type":"post","link":"https:\/\/grummfy.be\/blog\/364","title":{"rendered":"SQL : diff\u00e9rences entre LEFT JOIN, RIGHT JOIN, etc"},"content":{"rendered":"

J’ai toujours eu quelques diffucult\u00e9 a bien visualis\u00e9 les diff\u00e9rence qu’il y avait entre left join, right join, join, etc lorsque je fait des requ\u00eates SQL. Aujourd’hui je suis tomb\u00e9 sur un exemple frappant, et je me suis dit que cela pouvait en aider plus d’un! Comme une image vaux mieux qu’un long discours, en voici l’essence.<\/p>\n

L’exemple suivant se base sur une base de donn\u00e9e mysql :
\n
\nCREATE TABLE IF NOT EXISTS `acl_roles` (
\n`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
\n`name` varchar(30) CHARACTER SET utf8 NOT NULL,
\n`build_on` int(11) unsigned DEFAULT NULL,
\nPRIMARY KEY (`id`),
\nKEY `build_on` (`build_on`)
\n) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;<\/p>\n

INSERT INTO `acl_roles` (`id`, `name`, `build_on`) VALUES
\n(1, 'guest', NULL),
\n(2, 'normal', 1),
\n(3, 'modo', 2),
\n(4, 'admin', 3);<\/p>\n

ALTER TABLE `acl_roles` ADD CONSTRAINT FOREIGN KEY (`build_on`) REFERENCES `acl_roles` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
\n<\/code><\/p>\n

Nous avons donc une table avec des clefs de r\u00e9f\u00e9rence pour marquer les d\u00e9pendance entre les roles. En images cela donne ceci :<\/p>\n\n\n\n\n\n\n\n\n
id<\/th>\nname<\/th>\nbuild_on<\/th>\n<\/tr>\n<\/thead>\n
1<\/td>\nguest<\/td>\nNULL<\/td>\n<\/tr>\n
2<\/td>\nnormal<\/td>\n1<\/td>\n<\/tr>\n
3<\/td>\nmodo<\/td>\n2<\/td>\n<\/tr>\n
4<\/td>\nadmin<\/td>\n3<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Maintenant regardons le r\u00e9sultats de divers SELECT, le r\u00e9sultat parle de lui-m\u00eame.<\/p>\n

SELECT ar.*, arp.name AS parent_name FROM acl_roles ar, acl_roles arp WHERE arp.id = ar.build_on<\/p>\n\n\n\n\n\n\n\n
id<\/th>\nname<\/th>\nbuild_on<\/th>\nparent_name<\/th>\n<\/tr>\n<\/thead>\n
2<\/td>\nnormal<\/td>\n1<\/td>\nguest<\/td>\n<\/tr>\n
3<\/td>\nmodo<\/td>\n2<\/td>\nnormal<\/td>\n<\/tr>\n
4<\/td>\nadmin<\/td>\n3<\/td>\nmodo<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

SELECT ar.*, arp.name AS parent_name FROM acl_roles ar LEFT JOIN acl_roles arp ON arp.id = ar.build_on<\/p>\n\n\n\n\n\n\n\n\n
id<\/th>\n name<\/th>\n build_on<\/th>\n parent_name<\/th>\n<\/tr>\n<\/thead>\n
1<\/td>\nguest<\/td>\nNULL<\/em><\/td>\nNULL<\/em><\/td>\n<\/tr>\n
2<\/td>\nnormal<\/td>\n1<\/td>\nguest<\/td>\n<\/tr>\n
3<\/td>\nmodo<\/td>\n2<\/td>\nnormal<\/td>\n<\/tr>\n
4<\/td>\nadmin<\/td>\n3<\/td>\nmodo<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

SELECT ar.*, arp.name AS parent_name FROM acl_roles ar JOIN acl_roles arp ON arp.id = ar.build_on<\/p>\n\n\n\n\n\n\n\n
id<\/th>\n name<\/th>\n build_on<\/th>\n parent_name<\/th>\n<\/tr>\n<\/thead>\n
2<\/td>\nnormal<\/td>\n1<\/td>\nguest<\/td>\n<\/tr>\n
3<\/td>\nmodo<\/td>\n2<\/td>\nnormal<\/td>\n<\/tr>\n
4<\/td>\nadmin<\/td>\n3<\/td>\nmodo<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

SELECT ar.*, arp.name AS parent_name FROM acl_roles ar RIGHT JOIN acl_roles arp ON arp.id = ar.build_on<\/p>\n\n\n\n\n\n\n\n\n
id<\/th>\n name<\/th>\n build_on<\/th>\n parent_name<\/th>\n<\/tr>\n<\/thead>\n
2<\/td>\nnormal<\/td>\n1<\/td>\nguest<\/td>\n<\/tr>\n
3<\/td>\nmodo<\/td>\n2<\/td>\nnormal<\/td>\n<\/tr>\n
4<\/td>\nadmin<\/td>\n3<\/td>\nmodo<\/td>\n<\/tr>\n
NULL<\/em><\/td>\nNULL<\/em><\/td>\nNULL<\/em><\/td>\nadmin<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

J’esp\u00e8re que l’exemple servira a certain et que cela en aidera plus d’un!<\/p>\n","protected":false},"excerpt":{"rendered":"

J’ai toujours eu quelques diffucult\u00e9 a bien visualis\u00e9 les diff\u00e9rence qu’il y avait entre left join, right join, join, etc lorsque je fait des requ\u00eates SQL. Aujourd’hui je suis tomb\u00e9 sur un exemple frappant, et je me suis dit que cela pouvait en aider plus d’un! Comme une image vaux mieux qu’un long discours, en […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"webmentions_disabled_pings":false,"webmentions_disabled":false,"footnotes":""},"categories":[88],"tags":[18,24,105,28],"class_list":["post-364","post","type-post","status-publish","format-standard","hentry","category-reflexion-du-jour","tag-mysql","tag-programmation","tag-sql","tag-trucs-et-astuces"],"aioseo_notices":[],"aioseo_head":"\n\t\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t