{"id":4925,"date":"2015-11-26T12:00:53","date_gmt":"2015-11-26T03:00:53","guid":{"rendered":"http:\/\/www.skyarch.net\/blog\/?p=4925"},"modified":"2015-11-30T17:32:10","modified_gmt":"2015-11-30T08:32:10","slug":"understanding-sql-joins","status":"publish","type":"post","link":"https:\/\/www.skyarch.net\/blog\/understanding-sql-joins\/","title":{"rendered":"Understanding SQL Joins"},"content":{"rendered":"<h1><span style=\"color: #ff6600\"><strong>SQL JOINS<\/strong><\/span><\/h1>\n<h2 id=\"loom-header-2\" style=\"color: #cc6600\">An instruction to a database to combine data from more than one table.<\/h2>\n<p><strong>Mostly used SQL Join:<\/strong><\/p>\n<ul>\n<li><strong>INNER JOIN<\/strong> : Returns all rows When there IS at least one match in BOTH tables.<\/li>\n<li><strong>LEFT JOIN<\/strong> : Return all rows from the left table, and the matched rows from the right table.<\/li>\n<\/ul>\n<p><strong>Other commonly used SQL Join:<\/strong><\/p>\n<ul>\n<li><strong>RIGHT JOIN<\/strong> : Return all rows from the right table, and the matched rows from the left table.<\/li>\n<li><strong>FULL JOIN<\/strong> : Return all rows When there's a <strong>matched in ONE of the tables<\/strong>.<\/li>\n<\/ul>\n<table>\n<thead>\n<tr>\n<th><strong>id<\/strong><\/th>\n<th><strong>first_name<\/strong><\/th>\n<th><strong>last_name<\/strong><\/th>\n<th><strong>age<\/strong><\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td>1<\/td>\n<td>ryan<\/td>\n<td>bang<\/td>\n<td>24<\/td>\n<\/tr>\n<tr>\n<td>2<\/td>\n<td>jow<\/td>\n<td>nathan<\/td>\n<td>34<\/td>\n<\/tr>\n<tr>\n<td>3<\/td>\n<td>wilson<\/td>\n<td>chandler<\/td>\n<td>31<\/td>\n<\/tr>\n<tr>\n<td>4<\/td>\n<td>jerald<\/td>\n<td>green<\/td>\n<td>22<\/td>\n<\/tr>\n<\/tbody>\n<caption style=\"text-align: left;color: #ff6600\"><strong>USERS TABLE<\/strong><\/caption>\n<\/table>\n<table>\n<thead>\n<tr>\n<th><strong>id<\/strong><\/th>\n<th><strong>user_id<\/strong><\/th>\n<th><strong>product<\/strong><\/th>\n<th><strong>category<\/strong><\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td>1<\/td>\n<td>4<\/td>\n<td>casserole<\/td>\n<td>kitchen<\/td>\n<\/tr>\n<tr>\n<td>2<\/td>\n<td>2<\/td>\n<td>grass cutter<\/td>\n<td>garage<\/td>\n<\/tr>\n<tr>\n<td>3<\/td>\n<td>1<\/td>\n<td>wiper<\/td>\n<td>general<\/td>\n<\/tr>\n<tr>\n<td>4<\/td>\n<td>1<\/td>\n<td>rice cooker<\/td>\n<td>electronic<\/td>\n<\/tr>\n<\/tbody>\n<caption style=\"text-align: left;color: #ff6600\"><strong>PRODUCTS TABLE<\/strong><\/caption>\n<\/table>\n<h3 id=\"loom-header-3\"><strong style=\"color: #cc6600;font-size: 16px\">\u2666 LEFT JOIN or LEFT OUTER JOIN<\/strong><\/h3>\n<ul>\n<li>A left outer join, or left join, <strong>results in a set where all of the rows from the first, or left hand side, table are preserved<\/strong>.<\/li>\n<li>The rows from the Second, or right hand side table <strong>only show up if they have a matched<\/strong> with the rows from the first table.<\/li>\n<li>Where there Are values \u200b\u200bfrom the left table but not from the right, the table Will read <strong>null<\/strong> , Which Means That the value HAS not Been set.<\/li>\n<\/ul>\n<div class=\"markdown-body\">\n<p><strong>example<\/strong> :<\/p>\n<p style=\"line-height: 25px;background-color: #eee;padding: 5px 10px 5px 10px;font-weight: bold;font-size: 13px\">SELECT user.id, user.first_name, user.last_name, product.name, product.category<br \/>\nFROM users<br \/>\nLEFT JOIN products<br \/>\nON user_id = id;<\/p>\n<p style=\"text-align: left\"><strong>Result<\/strong> :<\/p>\n<table>\n<thead>\n<tr>\n<th><strong>id<\/strong><\/th>\n<th><strong>first_name<\/strong><\/th>\n<th><strong>last_name<\/strong><\/th>\n<th><strong>age<\/strong><\/th>\n<th><strong>name<\/strong><\/th>\n<th><strong>category<\/strong><\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td>1<\/td>\n<td>ryan<\/td>\n<td>bang<\/td>\n<td>24<\/td>\n<td>wiper<\/td>\n<td>general<\/td>\n<\/tr>\n<tr>\n<td>1<\/td>\n<td>ryan<\/td>\n<td>bang<\/td>\n<td>24<\/td>\n<td>rice cooker<\/td>\n<td>electronic<\/td>\n<\/tr>\n<tr>\n<td>2<\/td>\n<td>jow<\/td>\n<td>nathan<\/td>\n<td>34<\/td>\n<td>grass cutter<\/td>\n<td>garage<\/td>\n<\/tr>\n<tr>\n<td>3<\/td>\n<td>wilson<\/td>\n<td>chandler<\/td>\n<td>31<\/td>\n<td>null<\/td>\n<td>null<\/td>\n<\/tr>\n<tr>\n<td>4<\/td>\n<td>jerald<\/td>\n<td>green<\/td>\n<td>22<\/td>\n<td>casserole<\/td>\n<td>kitchen<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p><strong>Note<\/strong> :<br \/>\nOn the Other hand, <strong>LEFT JOIN <\/strong>has some downfall .<br \/>\nIf there Are much Users, you Have to Check all users even if you only need the user HAVING some Products.<\/p>\n<h3 id=\"loom-header-4\"><strong style=\"color: #cc6600;font-size: 16px\">\u2666 INNER JOIN<\/strong><\/h3>\n<ul>\n<li>An Inner join Produces a result set That IS Limited to the rows where there's a <strong>matched in both tables<\/strong> for what we're looking for.<\/li>\n<\/ul>\n<p><strong>example<\/strong> :<\/p>\n<p style=\"line-height: 25px;background-color: #eee;padding: 5px 10px 5px 10px;font-weight: bold;font-size: 13px\">SELECT user.id, user.first_name, user.last_name, product.name, product.category<br \/>\nFROM users<br \/>\nINNER JOIN products<br \/>\nON user_id = id;<\/p>\n<p style=\"text-align: left\"><strong>Result<\/strong> :<\/p>\n<table>\n<thead>\n<tr>\n<th><strong>id<\/strong><\/th>\n<th><strong>first_name<\/strong><\/th>\n<th><strong>last_name<\/strong><\/th>\n<th><strong>age<\/strong><\/th>\n<th><strong>name<\/strong><\/th>\n<th><strong>category<\/strong><\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td>1<\/td>\n<td>ryan<\/td>\n<td>bang<\/td>\n<td>24<\/td>\n<td>wiper<\/td>\n<td>general<\/td>\n<\/tr>\n<tr>\n<td>1<\/td>\n<td>ryan<\/td>\n<td>bang<\/td>\n<td>24<\/td>\n<td>rice cooker<\/td>\n<td>electronic<\/td>\n<\/tr>\n<tr>\n<td>2<\/td>\n<td>jow<\/td>\n<td>nathan<\/td>\n<td>34<\/td>\n<td>grass cutter<\/td>\n<td>garage<\/td>\n<\/tr>\n<tr>\n<td>4<\/td>\n<td>jerald<\/td>\n<td>green<\/td>\n<td>22<\/td>\n<td>casserole<\/td>\n<td>kitchen<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p><strong>Other query example<\/strong> :<\/p>\n<p style=\"line-height: 25px;background-color: #eee;padding: 5px 10px 5px 10px;font-weight: bold;font-size: 13px\">select * from users INNER JOIN products on<br \/>\nid = user_id;<\/p>\n<ul>\n<li>This can also be written as:<\/li>\n<\/ul>\n<p style=\"line-height: 25px;background-color: #eee;padding: 5px 10px 5px 10px;font-weight: bold;font-size: 13px\">select * from users, products where id = user_id;<\/p>\n<\/div>\n<p><a href=\"http:\/\/www.codeproject.com\/Articles\/33052\/Visual-Representation-of-SQL-Joins\">Reference: SQL Joins<\/a><\/p>\n<p style=\"color: #ff6600;font-size: 16px;letter-spacing: 1px\">Happy Programming Everyone !!!\u00a0<img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-4962\" src=\"http:\/\/www.skyarch.net\/blog\/wp-content\/uploads\/2015\/11\/smiley_skyarch1.jpg\" alt=\"smiley_skyarch\" width=\"25\" height=\"25\" \/><\/p>\n","protected":false},"excerpt":{"rendered":"<p>SQL JOINS An instruction to a database to combine data from more than one table. Mostly used SQL Join: INNER J&#8230;<\/p>\n","protected":false},"author":1,"featured_media":4940,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_locale":"ja","_original_post":"4925","footnotes":""},"categories":[23,9],"tags":[],"class_list":{"0":"post-4925","1":"post","2":"type-post","3":"status-publish","4":"format-standard","5":"has-post-thumbnail","7":"category-23","8":"category-dev","9":"ja"},"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.skyarch.net\/blog\/wp-json\/wp\/v2\/posts\/4925","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.skyarch.net\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.skyarch.net\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.skyarch.net\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.skyarch.net\/blog\/wp-json\/wp\/v2\/comments?post=4925"}],"version-history":[{"count":14,"href":"https:\/\/www.skyarch.net\/blog\/wp-json\/wp\/v2\/posts\/4925\/revisions"}],"predecessor-version":[{"id":4973,"href":"https:\/\/www.skyarch.net\/blog\/wp-json\/wp\/v2\/posts\/4925\/revisions\/4973"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.skyarch.net\/blog\/wp-json\/wp\/v2\/media\/4940"}],"wp:attachment":[{"href":"https:\/\/www.skyarch.net\/blog\/wp-json\/wp\/v2\/media?parent=4925"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.skyarch.net\/blog\/wp-json\/wp\/v2\/categories?post=4925"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.skyarch.net\/blog\/wp-json\/wp\/v2\/tags?post=4925"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}