Back to articles list August 6, 2020 - 7 minutes read Can you Join two Tables Without a Common Column? Kateryna Koidan Kateryna is a data science writer from Kyiv, Ukraine. She worked for BNP Paribas, the leading European banking group, as an internal auditor for more than 6 years. More recently, she decided to pursue only the favorite part of her job—data analysis. Now she is continuing her self-education with deep-learning courses, enjoys coding for data analysis and visualization projects, and writes on the topics of data science and artificial intelligence. Kateryna is also a proud mother of two lovely toddlers, who make her life full of fun. Tags: sql learn sql joins Yes, you can! The longer answer is yes, there are a few ways to combine two tables without a common column, including CROSS JOIN (Cartesian product) and UNION. The latter is technically not a join but can be handy for merging tables in SQL. In this article, I’ll guide you through the different solutions with examples. When Do you Need to Join Tables Without a Common Column? Beginners just getting started with SQL are usually introduced to standard equi joins, in which two tables are combined by a common column. However, the real-world tasks of a data analyst are usually more diverse and complex. For example, there are many use cases for a non-equi join, when two tables are combined using conditional operators other than the equal sign. If you are not familiar with this type of join, read this illustrated guide to the SQL non-equi join. In other cases, you may want to have every combination of the rows from the two tables. As an example, if you run a restaurant, you might want to see all possible combinations of wines from your wine card and main courses from your menu. Or, imagine that the information about your suppliers are stored in different tables. They may even have different column names by product type (i.e., wines, juices, fruits, etc.). You might want to combine all the information to generate a single table with all suppliers. In these cases, you would want to combine two or more tables without requiring a common column. It sounds like it should be a pretty straightforward SQL assignment. Well, of course, it is! I’ll suggest a couple of solutions in this guide. But first, let’s explore the data we’ll use for our examples. Getting to Know the Data We run a restaurant and have relevant data stored in multiple tables, among which are wine and main_course. The wine table contains the wine ID, the wine name, the supplier ID, and the price: idnamesupplier_idprice 1Merlot5007.95 2House4002.45 3Sangiovese6005.20 The main_course table contains the main course ID, the name, the supplier ID of the major supplier for this dish, and the price, for each dish: main_course idnamemajor_supplier_idprice 1Cardamom Maple Salmon20019.99 2Classic Macaroni & Cheese1008.99 3Baked Teriyaki Chicken30011.99 4Blue Cheese Beef Tenderloin40015.99 Let’s see how we can combine these tables to get the results we want. How to Combine two Tables Without a Common Column In our first example, we want to see all possible combinations of wines and main courses from our menu. There are at least two approaches to combining the wine and the main_course tables to get the result we need. Using the “FROM Table1, Table2” Syntax One way to join two tables without a common column is to use an obsolete syntax for joining tables. With this syntax, we simply list the tables that we want to join in the FROM clause then use a WHERE clause to add joining conditions if necessary. If what we want is every combination of rows from two tables, there is no need to include any joining conditions. We can use a query like this: SELECT w.name AS wine, m.name AS main_course FROM wine w, main_course m; The query will return a Cartesian product (a cross join), whose result set has the total number of rows equal to the number of rows in the first table multiplied by the number of rows in the second table. winemain_course MerlotCardamom Maple Salmon HouseCardamom Maple Salmon SangioveseCardamom Maple Salmon MerlotClassic Macaroni & Cheese HouseClassic Macaroni & Cheese SangioveseClassic Macaroni & Cheese MerlotBaked Teriyaki Chicken HouseBaked Teriyaki Chicken SangioveseBaked Teriyaki Chicken MerlotBlue Cheese Beef Tenderloin HouseBlue Cheese Beef Tenderloin SangioveseBlue Cheese Beef Tenderloin We got the result we wanted. This is a workable solution for joining two tables when the objective is to get a result set with all possible combinations of the rows. However, this is not the best approach. SQL practitioners prefer to use the explicit CROSS JOIN keyword for this kind of operation. It makes it clear that we are performing a cross join intentionally and not from accidentally omitting the join condition WHERE, which could happen when using the obsolete syntax for joining tables. Read more about the benefits of the new syntax for joining tables in our article What's the Difference Between JOIN and Multiple Tables in FROM? Using the CROSS JOIN Operator As you may have already guessed, the second approach to getting all possible combinations of the rows from two tables is by using the CROSS JOIN operator: SELECT w.name AS wine, m.name AS main_course FROM wine w CROSS JOIN main_course m; This query outputs the exact same result set as shown earlier. However, this syntax is preferable as it clearly states the intent to perform a CROSS JOIN and is more readable for other users of your SQL code. Learn more about cross joins in our Illustrated guide to SQL CROSS JOIN. Using UNION or UNION ALL There are other use cases for combining two tables without a common column. As in the example mentioned earlier, you may want to consolidate all the supplier information stored in multiple tables. You don’t want a Cartesian product in this case. So, how do you combine the tables? In this case, you use a UNION to merge information from multiple tables. This is technically not a join; however, it can be very handy for combining rows from several tables, like in our example below. Simply put, JOINs combine data by appending the columns from one table alongside the columns from another table. In contrast, UNIONs combine data by appending the rows alongside the rows from another table. So, if we want to generate a combined list of the supplier IDs from the tables wine and main_course, we can use the following SQL query: SELECT w.supplier_id FROM wine w UNION SELECT m.major_supplier_id FROM main_course m ORDER BY supplier_id; Here, we first select the supplier IDs from the wine table (column supplier_id) and from the main_course table (column major_supplier_id) separately. Then, we combine these rows using the UNION keyword. Finally, we sort the result for convenience: supplier_id 100 200 300 400 500 600 Note the following when using UNION in SQL: All SELECT statements should list the same number of columns. The corresponding columns must have the same data type. The corresponding columns can have different names, as they do in our example. By default, the name of the corresponding column in the output will be taken from the first SELECT statement. If necessary, you can set the names for the resulting columns using the AS keyword. Note that the columns from different tables have to be named in the same order for them to correspond correctly. The UNION operator removes duplicates from the result set. As you may have noticed, the supplier_id 400 was in both tables, but it appears only once in the result set. If you do not want the duplicates removed, use the UNION ALL operator instead: SELECT w.supplier_id FROM wine w UNION ALL SELECT m.major_supplier_id FROM main_course m ORDER BY 1; The result of this query will include the supplier_id 400 twice: supplier_id 100 200 300 400 400 500 600 If neither CROSS JOIN nor UNION are applicable to your use case of combining two tables without a common column, check out this article with examples of non-equi joins. You may find some relevant examples there. Time to Practice CROSS JOINs and UNIONs! You now know a lot about combining tables without a common column. You’ll be prepared for the question on how to combine two tables without a common column. This is a very common question in SQL job interviews! If you want to learn more about JOINs, check out the SQL JOIN Basics video from our “We Learn SQL” series on YouTube. Also, try LearnSQL.com’s guide on the best ways to learn SQL JOINs. However, you probably already know that the shortest path to becoming an SQL expert is through lots of practice writing SQL queries. LearnSQL.com offers a comprehensive course on SQL JOINs with 93 interactive exercises. In this course, you’ll have the opportunity to practice all kinds of basic JOINs, including CROSS JOINs, self-joins, non-equi joins, and joins of multiple tables. Happy learning! Tags: sql learn sql joins You may also like SQL JOINs A SQL JOIN is a method to retrieve data from two or more database tables. Here we present a basic overview of what data from a particular SQL join will look Read more Learning JOINs With Real World SQL Examples JOIN statement lets you work with data stored in multiple tables. This article is a practical introduction to the SQL JOIN. Check out examples in real life. Read more An Introduction to Using SQL Aggregate Functions with JOINs Aggregate functions. Powerful SQL tools. Let's see how they cooperate paired with LEFT JOIN, SUM and GROUP BY perform computations on multiple tables. Read more How to Join 3 Tables (or More) in SQL Have you ever wondered how to join three tables in SQL? It's easy when you know the basics. Joining three tables can be as easy as joining two tables. Read more Subquery vs. JOIN What are the differences between a subquery and a JOIN in SQL? How do you choose one over the other? See examples of when to use which. Read more How to LEFT JOIN Multiple Tables in SQL You will learn how to left join 3 tables in SQL while avoiding common mistakes in joining multiple tables. Examples included! Read more Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.