{"id":5531,"date":"2022-01-24T04:44:08","date_gmt":"2022-01-24T04:44:08","guid":{"rendered":"https:\/\/studysection.com\/blog\/?p=5531"},"modified":"2022-01-24T05:33:44","modified_gmt":"2022-01-24T05:33:44","slug":"join-two-tables-without-a-common-column-in-mysql","status":"publish","type":"post","link":"https:\/\/studysection.com\/blog\/join-two-tables-without-a-common-column-in-mysql\/","title":{"rendered":"Join Two Tables Without a Common Column in MySQL"},"content":{"rendered":"

There are few ways to combine the two tables without a common column including Cross Join (Cartesian Product) and UNION. This is not a join but can be useful for merging tables in SQL<\/a>. In this post, I\u2019ll explain different solutions with examples.<\/p>\n

Here is the example Below.<\/h2>\n

Suppose there is a restaurant and they have two tables wine<\/strong> and main_courser<\/strong> in their database.<\/p>\n

The wine table contains the wine ID<\/strong>, the name of the wine<\/strong>, the supplier ID<\/strong>, and the price:<\/strong><\/p>\n\n\n\n\n\n\n
id<\/td>\nname<\/td>\nsupplier_id<\/td>\nprice<\/td>\n<\/tr>\n
1<\/td>\nMerlot<\/td>\n500<\/td>\n7.95<\/td>\n<\/tr>\n
2<\/td>\nHouse<\/td>\n400<\/td>\n2.45<\/td>\n<\/tr>\n
3<\/td>\nSangiovese<\/td>\n600<\/td>\n5.20<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

The main course table contains the main course id, the main course name, the major supplier ID for this meal, and the price:<\/p>\n\n\n\n\n\n\n\n
id<\/td>\nname<\/td>\nmajor_supplier_id<\/td>\nprice<\/td>\n<\/tr>\n
1<\/td>\nCardamom Maple Salmon<\/td>\n200<\/td>\n19.99<\/td>\n<\/tr>\n
2<\/td>\nClassic Macaroni & Cheese<\/td>\n100<\/td>\n8.99<\/td>\n<\/tr>\n
3<\/td>\nBaked Teriyaki Chicken<\/td>\n300<\/td>\n11.99<\/td>\n<\/tr>\n
4<\/td>\nBlue Cheese Beef Tenderloin<\/td>\n400<\/td>\n15.99<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

In our first example, we want all the possible combinations of wines and main course for our menu.
\nFirst, we will use the FROM TABLE 1, TABLE 2 Syntax:<\/em>
\nWe simply join the two tables together with this syntax. Two tables are joined with the help of FROM clause and then use the WHERE clause if necessary.
\nThere is no need to specify any joining conditions if all we want is every combination of rows from two tables. A query like this can be used:<\/p>\n

SELECT w.name AS wine, m.name AS main_course
\nFROM wine w, main_course m;<\/code>
\nThis query provides a cross join, or cartesian product, with a total number of rows equal to the first table’s number of rows multiplied by the second table’s number of rows.<\/p>\n\n\n\n\n\n\n\n\n\n\n\n
wine<\/td>\nmain_course<\/td>\n<\/tr>\n
Merlot<\/td>\nCardamom Maple Salmon<\/td>\n<\/tr>\n
House<\/td>\nCardamom Maple Salmon<\/td>\n<\/tr>\n
Sangiovese<\/td>\nCardamom Maple Salmon<\/td>\n<\/tr>\n
Merlot<\/td>\nClassic Macaroni & Cheese<\/td>\n<\/tr>\n
House<\/td>\nClassic Macaroni & Cheese<\/td>\n<\/tr>\n
Sangiovese<\/td>\nClassic Macaroni & Cheese<\/td>\n<\/tr>\n
Merlot<\/td>\nBaked Teriyaki Chicken<\/td>\n<\/tr>\n
House<\/td>\nBaked Teriyaki Chicken<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

This is one of the approaches for joining two tables together when the objective is to get a result set with all possible combinations of the rows but this is not the best approach.<\/p>\n

Mostly, CROSS JOIN is used for this type of operation.<\/p>\n

Using the CROSS JOIN:<\/h3>\n

SELECT w.name AS wine, m.name AS main_course
\nFROM wine w
\nCROSS JOIN main_course m;<\/code><\/p>\n

This query produces the same result as earlier. This syntax is more readable for the users and it also clearly states the purpose of CROSS JOIN.<\/p>\n

Using UNION or UNION ALL:<\/h3>\n

There are additional reasons to join two tables that don’t have a common column. You may want to integrate all of the supplier information maintained in several tables, as in the previous example. In this scenario, you don’t want a Cartesian product. So, how do you put the tables together?<\/p>\n

In this scenario, a UNION is used to combine data from different tables. Although this is not exactly a join, it can be highly useful for integrating rows from multiple tables, as in the example below.<\/p>\n

Simply put, JOINS joins two tables together by adding the columns from one table to the columns from the other. UNIONs, on the other hand, merge data by appending rows from one table to the rows from another.<\/p>\n

So, if we want to get a list of all the supplier IDs from the databases wine and main course, we may use the SQL query below:
\nSELECT w.supplier_id
\nFROM wine w
\nUNION
\nSELECT m.major_supplier_id
\nFROM main_course m
\nORDER BY supplier_id;<\/code><\/p>\n

We select the supplier IDs individually from the wine table (column supplier id) and the main course table (column-major supplier id). The UNION keyword is then used to join these rows together. Finally, for ease of use, we order the results:<\/p>\n\n\n\n\n\n\n\n\n\n
supplier_id<\/td>\n<\/tr>\n
100<\/td>\n<\/tr>\n
200<\/td>\n<\/tr>\n
300<\/td>\n<\/tr>\n
400<\/td>\n<\/tr>\n
500<\/td>\n<\/tr>\n
600<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Note the following when using UNION in SQL:<\/h3>\n

The number of columns in each SELECT statement should be the same.
\nThe data types of the related columns must be the same.
\nAs seen in our example, the related columns can have different names. By default, the first SELECT statement is used to determine the name of the relevant column in the output. The AS keyword can also be used to change the names of the generated columns.<\/p>\n

The UNION operator removes duplicate records. Although the supplier id 400 is present in both tables, it only appears once in the result set. If you don’t want duplicate records, use UNION ALL:
\nSELECT w.supplier_id
\nFROM wine w
\nUNION ALL
\nSELECT m.major_supplier_id
\nFROM main_course m
\nORDER BY 1;<\/code><\/p>\n

The supplier_id 400 is twice in this query:<\/strong><\/p>\n\n\n\n\n\n\n\n\n\n\n
supplier_id<\/td>\n<\/tr>\n
100<\/td>\n<\/tr>\n
200<\/td>\n<\/tr>\n
300<\/td>\n<\/tr>\n
400<\/td>\n<\/tr>\n
400<\/td>\n<\/tr>\n
500<\/td>\n<\/tr>\n
600<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Study Section provides a big list of certification exams through its online platform. The French Certification Exam<\/a> can help you to certify your skills to communicate in the French language. Whether you are new to the language or you are an expert in it, this French certification exam can test the ability of anybody\u2019s command over the French language.<\/em><\/small><\/p>\n","protected":false},"excerpt":{"rendered":"

There are few ways to combine the two tables without a common column including Cross Join (Cartesian Product) and UNION.<\/p>\n","protected":false},"author":1,"featured_media":5532,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[6],"tags":[507,755],"acf":[],"yoast_head":"\nJoin Two Tables without a common column in MySQL - SS Blog<\/title>\n<meta name=\"description\" content=\"This is not a join but can be useful for merging tables in SQL. In this post, I\u2019ll explain different solutions with examples.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/studysection.com\/blog\/join-two-tables-without-a-common-column-in-mysql\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Join Two Tables without a common column in MySQL - SS Blog\" \/>\n<meta property=\"og:description\" content=\"This is not a join but can be useful for merging tables in SQL. In this post, I\u2019ll explain different solutions with examples.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/studysection.com\/blog\/join-two-tables-without-a-common-column-in-mysql\/\" \/>\n<meta property=\"og:site_name\" content=\"Blog Posts on famous people, innovations and educational topics\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/studysection\" \/>\n<meta property=\"article:published_time\" content=\"2022-01-24T04:44:08+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2022-01-24T05:33:44+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/studysection.com\/blog\/wp-content\/uploads\/2022\/01\/MySQL.png\" \/>\n\t<meta property=\"og:image:width\" content=\"300\" \/>\n\t<meta property=\"og:image:height\" content=\"200\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"admin-studysection-blog\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@studysection\" \/>\n<meta name=\"twitter:site\" content=\"@studysection\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"admin-studysection-blog\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/studysection.com\/blog\/join-two-tables-without-a-common-column-in-mysql\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/studysection.com\/blog\/join-two-tables-without-a-common-column-in-mysql\/\"},\"author\":{\"name\":\"admin-studysection-blog\",\"@id\":\"https:\/\/studysection.com\/blog\/#\/schema\/person\/db367e2c29a12d1808fb1979edb3d402\"},\"headline\":\"Join Two Tables Without a Common Column in MySQL\",\"datePublished\":\"2022-01-24T04:44:08+00:00\",\"dateModified\":\"2022-01-24T05:33:44+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/studysection.com\/blog\/join-two-tables-without-a-common-column-in-mysql\/\"},\"wordCount\":744,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/studysection.com\/blog\/#organization\"},\"keywords\":[\"MySQL\",\"Table\"],\"articleSection\":[\"Learn and Grow\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/studysection.com\/blog\/join-two-tables-without-a-common-column-in-mysql\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/studysection.com\/blog\/join-two-tables-without-a-common-column-in-mysql\/\",\"url\":\"https:\/\/studysection.com\/blog\/join-two-tables-without-a-common-column-in-mysql\/\",\"name\":\"Join Two Tables without a common column in MySQL - SS Blog\",\"isPartOf\":{\"@id\":\"https:\/\/studysection.com\/blog\/#website\"},\"datePublished\":\"2022-01-24T04:44:08+00:00\",\"dateModified\":\"2022-01-24T05:33:44+00:00\",\"description\":\"This is not a join but can be useful for merging tables in SQL. In this post, I\u2019ll explain different solutions with examples.\",\"breadcrumb\":{\"@id\":\"https:\/\/studysection.com\/blog\/join-two-tables-without-a-common-column-in-mysql\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/studysection.com\/blog\/join-two-tables-without-a-common-column-in-mysql\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/studysection.com\/blog\/join-two-tables-without-a-common-column-in-mysql\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/studysection.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Join Two Tables Without a Common Column in MySQL\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/studysection.com\/blog\/#website\",\"url\":\"https:\/\/studysection.com\/blog\/\",\"name\":\"Blog Posts on famous people, innovations and educational topics\",\"description\":\"\",\"publisher\":{\"@id\":\"https:\/\/studysection.com\/blog\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/studysection.com\/blog\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/studysection.com\/blog\/#organization\",\"name\":\"StudySection\",\"url\":\"https:\/\/studysection.com\/blog\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/studysection.com\/blog\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/studysection.com\/blog\/wp-content\/uploads\/2021\/10\/studySection-logo.png\",\"contentUrl\":\"https:\/\/studysection.com\/blog\/wp-content\/uploads\/2021\/10\/studySection-logo.png\",\"width\":920,\"height\":440,\"caption\":\"StudySection\"},\"image\":{\"@id\":\"https:\/\/studysection.com\/blog\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"https:\/\/www.facebook.com\/studysection\",\"https:\/\/twitter.com\/studysection\",\"https:\/\/www.instagram.com\/study.section\/\",\"https:\/\/www.linkedin.com\/company\/studysection\"]},{\"@type\":\"Person\",\"@id\":\"https:\/\/studysection.com\/blog\/#\/schema\/person\/db367e2c29a12d1808fb1979edb3d402\",\"name\":\"admin-studysection-blog\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/studysection.com\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/054ac87a6874df1932004239cd8eab36?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/054ac87a6874df1932004239cd8eab36?s=96&d=mm&r=g\",\"caption\":\"admin-studysection-blog\"},\"url\":\"https:\/\/studysection.com\/blog\/author\/admin-studysection-blog\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Join Two Tables without a common column in MySQL - SS Blog","description":"This is not a join but can be useful for merging tables in SQL. In this post, I\u2019ll explain different solutions with examples.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/studysection.com\/blog\/join-two-tables-without-a-common-column-in-mysql\/","og_locale":"en_US","og_type":"article","og_title":"Join Two Tables without a common column in MySQL - SS Blog","og_description":"This is not a join but can be useful for merging tables in SQL. In this post, I\u2019ll explain different solutions with examples.","og_url":"https:\/\/studysection.com\/blog\/join-two-tables-without-a-common-column-in-mysql\/","og_site_name":"Blog Posts on famous people, innovations and educational topics","article_publisher":"https:\/\/www.facebook.com\/studysection","article_published_time":"2022-01-24T04:44:08+00:00","article_modified_time":"2022-01-24T05:33:44+00:00","og_image":[{"width":300,"height":200,"url":"https:\/\/studysection.com\/blog\/wp-content\/uploads\/2022\/01\/MySQL.png","type":"image\/png"}],"author":"admin-studysection-blog","twitter_card":"summary_large_image","twitter_creator":"@studysection","twitter_site":"@studysection","twitter_misc":{"Written by":"admin-studysection-blog","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/studysection.com\/blog\/join-two-tables-without-a-common-column-in-mysql\/#article","isPartOf":{"@id":"https:\/\/studysection.com\/blog\/join-two-tables-without-a-common-column-in-mysql\/"},"author":{"name":"admin-studysection-blog","@id":"https:\/\/studysection.com\/blog\/#\/schema\/person\/db367e2c29a12d1808fb1979edb3d402"},"headline":"Join Two Tables Without a Common Column in MySQL","datePublished":"2022-01-24T04:44:08+00:00","dateModified":"2022-01-24T05:33:44+00:00","mainEntityOfPage":{"@id":"https:\/\/studysection.com\/blog\/join-two-tables-without-a-common-column-in-mysql\/"},"wordCount":744,"commentCount":0,"publisher":{"@id":"https:\/\/studysection.com\/blog\/#organization"},"keywords":["MySQL","Table"],"articleSection":["Learn and Grow"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/studysection.com\/blog\/join-two-tables-without-a-common-column-in-mysql\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/studysection.com\/blog\/join-two-tables-without-a-common-column-in-mysql\/","url":"https:\/\/studysection.com\/blog\/join-two-tables-without-a-common-column-in-mysql\/","name":"Join Two Tables without a common column in MySQL - SS Blog","isPartOf":{"@id":"https:\/\/studysection.com\/blog\/#website"},"datePublished":"2022-01-24T04:44:08+00:00","dateModified":"2022-01-24T05:33:44+00:00","description":"This is not a join but can be useful for merging tables in SQL. In this post, I\u2019ll explain different solutions with examples.","breadcrumb":{"@id":"https:\/\/studysection.com\/blog\/join-two-tables-without-a-common-column-in-mysql\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/studysection.com\/blog\/join-two-tables-without-a-common-column-in-mysql\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/studysection.com\/blog\/join-two-tables-without-a-common-column-in-mysql\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/studysection.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Join Two Tables Without a Common Column in MySQL"}]},{"@type":"WebSite","@id":"https:\/\/studysection.com\/blog\/#website","url":"https:\/\/studysection.com\/blog\/","name":"Blog Posts on famous people, innovations and educational topics","description":"","publisher":{"@id":"https:\/\/studysection.com\/blog\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/studysection.com\/blog\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/studysection.com\/blog\/#organization","name":"StudySection","url":"https:\/\/studysection.com\/blog\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/studysection.com\/blog\/#\/schema\/logo\/image\/","url":"https:\/\/studysection.com\/blog\/wp-content\/uploads\/2021\/10\/studySection-logo.png","contentUrl":"https:\/\/studysection.com\/blog\/wp-content\/uploads\/2021\/10\/studySection-logo.png","width":920,"height":440,"caption":"StudySection"},"image":{"@id":"https:\/\/studysection.com\/blog\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/studysection","https:\/\/twitter.com\/studysection","https:\/\/www.instagram.com\/study.section\/","https:\/\/www.linkedin.com\/company\/studysection"]},{"@type":"Person","@id":"https:\/\/studysection.com\/blog\/#\/schema\/person\/db367e2c29a12d1808fb1979edb3d402","name":"admin-studysection-blog","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/studysection.com\/blog\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/054ac87a6874df1932004239cd8eab36?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/054ac87a6874df1932004239cd8eab36?s=96&d=mm&r=g","caption":"admin-studysection-blog"},"url":"https:\/\/studysection.com\/blog\/author\/admin-studysection-blog\/"}]}},"views":9011,"_links":{"self":[{"href":"https:\/\/studysection.com\/blog\/wp-json\/wp\/v2\/posts\/5531"}],"collection":[{"href":"https:\/\/studysection.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/studysection.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/studysection.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/studysection.com\/blog\/wp-json\/wp\/v2\/comments?post=5531"}],"version-history":[{"count":10,"href":"https:\/\/studysection.com\/blog\/wp-json\/wp\/v2\/posts\/5531\/revisions"}],"predecessor-version":[{"id":6412,"href":"https:\/\/studysection.com\/blog\/wp-json\/wp\/v2\/posts\/5531\/revisions\/6412"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/studysection.com\/blog\/wp-json\/wp\/v2\/media\/5532"}],"wp:attachment":[{"href":"https:\/\/studysection.com\/blog\/wp-json\/wp\/v2\/media?parent=5531"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/studysection.com\/blog\/wp-json\/wp\/v2\/categories?post=5531"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/studysection.com\/blog\/wp-json\/wp\/v2\/tags?post=5531"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}